Nov 24 2021 10:57 AM
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?
Nov 24 2021 11:11 AM
=AND(ISTEXT($D2),TODAY()-VLOOKUP($D2,$A$2:$D$17,4,FALSE)<180)
Is this the rule for conditional formatting you are looking for?
Nov 24 2021 11:19 AM
Nov 24 2021 11:30 AM
=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.
Nov 24 2021 11:34 AM
Nov 24 2021 12:42 PM
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.
Nov 24 2021 01:12 PM