Auto Add a Number After Auto Numbering
I have a master sheet which captures data and assigns an auto-number (Incident Number). When a row falls into a certain category (Warranty), it gets copied to a new sheet.
I want to be able to take that unique Incident Number and add to it. This is what I have so far:
It puts a prefix onto the Incident Number, but I also want it to add a letter or number at the end. So it would read W-CIR-00001-A.
Any ideas would be much appreciated.
Thanks!
Answers
-
What would be the logic? Would it always be "A", or would you need different letters based on certain variables?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
It would need to be a consecutive letter (or number). It just needs to be something that can uniquely identify the row.
What happens is we get a warranty claim, but there could be multiple items on it. We need to break that claim up per item, i.e. create a row for each item within the claim so that we can identify them individually.
-
A number would definitely be much easier. Would you need it prefilled with leading zeros such as 001, 002 or just 1, 2 is fine?
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
-
Preferably with leading zeros, this will give us a bit of scope if there are lots of items.
-
Ok. Add this to the end of your existing formula:
+ IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 10, "00", IF(COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)< 100, "0", "")) + COUNTIFS([Incident Number]:[Incident Number], @cell = [Incident Number]@row, [Date Logged]:[Date Logged], @cell<= [Date Logged]@row)
This will prefill with up to 2 zeros so that you have capacity for up to 999 entries per incident number.
Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!