Auto Generating a Sequential Document Number Based on Cell Criteria

RvogelPrimus

What formula can I use to auto-generate a sequential number into the Document Number column for each row based on the information placed in Company, Department, and Type? I need the numbers to look like this: PBI-ACT-FRM-0001.

"Company-Department-Type-Sequential Number"

We have several Department and Type options in a dropdown. So, the variations are large but we only want a number to be used once, i.e. PBI-ACT-FRM-0001 would generate and the next time we select those same criteria, the next number to generate would be PBI-ACT-FRM-0002, and so on. If we had PBI-PM-GUI selected, then the first number generated would be PBI-PM-GUI-0001. The next time we choose PBI-PM-GUI, the number generated for that would be PBI-PM-GUI-0002, and so on.

Paul Newcome

You would need to first insert an auto-number column with no special formatting (called "Auto" in this example).

Then you would use something along the lines of

=Company@row + "-" + Department@row + "-" + Type@row + "-" + RIGHT("000" + COUNTIFS(Company:Company, @cell = Company@row, Department:Department, @cell = Department@row, Type:Type, @cell = Type@row, Auto:Auto, @cell <= Auto@row), 4)

RvogelPrimus

So, I did the formula you suggested. It generates a document number but duplicates the actual 4-digit number at the end. See below. Thoughts? Anyway to make it so that it is sequential? 0001, 0002, 0003, etc.

Paul Newcome

Did you insert the auto-number column and then save? It is dependent on the auto-number column being populated, but that columns doesn't populate until the sheet is saved.

RvogelPrimus

Thank you! I did not save the sheet. It is now generating sequential document numbers. Thank you, again!

Paul Newcome

Happy to help. 👍️

RvogelPrimus

Ok, I have an update to this spreadsheet/additional question. I added conditional formatting so that row colors alternate between blue and white. Is there a way to add conditional formatting (or maybe it's a formula) so that when the "On BOS," "On DMS," and "Reformatted" columns are all three checked the blue row turns a medium green and the white row turns a light green?

Paul Newcome

You would set up another rule that evaluates all three checkbox columns in addition to the 0/1 column.