Data Validation in cell X depending upon the value in cell Y?

[gjfreem]

I have a worksheet with a list of teams: A, B, C, etc and in cell a1 I have data validation set to that list, so that the team names can be picked from a drop-down

I then have separate lists for the name of people in each team. E.g:
Team A has a list containing alice, bill, george, etc
Team B has a list containing brian, dave, sue, etc
:
:

What I'd like is,
if I select Team A in cell a1, to be able to have JUST Team A's list available from the drop down in cell b1.
but if I select Team B in cell a1, to be able to have JUST Team B's list available from the drop-down in b1
ETC, Etc, etc ...

Any ideas how this might best be achieved, please?

[tigeravatar]

gjfreem,

I think this is what you're looking for:
http://www.contextures.com/xlDataVal02.html

[vishnu01445]

we can achieve it with indirect function...im not good at explaining. please find the attached sheet.

[gjfreem]

gjfreem,

I think this is what you're looking for:
http://www.contextures.com/xlDataVal02.html

Many thanks - that worked perfectly.

---------- Post added at 05:45 PM ---------- Previous post was at 05:44 PM ----------

we can achieve it with indirect function...im not good at explaining. please find the attached sheet.

Thanks too - this is a clear example