Self created validation list

Searched the forum...can't find exactly what I am looking for.

Suppose I have a list of team names in Column A, and a different list of team names in Column D....

How do I create a validation list in CELL E1 that will contain the team names in CELL A1 and CELL D1??

Specifically, what I want, is a formula to enter into the validation for each cell in Column E that will look in cells A:A and D:D and offer those two teams as options in the drop down list...even if the team names are changed.

Is this possible....???

Point the validation list at a helper column of =INDEX(CHOOSE(MOD((ROW()),2)+1,A:A,D:D),INT((ROW()+1)/2))

If you try selecting a multiple range then Excel rather snootily informs you that "You may not use unions, intersections or array constants for Data Validation criteria".

A common way to get round some of the constraints that Excel imposes is to use a named range, but Excel insists that "The list source must be a delimited list, or a reference to single row or column".

Yep....that's exactly what I encountered.

I took the easy way out and inserted two columns to the left of my validated column...
and copied the following validation source formula down column G:

=$E1:$F1

I then just hid the two list columns.

Not the prettiest thing...but it works.

Does link help

Look under >> DV0014 - Combine Multiple Lists into One

Not really....but that's some pretty cool stuff.
I couldn't quite figure out how to apply that example to my situation.

Thanks for your feedback, as always, VBA noob.



mikerickson...

I entered your formula into the source box for my data validation.
I was greeted with a message that said that "the formula that I have entered calculates to an error".

Perhaps I didn't enter the formula correctly....

Also...if I get the formula to work...can it be copied down the entire column?

Its the same concept as your solution. Glad you found a work around.