Forum Discussion
Conditional formatting using date passed and value in another column
Hi please could anyone help with this - I'm trying to use an 'AND' formula but seem to be messing it up I have a sheet tracking projects, showing the date open - the date is in column B, and colu...
Thanks Hans, appreciated. Think this would highlight anything with specific days rather than looking at eg level 6 projects that are over 60 days or level 7 projects over 90 days. Since solved. Thanks again, it's really useful learning as can imagine the formula you've shown will also come in handy in future 🙂
Have you actually tried my suggestion? It's simpler than Riny_van_Eekelen's (excellent!) solution.
- Wow - thanks Hans and apologies for my earlier reply: I don't understand how it does it, but it works! A very elegant formula... how is that interpreting levels in column i that are less than 7 and applying the 60 day rule? Brilliant anyway, thank you both for these solutions
Since we select 'greater than' from the second drop down, the rule will highlight cells whose value is greater than the result of the formula in the box next to the second drop down.
The formula =IF(I2<7, 60, 90) returns 60 if I2<7, i.e. if I2 is 1, 2, 3, 4, 5, or 6. So in those cases, the cell will be highlighted if its value is greater than 60.
But the formula returns 90 if I2 is not <7, i.e. if I2 is 7, 8, or 9. In those cases, the cell will be highlighted if its value is greater than 90.
- A deceptively simple formula - fantastic, thanks Hans!