Conditional Formatting dependent on finding references

Copper Contributor

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.

JesMJ_0-1637779831714.png

 

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

@JesMJ 

=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?

@JesMJ 

=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.

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.

@OliverScheurich 

 

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!