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

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?

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

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?

• excel
• excel-formula
• conditional-formatting
• named-ranges

• 1 IIUC, should be doable using a rule based on XLOOKUP. – BigBen Commented Mar 3 at 20:25
• @BigBen - Oooh, as well-versed in Excel formulas as I am, I must admit I've seldom used XLOOKUP, will look in to that, thanks! – BruceWayne Commented Mar 3 at 21:22

1 Answer 1

Paste this in a new sheet:

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.