Sequence numbering based on another Cells info.
I'm looking for a formula to sequence document numbers based on another cells info. I've set up IF functions to code each main column (in blue) which will be hidden in the background & locked to which i then used a JOIN function to get a serial code. (shown in "document Code").
With those in place, On "Document No." Column - I'm looking for a formula to create sequences numbers based on the Document Code Column. As i have multiple RA-LOGIH. I want this to be RA-LOGIH001 & the one below LOGIH002 not broken down in sequence due to each row as shown on "Document No." (if this makes sense).
I've created on the sheet in Orange - What i want.
So the user only needs to select "Document Type" -> "Department" -> "Dept. Coverage" & this will automate a Document number based on previous entries.
Best Answer
-
You would drop that field in to the first portion similar to how we strung together the Doc Cod and Dept Code. Then you would add in another range/criteria set to the COUNTIFS to follow the same syntax as both the Doc and Dept Codes.
Answers
-
You will first need to insert an auto-number column with no special formatting (called "Auto" in this example). Then you would use something like this:
=[Doc Code]@row + "-" + [Dept Code]@row + RIGHT("000" + COUNTIFS([Doc Code]:[Doc Code], @cell = [Doc Code]@row, [Dept Code]:[Dept Code], @cell = [Dept Code]@row, Auto:Auto, @cell<= Auto@row), 3)
-
Hi Paul,
this worked wonders except it's not picking up dept. Cov Code? If you can include the Dept. Cov code as well - This would be exactly what i need.
Thank you for replying by the way - You have no idea how long i've been trying to wrap my brain around this one.
-
You would drop that field in to the first portion similar to how we strung together the Doc Cod and Dept Code. Then you would add in another range/criteria set to the COUNTIFS to follow the same syntax as both the Doc and Dept Codes.
-
That Worked mate.
Thank you - Really appreciate you help with this mate.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.3K Get Help
- 423 Global Discussions
- 221 Industry Talk
- 461 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!