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 OR-1000, OR-1001, OR-1002 AND SAF-1000, SAF-1001, SAF-1002. I don't think I can do this with an auto-number column, but any suggestions on how to do this?
Thanks,
Ashley
Best Answers
-
You would use an auto-number 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, [Auto-Number]:[Auto-Number, @cell <= [Auto-Number]@row))
-
Thank you, I have it working now. I appreciate the help!
Answers
-
You would use an auto-number 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, [Auto-Number]:[Auto-Number, @cell <= [Auto-Number]@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 auto-number column spelled wrong in the formula.
You have
[Auto Number]@row
But the column name in the sheet is Auto-Number (with a hyphen instead of a space.
-
Thank you, I have it working now. I appreciate the help!
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 466 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!