Automate Phase

System

This discussion was created from comments split from: Change cell color based on date from another sheet.

Jeff Casto

Hello @Krissia B. and @Genevieve P. !

I have a similar-ish issue...

I have project sheets that have lists of tasks and dates. So far, so normal.

Included in that sheet is a top row of high level information that gets pulled into a report that shows data from multiple project sheets.

In that top row of high level information, I have a phase column to show what general stage the project is in. Currently, I manually choose the phase from a drop down list and the color is automatically created based on Conditional Formatting I set up.

I would, however, like to automate this more if possible.

Is it possible to have the "Phase" column change automatically based on the date associated with the task that the phase relates to?

In the screenshot below, you see my project sheet. It has the top row of high level data and multiple rows beneath showing tasks. For example, is it possible for the "Phase" column to change when the start date for "Creative dev" is arrived on? And then the phase column change every time the new Parent task is arrived on?

Please let me know if this is confusing. The fundamental question is can a cell in a sheet as described above change triggered by a task date.

Thanks for any help!!!

Jeff

Genevieve P.

Hey @Jeff Casto

Yes this is possible! However I would suggest setting up some helper columns to be able to identify the level of rows (the second-level hierarchy).

Add a column anywhere in your sheet (you can hide it) with the simple column formula of

=PARENT([Task Name]@row)

I've highlighted my levels in different colours to make it easier to see. Notice how all my second level tasks say "Top Level" in my helper column?

In your instance, I would expect this to return "All Tasks" in each of those type of rows.

Then we can use this as a filter criteria in an INDEX(COLLECT function, so it only looks at those rows and checks the date. The way we check the date is we want to see the MAX date in that level row, but only if that date is in the past or equal to today.

You can see my formula in the image above. In your instance, it will likely be something like:

=INDEX(COLLECT([Task Name]:[Task Name], Parent:Parent, "All Tasks", Start:Start, MAX(COLLECT(Start:Start, Parent:Parent, "All Tasks", Start:Start, <=TODAY()))), 1)

Keep in mind that since this formula uses the TODAY function, you will need to open or refresh the sheet if you want this to update on a daily basis. See: Automatically update the TODAY function in formulas

Let me know if this works for you!

Cheers,

Genevieve

Jeff Casto

@Genevieve P. , per usual, amazing.

Also per usual, I have a couple follow up not so amazing questions.

I added the Parent and Phase Formula columns and made them a column formula. There are three items I have questions about.

1. In the Phase Formula column, the only "All Tasks" (or "Top Level" from your sheet) stage that shows up is Strategy. You can see where "Creative dev" starts, "Strategy" remains in the Phase Formula helper column.
2. In the Phase column, there is nothing showing up. Is there a formula to put in that column that would pick up the item from the Phase Formula column to show the phase? Something like =PhaseFormula@row?
3. Is it possible to change the color of the phases in the Phase column when the phases change?

Thank you again so much!

Jeff

Genevieve P.

Hey @Jeff Casto

Forgive me, I have led you astray!

The "Phase Formula" column I included in my image was just to show you the formula open, while the actual formula was placed in the top cell of the "Phase" column. This should sort out your Question #2!

For your first question, I may have misunderstood what you were looking to do. This formula will only look at the second-level parents, so "Strategy" and "Creative Dev", but it will skip all the third level Child Rows (internal review, revisions, etc).

So essentially it's looking only at your bolded, Parent rows as the "Phase". Let me know if I've misunderstood!

I also see that you made this a column formula so it appears on every row, versus a summary cell at the very top of the sheet to indicate the parent level that's currently in progress.

Are you just looking to populate the Parent Phase name IF the current row is in progress? Or is this a summary rollup value?

3 - Yes! You'd use Conditional Formatting in this instance. 🙂

Jeff Casto

@Genevieve P. !

Again, you have saved the day. You understood what I was asking and provided the correct advice. I just did (at least) two not awesome things, one being adding a phase formula column and making it a column row.

So, all I did to remedy my booboo was place the formula for the Phase in the proper cell and I removed my Phase Formula column.

You are correct, when the current task phase (strategy, creative dev, production, launch...whatever) is active, I want the name of that stage to show up in the top row phase column.

I just tested it by moving dates around and it works like a charm!!!

A million thanks again and again!

Genevieve P.

I'm so glad to hear this worked for you! Thanks for following up.