What formulas would determine active proof, current task, and next task?

Sara Ross

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

Genevieve P.

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:

• Formula combinations for cross sheet references
• JOIN Function / COLLECT Function

Let me know if this accomplishes what you're looking for!

Cheers,

Genevieve