Formula to Auto assigned Id_Number as Input in Sheet

Ashwani94k

What Formula should I used get desired JV number output

-> Here In Sheet I what to get out similar as shown in JV Number column, Point to be note number id in JV number should be assign as per entry in sheet.

1) So Here in variation# column the entry is against variation num. VAR-0012814 so number assign to it should be JV-001, now whenever new entry is done with same variation number again at any position in sheet the JV number it should get JV-001.

2) Next entry in Variation# column is VAR-0012816 , VAR-0012817 and so on. in reference of these entry JV number output should follow same pattern as shown in picture.

MariaCurtis

You may have to create a "helper" sheet with the variation numbers on it in one column, and the corresponding JV # next to it. Then do a vlookup to that sheet as your formula in the JV column.

Your "helper" sheet could just have these 2 columns:

Then your formula back on your main sheet could look like this. I converted it to a column formula so that any new rows added would automatically have the formula. Also, as a best practice, I typically lock my formula columns.

Hope this helps!