Can the data validation list range of one cell be driven by thecontents of another cell?

[Ciarán]

I'm trying to populate 2 cells using pre-defined lists and force the
second cell to be dependent on the contents of the first.

The example below illustrates what I'm trying to achieve.

Using a table like this:

ColumnA ColumnB
Ciaran Football
Ciaran Basketball
James Running
James Swimming


In CellA2, I want to use data validation to give the user the option
to choose either Ciaran or James. Oh, I also need this selection to be
unique - I don't want Ciaran or James to appear twice.
Then in CellB2 I want to use data validation to give the users the
option to choose from ColumnB, restricted to those options which make
sense.
So if the user selects Ciaran in CellA2, they should only be offered
the choice of Football or Basketball in CellB2.

 

[Bony Pony]

Hi,
I use INDIRECT for this.

Col A Col B
1 Player_1 =indirect(a1)
2 Player_2 =indirect(a2)
3 Player_1 etc
4 Player_2
etc

elsewhere in the sheet I have a named range called Player_1 which contains
the sports that Player_1 is linked to

e.g.
Player_1 Player_2
Football Darts
Tennis Snooker

You need a named range for each player in col A.

HTH

Regards,
Bony

 

[Ciarán]

Hi Bony,

I'm struggling with this.

If I do

Col A Col B
1 Player_1 =indirect(a1)

the result of =indirect(a1) is #REF!

Regards,
Ciarán

 

[Bony Pony]

Hi Ciarán,
The indirect formula needs to be entered in the Data Validation dialogue.
Select Data Validation, Select LIST and type the indirect formula into the
box.

Player_1 must be a named range including the sports Player_1 can play.

HTH
Regards,
Bony