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...
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?
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.- I figured it out!
I didn't know that the "4" in your formula related to the column it would be pulling from.
Since in the IRL spreadsheet, it's column I and I'm having it search in relation to A and I ($A$6:$I$555) it would be "9"
Thanks for all your help!
- Okay thanks, I'll try this formula.
However, I tried the previous formula in the IRL spreadsheet (adjusting for correct columns and row numbers) and it didn't seem to take.
I figured the rule would still work except on the ones where "On File" is the text, since there isn't a match. But it didn't work for things that should've applied.
Maybe it'll like this one better, though.