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 document when I received some documentation from vendors, highlighting documentation received within the last 6 months BUT, some of the documentation covers multiple vendors.
In the IRL spreadsheet, there are over 500 individual "brand codes" representing various vendors and including some but not all distributors.
I'd like to be able to have a row highlight if another referenced row is highlighted, but I need it to search to find the reference and see if the condition is applied.
Ex. If brands 4, 5, and 6 are covered under brand 2's document, and that document is within the last 6 months, I'd like to basically say "see brand 2" for them and have them highlight.
I've attached a sample of how it's setup.
The only rule in this file right now is for the date.
But what I'd like to do is set it up so that column (column D) will (1) determine if column D is text (as opposed to a date or blank), (2) if it's text to find a match in column A, (3) and see if the date in column D of the matching row is less than 6 months (and (4) highlight).
Is that a thing I can do?
6 Replies
=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.
