Using INDIRECT Function in Google Sheets conditional formatting

[natemoss1218]

Hi guys!

I have a column that I need to color with conditional formatting based on two different criteria. I am using the following formula in the conditional formatting pain and it does work: =AND(AC2="Yes", AK2=INDIRECT("Partners!B6"))

The issue is I need the range of Partners!B6:B not just Partners!B6. I did it the way above just to double check my logic to make sure I was on the right path. So, for the second logical expression, I need to see if AK2 is equal to a name in the list of partner names that are in column B on the Partners tab (The list starts in B6 due to the headers). I am not sure what I need to do to achieve this. If needed I can create a sample, but I can't provide what I am working on due to private info etc.

Hopefully this is enough information, thanks in advance for all the help!

 

[RoryA]

It sounds like you possibly want:

=AND(AC2="Yes", COUNTIF(INDIRECT("Partners!B6:B"),AK2)>0)

 

[natemoss1218]

Worked perfect, you are the real MVP Rory