Autonumbering outdent and indent separately
My outdents are initiatives and my indents and milestones and would like to number my outdents INIT001 and so on, and my indents to be numbered as MILE0001 and so on...though I dont seem to be able to do this. I've tried having separate columns for this also one for Initiative Number and one for Milestone Number but the autonumber is applying to all rows...hopefully someone can help
Answers
-
The way I would do this is to add a number of helper columns to the sheet.
- Column to identify if a row is a Parent or Child
- Auto-Number System column with just numbers
- Rank column to rank the Auto-Number, based on if it's a Parent or Child, and add in the correct preface.
Formula in the Parent column:
=IF(COUNT(ANCESTORS(Task@row)) = 0, "INIT", "MILE")
See: ANCESTORS Function
Formula in the Unique ID column:
=IF(Parent@row = "INIT", "INIT" + RANKEQ(Auto@row, COLLECT(Auto:Auto, Parent:Parent, "INIT"), 1), "MILE" + RANKEQ(Auto@row, COLLECT(Auto:Auto, Parent:Parent, "MILE"), 1))
See: RANKEQ Function and COLLECT Function
Let me know if this makes sense and will work for you!
Cheers,
Genevieve
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
Oh this sounds great...I have added the Auto and Parent columns. Created the Uniqie ID column however the formula is showing as invalid operation...
-
It looks like your Auto number column contains 0's leading up to the actual number. Leading 0's will translate the number into a text value (to keep the 0 showing) so it can't be evaluated with the RANKEQ to rank the Auto Number and produce a new number.
Does your process require the 0s?
If not, you can re-set an auto-number column to start back at 1 from the top of the sheet (see: Reset the Auto-Number Column)
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
This worked a treat...you are a star!!! Thank you soooooo much!!!
-
You're welcome! I'm so glad it works for you.
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.7K Get Help
- 406 Global Discussions
- 218 Industry Talk
- 456 Announcements
- 4.7K Ideas & Feature Requests
- 141 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!