Forum Discussion
Conditional Formatting dependent on finding references
Hi, I'm trying to figure out how to setup a conditional formatting based on several conditions being formatted in other cells. Basically chaining conditions. In this scenario, I'm trying to docu...
=AND(ISTEXT($D2),TODAY()-VLOOKUP($D2,$A$2:$D$17,4,FALSE)<180)
Is this the rule for conditional formatting you are looking for?
- Yeah, that's exactly what I needed!
I mis"spoke" in my rundown at the end and said I need it to see if column d is text, but I think the inciting criteria needs to be if column C contains the word "See" -- I forgot that in the IRL spreadsheet I do have notes like "On File" in the date column for legacy documents that don't need to be updated.
I'd imagine that I could either (a) change the first part of the AND to something like SEARCH("See",$C2) (if I'm understanding how to use SEARCH right) -- or (b) add a dummy row for "On File" with a dummy date or something to make it valid).
Right?=AND(ISNUMBER(SEARCH("See",$C2)),TODAY()-VLOOKUP($D2,$A$2:$D$17,4,FALSE)<180)
This rule checks if "See" is in column C. This could be what you are looking for. I can't comment on your option (b) which would mean adding a dummy row for "On file" unfortunately.
Unfortunately that didn't work either.
I've attached a scrubbed version of the IRL file. It has a few formulas going on in it, and I didn't figure they'd conflict, but maybe they do.
