CODEWITHSUNDEEP
excelexcel-formulaconditional-formattingnamed-ranges
BruceWayne
BruceWayne

Reputation: 23285

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?

Upvotes: 0

Views: 40

Answers (1)

Evil Blue Monkey
Evil Blue Monkey

Reputation: 2794

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.

Upvotes: 3

Related Questions