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

Share Follow asked Mar 3 at 20:12 BruceWayneBruceWayne 23.3k1616 gold badges7575 silver badges121121 bronze badges 2

• 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

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 3

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.