Health column that references variance column before setting baselines
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"))), "")
Answers

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 [TodayEndDate] 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 refresh 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
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions

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.

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 reset 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.
PreBaseline:
PostBaseline, not saved:
PostBaseline, Saved and Refreshed:
Need more help? 👀  Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋  Global Discussions
Help Article Resources
Categories
 All Categories
 14 Welcome to the Community
 Smartsheet Customer Resources
 63.6K Get Help
 403 Global Discussions
 215 Industry Talk
 454 Announcements
 4.7K Ideas & Feature Requests
 141 Brandfolder
 136 Just for fun
 56 Community Job Board
 459 Show & Tell
 31 Member Spotlight
 1 SmartStories
 296 Events
 36 Webinars
 7.3K Forum Archives
Check out the Formula Handbook template!