Solved: Calculating & Writing Sequence Number which has to... - Page 2

Neeraj_Malik · 12-08-2020 09:47 PM

Looking for a solution to have sequence number that has to be written on three-digits like 001, 002 etc and the sequence has to be reset with the change of month. Also the same has to be prefixed with defined characters along with month & year of the day.

I could think of the prefix logic using concatenate but not abe to figure out the way to add 3 digit sequence number having reset mechanism with the change of month

CONCATENATE(“MB-INV”, TEXT([Date], “yymm”), _______)

Any suggestion?

Neeraj_Malik

I figured out a way to do this where I wanted to have the value of column [Rececipt Number] in sequence and the sequence shall reset to 1 at the change of month.

use the following or similar expression in google sheet

CONCATENATE("" &"MB/PMT/" &text(A3,"yymm") &TEXT(IF(MONTH(A3)=MONTH(A2),VALUE(RIGHT(N2,3))+1,1),"000"))

Regenerate the structure of the respective table
this formula would become the value of Autocompute Spreadsheet formula
And then every row would have a different value in sequence and the following result is achieved

The only catch is that the value of 1st row would show as kind of error, which I personly handled by manually setting the value to MB/PMT/2012000. This value in above snapshot has been set manually

The issue of same value by two users would be handled through the key as UNIQUEID()

View solution in original post

Calculating & Writing Sequence Number which has to reset with the change of month