Automating Sheet Summary

Caitlin Goodman

Hi there,

I'm extremely new to SS. I'm trying to automate our Sheet Summary on our timelines. I would like to automate the Project Health to look something like this.

RED = 7 days before due date and less than 75% complete

YELLOW = 30 days before due date and <50% complete, AND 14 days before due date and <75% complete

GREEN = 30 days before due date and >=50% complete, AND 14 days before due date and >=75% complete

GRAY = 0% complete <--- basically indicating the project hasn't Kicked off yet.

Thanks in advance for any insight you can offer.

Paul Newcome

It is possible. Do you have a row that contains a specific Due Date that you would use for the overall project?

Paul Newcome

Is this in an actual Sheet Summary field, or is this by row?

Caitlin Goodman

Hi Paul,

Well so the timeline each task has a health, but we want to use the sheet summary to summarize the overall health of the project. Is this possible?

Paul Newcome

It is possible. Do you have a row that contains a specific Due Date that you would use for the overall project?

Caitlin Goodman

Yes I do have a field for the specific due date.

Paul Newcome

Then your formula would look pretty much the same as if you were going to automate the RYG status in the sheet itself.

In the formula below, just update column names/row numbers to reflect what cell you will actually be referencing in the sheet itself.

=IF([% Complete]1 = 0, "Gray", IF(AND([Due Date]1<= TODAY(7), [% Complete]1<= 0.75), "Red", IF([Due Date]1<= TODAY(14), IF([% Complete]1<= 0.75, "Yellow", "Green"), IF([Due Date]1<= TODAY(30), IF([% Complete]1<= 0.5, "Yellow", "Green")))))