Data Validation fromn two different named range alternatives

Can't you simply combine the lists to form a single list and then use that list as the source for the drop down?

Do you know what the maximum number of items can be for each list?

For example, even though each list currently has 7 or 8 items, each list will never have more than 20 items.

You could do this...

In some out of the way location on the sheet where you want the drop down lists...

Enter the items that make up each list but leave some empty cells between the lists for future data addition.

For example, enter list1 in the range Z3:Z43 and enter list2 in the range Z45:Z85.

Now, let's use a formula to combine these 2 lists into a single list. This new list will be the source for the drop down.

Enter this array formula** in AA3:

=IFERROR(INDEX(Z:Z,SMALL(IF(Z$3:Z$100<>"",ROW(Z$3:Z$100)),ROWS(AA$3:AA3))),"")

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Copy down to AA100.

Now, as the source for the drop down lists use this formula:

=OFFSET(AA$3,,,COUNTIF(AA$3:AA$100,"?*"))

I'll take a look at it tomorrow. Been kind of busy today.

You might be better off using a VBA procedure to do this.

There's no way using formulas that you can add to the data validation list. You have to use an event macro for that.

I'm not much of a programmer so I can't help with this.