Data Validation with formula to calculate lists

[brooksc29]

I am trying to use data validation in a cell that will return a list of names depending on criteria in another cell.

I'd like to say with a formula that will return a list in cell J1
If D1 says "ML", return a drop down list from the range "mlteams", if D1 says "MiL", return a drop down list from range "milteams". Seems simple enough but I'm entering the formula in the data validation cell and getting an error.

Please help!

 

[cyrilbrd]

Given in D1 either 'Mil" or 'Ml'.
Given two named ranges 'mlteams' and 'milteams'
Given in a cell of your choice a data validation as follows:
Allow: List
Source: =IF(D1="Ml",mlteams,milteams)
Click OK.

In said cell the data validation will display either the data of 'mlteams' or of 'milteams' based of the value found in D1.

Would that work for you?