Data validation limits

[pigeony68]

Hi, can somebody help me please.
I am trying to creat data validation drop downlists using the "if" function,
in cell "a1" there are 27 variables and i have no problem with that its fine, but i want a diffent drop down list to appear depanding what is selected in "a1". For some reason i can only do 9 nested if functions in the data validation before it appears that i have run out of typing space, does anyone have any suggestions please. Bewlow is an example of the type and length of formula that i am trying to fit in.

=IF(F15=Table41[[#Headers],[Andrew Pollock]],Table41[Andrew Pollock],IF(F15=Table40[[#Headers],[Adrian Brookes]],Table40[Adrian Brookes],IF(F15=Table39[[#Headers],[Arun Bejugam]],Table39[Arun Bejugam],IF(F15=Table38[[#Headers],[Corinne Lewis]],Table38[Corinne Lewis],IF(F15=Table37[[#Headers],[Dean Edney]],Table37[Dean Edney],IF(F15=Table36[[#Headers],[Dominique Ritchie]],Table36[Dominique Ritchie],IF(F15=Table35[[#Headers],[Fran Vaughan]],Table35[Fran Vaughan],IF(F15=Table34[[#Headers],[Hemant Kalidas]],Table34[Hemant Kalidas],IF(F15=Table33[[#Headers],[Ian Plaistow]],Table33[Ian Plaistow],IF(F15=Table32[[#Headers],[John Bell]],Table32[John Bell],IF(F15=Table31[[#Headers],[John Elnaugh]],Table31[John Elnaugh],IF(F15=Table30[[#Headers],[John Johnstone]],Table30[John Johnstone],IF(F15=Table29[[#Headers],[Karen Hicks]],Table29[Karen Hicks],IF(F15=Table28[[#Headers],[Karl Adams]],Table28[Karl Adams],IF(F15=Table27[[#Headers],[Keith Whitehead]],Table27[Keith Whitehead],IF(F15=Table26[[#Headers],[Linda Cawthorne]],Table26[Linda Cawthorne],IF(F15=Table25[[#Headers],[Mandy Blackwood]],Table25[Mandy Blackwood],IF(F15=Table24[[#Headers],[Mata Gupta]],Table24[Mata Gupta],IF(F15=Table23[[#Headers],[Nick Kumar]],Table23[Nick Kumar],IF(F15=Table22[[#Headers],[Nicky Baker]],Table22[Nicky Baker],IF(F15=Table21[[#Headers],[Paul Anderson]],Table21[Paul Anderson],IF(F15=Table20[[#Headers],[Phil Hills]],Table20[Phil Hills],IF(F15=Table17[[#Headers],[Rana Ashraf]],Table17[Rana Ashraf],IF(F15=Table19[[#Headers],[Raudy Lockhart]],Table19[Raudy Lockhart],IF(F15=Table13[[#Headers],[Rebeca Thomas]],Table13[Rebeca Thomas],IF(F15=Table11[[#Headers],[Roy Dillon]],Table11[Roy Dillon],IF(F15=Table8[[#Headers],[Vicki Parnell]],Table8[Vicki Parnell],"")))))))))))))))))))))))))))

Your help would be much appreciated.

Thanks

Martin

 

[Gerald Higgins]

Sorry, this isn't clear.

Are you saying you want to create two drop down lists ?
1 in a1, which you have, and one in some other cell, whose options will be determined by the choices made in A1 ?

 

[pigeony68]

The names in the formula are team managers and the all have different people working for them, when the team managers name is selected in cell "a1" i want data validation to only display there team members. i have also just been playing around with trying to put the hlookup formula in the custom data validation with no joy.

 

[Brian from Maui]

See

http://www.mrexcel.com/forum/showthread.php?p=126

on dependant lists

 

[pigeony68]

Thankyou very much