Dynamic numbering
Hi, I wanted to know if its possible to have the entry number as the Function + RAID item(R,A,I,D) + number. What I am looking for is for the R,A,I,D items to be named as per the functions- i.e. if its the 'first' R in Funciton1 , the result should be - Function 1. R.1 and if its the 'third' A in function 4, the result should be - Function 4. A.3. Please help! Thanks!
Best Answer
-
Ok, so I got it working, but I added a Unique Counts column to break the formulas up a bit. You could combine it, but the formula will get a little wild at that point.
The formula in the Unique Counts column is:
=COUNTIFS(RAID$1:RAID@row, RAID@row, Function$1:Function@row, Function@row)
Where you're counting the instances where the RAID value is equal to the current RAID value and the function value is equal to the current value up to that row (whichever row you're on). This should get you the unique value of how many times the letter has been used to this point.
Then in the Description column I am putting everything together by doing:
=Function1 + ". " + RAID1 + "." + [Unique Counts]@row
Where you're pulling the various elements together and adding the periods you had in your statement. If you wanted it to be all in one you could replace the formula in the Unique Counts Column where it says [Unique Counts]@row.
Answers
-
Just to clarify, in your example pictured your results would be:
Function 1. R.1
Function 2. A.1
Function 1. R.2
Function 2. R.1
Correct?
-
Hi David. Yes you are correct!
-
Would it be possible to use a custom sort to sort by Function Name, then by RAID letter? If so that makes the formula much easier.
-
Hi David. The Entry no is linked to some other sheets and so I cant sort it. I was hoping to have dynamic numbering so that it can be pulled to these sheets immediately.
-
Ok, so I got it working, but I added a Unique Counts column to break the formulas up a bit. You could combine it, but the formula will get a little wild at that point.
The formula in the Unique Counts column is:
=COUNTIFS(RAID$1:RAID@row, RAID@row, Function$1:Function@row, Function@row)
Where you're counting the instances where the RAID value is equal to the current RAID value and the function value is equal to the current value up to that row (whichever row you're on). This should get you the unique value of how many times the letter has been used to this point.
Then in the Description column I am putting everything together by doing:
=Function1 + ". " + RAID1 + "." + [Unique Counts]@row
Where you're pulling the various elements together and adding the periods you had in your statement. If you wanted it to be all in one you could replace the formula in the Unique Counts Column where it says [Unique Counts]@row.
-
Awesome, David! It worked like a charm. Thanks for solving my query! Cheers! :)
-
No problem. Glad it's working.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.8K Get Help
- 410 Global Discussions
- 219 Industry Talk
- 457 Announcements
- 4.7K Ideas & Feature Requests
- 142 Brandfolder
- 136 Just for fun
- 57 Community Job Board
- 459 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 297 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!