excelexcel-formulaconditional-formattingnamed-ranges

Create Dynamic Named Range, based on adjacent value

Without using VBA (need to save as .xlsx), is there a way to create a dynamic named range?

Name            Include
Dr. Nick        Yes
Dr. Hibbert     No
Dr. J           No
Dr. Spaceman    Yes

I am trying to conditional format another table, where the "Name" is to be Included.

What I have tried is creating a named range, =Index($A$2:$A$5,Match("Yes",$B$2:$B$5,0)).
Unfortunately, this only seems to grab the first name in the list (Dr. Nick above).

It's possible this is an X/Y Issue, so my real ultimate goal is to highlight cells in one table, if in another table a value adjacent to that cell, is "Yes".

enter image description here

So if someone goes in and updates my top table, say putting "Yes" next to Hibbert and J, then the table with that highlighting automatically highlights the additional two.

Is this possible without VBA?

Solution

  • Paste this in a new sheet:

    Name Include Name City
    Dr. Nick Yes Dr. J Philadelphia
    Dr. Hibbert No Dr. Spaceman NYC
    Dr. J No Dr. Hibbert Springfield
    Dr. Spaceman Yes Dr. Nick Springfield

    Apply a conditional format to range D2:E5 with this formula:

    =COUNTIFS($A:$A,$D2,$B:$B,"Yes")

    In case you want to apply the rule to a different range (say the entire column) remember to change the row of $D2 so it is the first row of the range you've applied the conditional formatting. Of course if you change also the column, you should change the column too; just keep the reference absolute.