Saturday, February 3, 2018

yogi_Assign sequential ID from range based on cell text and previously used range value

Google Spreadsheet   Post  #2359

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Feb-03-2018
question by b_sander
I would like to populate a field with a sequentially unique number from a given range dependent on a string/product type.
There are three products (ProdA, ProdB, and ProdC) and when one of the three products is selected from a field, I want a number generated sequentially from a range of numbers where (ProdA=100-199, ProdB=200-299, and ProdC=300-399)

Product, Article#
ProdA, 101
ProdA, 102
ProdC, 301
ProdB, 201
ProdC, 302
ProdA, 103
ProdB, 202
I have used  =if(A2="ProdA",100,if(A2="ProdB",200,if(A2="ProdC",300))) and then manually changed the last digit to the next unused digit in the range.

I have also tried to use the script editor unsuccessfully.

Any direction or advice would be appreciated.