Forum Discussion
Conditional formatting based on the date output of a formula
I have 4 Columns that are linked, one has Analytes listed, there is not a set list of analytes or some kind of drop down because some are new and one time use. Two columns change the date shown based...
So I would recommend creating a helper table for all those values and doing a lookup.
alternatively you can use Name Manage and create a constant table value like this:
but in either case if the table above or the name variable is 'tVals' then you just need to use:
=IF($H2="",IFERROR($F2+VLOOKUP($I2,tVals,2,0),""),"")
and if you need to compare with TODAY() or TODAY()+1
you can create a couple conditional formatting formulas or just use the above formula - TODAY() to get a number 0 for today, ,1 for tomorrow, >1 for more days. Then use 3 color scale formatting to do all 3 colors:
notice the type is set to number and then values are 0,1,2
or you can use ICON set and then use 'show icon only' so you can hide the numbers: