What formulas would determine active proof, current task, and next task?
In my sheet summary, I would like to indicate 1) which proof is currently active, 2) the task that currently needs completed, and 3) the next task to complete once the current task is completed. *Note - The tasks to be referenced will be one of Staff Submissions (children not needed), Consolidate Submissions, Send Proof to PPM, or PPM Return Proof to CSD.
Any help you can provide is greatly appreciated!
Sara
Best Answer
-
Hi @Sara Ross
The first thing I'd do here is add a helper column in your sheet to determine the Levels of hierarchy. That way you can filter in your formulas for only the blue rows to find the tasks (and not the grey child rows).
=COUNT(ANCESTORS([Primary Column]@row))
Then you can use this "Level" column in your formulas to only look for Level 2, or Level 1 for Proof names. You can also hide the column when you're done building your formulas.
1) Active Proof
To see Active Proofs, I would suggest using a JOIN(COLLECT formula. The reason I would use JOIN instead of INDEX is on the off chance you have two Proofs in progress... potentially an accident, but still good to see.
=IFERROR(JOIN(COLLECT([Primary Column]:[Primary Column], Level:Level, 1, Status:Status, "In Progress"), " / "), "None Started")
This filters by the Level being 1 (a Proof level) and the Status column being set to "In Progress".
If there's only one Proof with the status in Progress, it will only show the one name.
2) Task to be Completed
We can use the exact same formula to then return what second-level tasks under that Proof are in Progress. Just change out the Level!
=JOIN(COLLECT([Primary Column]:[Primary Column], Level:Level, 2, Status:Status, "In Progress"), " / ")
3) Next Task
The Next Task is a little trickier. Assuming that all of the rows in your sheet are in order, this is where you could use INDEX instead of JOIN, and grab the first row that meets your criteria. In this case, it's a row with Level 2 that is currently "Not Started"
=IFERROR(INDEX(COLLECT([Primary Column]:[Primary Column], Level:Level, 2, Status:Status, "Not Started"), 1), "n/a")
Notice the 1 at the end, which says to bring back the 1st row. I also wrapped this formula in an IFERROR so that when all tasks are completed within your sheet you'll see "n/a" instead of an error.
Here are some resources that may be helpful as you build out your formulas:
Let me know if this accomplishes what you're looking for!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hi @Sara Ross
The first thing I'd do here is add a helper column in your sheet to determine the Levels of hierarchy. That way you can filter in your formulas for only the blue rows to find the tasks (and not the grey child rows).
=COUNT(ANCESTORS([Primary Column]@row))
Then you can use this "Level" column in your formulas to only look for Level 2, or Level 1 for Proof names. You can also hide the column when you're done building your formulas.
1) Active Proof
To see Active Proofs, I would suggest using a JOIN(COLLECT formula. The reason I would use JOIN instead of INDEX is on the off chance you have two Proofs in progress... potentially an accident, but still good to see.
=IFERROR(JOIN(COLLECT([Primary Column]:[Primary Column], Level:Level, 1, Status:Status, "In Progress"), " / "), "None Started")
This filters by the Level being 1 (a Proof level) and the Status column being set to "In Progress".
If there's only one Proof with the status in Progress, it will only show the one name.
2) Task to be Completed
We can use the exact same formula to then return what second-level tasks under that Proof are in Progress. Just change out the Level!
=JOIN(COLLECT([Primary Column]:[Primary Column], Level:Level, 2, Status:Status, "In Progress"), " / ")
3) Next Task
The Next Task is a little trickier. Assuming that all of the rows in your sheet are in order, this is where you could use INDEX instead of JOIN, and grab the first row that meets your criteria. In this case, it's a row with Level 2 that is currently "Not Started"
=IFERROR(INDEX(COLLECT([Primary Column]:[Primary Column], Level:Level, 2, Status:Status, "Not Started"), 1), "n/a")
Notice the 1 at the end, which says to bring back the 1st row. I also wrapped this formula in an IFERROR so that when all tasks are completed within your sheet you'll see "n/a" instead of an error.
Here are some resources that may be helpful as you build out your formulas:
Let me know if this accomplishes what you're looking for!
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
That worked beautifully, Genevieve! Thank you for the thorough explanation.
Sara
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!