Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

  • ✭✭✭✭✭
    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

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!

Trending in Formulas and Functions