Auto-Assigning Project Codes Based on Status
Good afternoon,
I have a formula to auto-generate a Project Code based on Approval Status and Department and department.
=IF([Approval Status]@row = "Approved", LEFT(Department@row, 3) + "-" + (YEAR([Project Intake Date]@row) + "-" + IF(Prefix@row <> "", Prefix@row, "") + [Suffix Helper]@row + ""))
Using the screenshot above as an example, I would like to adjust this formula so that Row 5 becomes OTL-2024-103 since it was "approved," and no Project Code was generated for Row 4 because the project was "declined".
To take it a step further, I'd like Row 7 to become OTL-2024-105 since Row 6 hasn't been "approved" yet.
Once Row 6 is approved, I'd like that to become OTL-2024-106.
Once a row has been assigned a project code, I'd like to "lock" it in place so it can't be adjusted.
Answers
-
More context:
This is the formula I'm using to generate the Suffix.
=IF([Approval Status]@row <> "Approved", "", MATCH(UniqueID@row, COLLECT(UniqueID:UniqueID, [Parent ID]:[Parent ID], [Parent ID]@row, Department:Department, Department@row), 0))
I have a second helper suffix column to add digits.
=IF(LEN(Suffix@row) = 1, "10" + Suffix@row, IF(LEN(Suffix@row) = 2, "1" + Suffix@row, IF(LEN(Suffix@row) = 3, "" + Suffix@row)))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.2K Get Help
- 358 Global Discussions
- 198 Industry Talk
- 427 Announcements
- 4.4K Ideas & Feature Requests
- 135 Brandfolder
- 127 Just for fun
- 128 Community Job Board
- 444 Show & Tell
- 28 Member Spotlight
- 1 SmartStories
- 283 Events
- 35 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!