Problem with dependent drop-down list

Question

Hi,

I have a problem, which is the following: I have two drop-down lists (column B and C) where the selection for list #2 depends on list #1. However, this only works perfectly for the first cell of column B and C. This is because the formula contains a reference to B3 >>> =SORT(UNIQUE(XLOOKUP(Sheet1!$B3;Table2[#Headers];E3:H9)))).

Meanwhile, I tried all kinds of possible solutions, with no result. I have no idea how to solve this.

Who can help me?

Thanks in advance!

Answer 1

It turned out to be a problem with the naming of the ranges.

Answer 2

We're out of luck...

Answer 3

My bad! Use SUBSTITUTE instead of REPLACE.

Sorry about that.

Answer 4

Thanks for your solution @HansV MVP :-)

Defining names goes without any problem.

What is going wrong when setting the Data Validation Source?

Cheers,

/Henk

Answer 5

Create defined names

Administrative_risks referring to =OFFSET($E$2, 1, 0, COUNTA($E$2:$E$9), 1)

Clinical_risks referring to =OFFSET($F$2, 1, 0, COUNTA($F$2:$F$9), 1)

etc.

Set the Source of the data validation rule in C3 and down to

=INDIRECT(REPLACE(B3, " ", "_"))