Two sets of generated task numbers based on Prefix
Hello,
I have a sheet that is tracking submitted occurrence reports. I need the numbers to be sequential for two different prefixes OR and SAFOR. Example OR1000, OR1001, OR1002 AND SAF1000, SAF1001, SAF1002. I don't think I can do this with an autonumber column, but any suggestions on how to do this?
Thanks,
Ashley
Best Answer

Thank you, I have it working now. I appreciate the help!
Answers

You would use an autonumber column, but you would have it set up with no modifications. You want it to just output numbers.
From there you would use a text/number column with a formula to generate your IDs. It would start with an IF statement to determine which prefix
=IF(this = something, "OR", "SAFOR") +
Then a COUNTIFS to get your incrementation.
=IF([Column Name]@row = "something", "OR", "SAFOR") + (1000 + COUNTIFS([Column Name]:[Column Name], @cell = [Column name]@row, [AutoNumber]:[AutoNumber, @cell <= [AutoNumber]@row))

Thanks, and I almost have it working. Here's a screen shot of my columns with the formulas  the "=" so you can see the whole formula. I'm getting unparsable in the Countif column, so I guess something needs tweaking?
Thanks,
Ashley

You missed the opening parenthesis immediately after the COUTNIFS function. Also looks like you have the reference to the autonumber column spelled wrong in the formula.
You have
[Auto Number]@row
But the column name in the sheet is AutoNumber (with a hyphen instead of a space.

Thank you, I have it working now. I appreciate the help!
Help Article Resources
Categories
Check out the Formula Handbook template!