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
- Customer Resources
- 65.2K Get Help
- 445 Global Discussions
- 143 Industry Talk
- 476 Announcements
- 5K Ideas & Feature Requests
- 84 Brandfolder
- 150 Just for fun
- 71 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 301 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!