Mutiple Project Status

Hello I need some expert help from the smartsheet community
I'd like to create a formula in the to determine the Project Status Heath. Each project is supposed to only last a certain # of days within the project stage. (ie 3d Print/other (DE) should be only 5 days long and Bottle Design should be 5 days long)
I'd to explore two potential ways to create this formula
1. if i nested all the possible project status in one formula within if and statements
2. if i used the table of list of project statuses and duration thresholds.
Ideally i'd like to stick with method 2 because other team members needs to maintain the list of statuses and durations. It'll be easier for them to just edit the list of project status and their duration thresholds than to it within change the formula.
Best Answer
-
Hello @Enoch Mak
I'd recommend having the table of list of project statuses and duration thresholds on a different sheet.
Then try this formula on your Project Status Health column:
=IF([Duration (Subtract from today)]@row > INDEX(COLLECT({Duration Threshold}, {List of Project Statuses}, [Project Status]@row), 1), 1, 0)
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined
Answers
-
Hello @Enoch Mak
I'd recommend having the table of list of project statuses and duration thresholds on a different sheet.
Then try this formula on your Project Status Health column:
=IF([Duration (Subtract from today)]@row > INDEX(COLLECT({Duration Threshold}, {List of Project Statuses}, [Project Status]@row), 1), 1, 0)
Melissa Yamada
melissa@insightfulsheets.com
Data made simple, spreadsheets reimagined -
@Melissa Yamada - thanks Melissa this worked for me appreciate it!
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 433 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 506 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!