Health column that references variance column before setting baselines

Katy H

I am working on a project plan template for a colleague and they want the health column to be dependent on variance. The issue is they will struggle to write or edit formulas and the variance column doesn't appear until baselines are set.

Has anyone come up with a solution for this?

The current formula I have been testing is below. It works until I copy that formula into a sheet with no variance column. It doesn't start working once the correct columns are added in. (Note: [Today - End Date]@row is a formula that subtracts the target End Date from Today's Date.

=IFERROR(IF(OR(AND(Status@row = "Complete", Variance@row > 10), AND(NOT(Status@row = "Complete"), [Today - End Date]@row > 10)), "Red", IF(OR(AND(NOT(Status@row = "Complete"), [Today - End Date]@row > 4), AND(Status@row = "Complete", OR(Variance@row > 0, Variance@row < 4))), "Yellow", IF(AND(Status@row = "Complete", Variance@row <= 0), "Green", "Gray"))), "")

Genevieve P.

Hi @Katy H

If the formula is working for you in a different sheet, is it possible that when the new columns are added in to the second sheet where you've pasted the formula that some of the column names are slightly different?

For example, if there aren't correct spaces in the [Today-EndDate] column, then the formula won't match the column name and will give you UNPARSEABLE.

I will also note that if you first input the formula then create the columns, you may need to drag the formula up/down the column to re-fresh it and have it recognize that new columns exist.

If I've misunderstood, it would be helpful to see a screen capture with an explanation of how it's "not working" (e.g. is it giving you the wrong output or an error).

Thanks!

Genevieve

Katy H

Thank you for your response @Genevieve P.

It falls into the second scenario. I am creating a template set/toolkit (to use the SMAR internal phrase). I want to preset the health column formula to reference the variance column before the baselines have been set so that when a template set/toolkit has been copied, dates have been added to the project sheet. The baseline has been set, that the health formula references variance. But it seems that once the column is added the formula doesn't automatically read that column. Dragging the formula to refresh also doesn't seem to help.

If there is any other way that people have written a formula that references the concept of variance without using the variance column, I am open to that as well.

Genevieve P.

Hi @Katy H

After the columns are added and the sheet is saved, does the formula update when you hit Refresh or exit and come back to the sheet again? As long as the column names are spelled the exact same, the formula should (in theory) recognize those columns after a re-set of some kind (e.g. a drag of the formula or a refresh of the sheet).

I tested on my own sheet and while the formula didn't update the second I added the Variance column, it did update after I saved and refreshed the sheet.

Pre-Baseline:

Post-Baseline, not saved:

Post-Baseline, Saved and Refreshed: