Data validation based on two named ranges, without vba

Assume List1 is in column A, and List2 is in column B. Create a third list, List 3 in column C

A1=List1; B1=List2; C1=List3

Then, in cell C2:

Formula:

=IF(ROW(C2)<=COUNTA(A:A),INDEX(A:A,ROW(A2)),IF(ROW(C2)<=(COUNTA(A:A)+COUNTA(B:B)-1),INDEX(B:B,ROW(A2)-COUNTA(A:A)+1),""))

and drag down until you get blank entries.

DNR for List1:

Formula:

=Sheet1!$A$2:INDEX(Sheet1!$A:$A,COUNTA(Sheet1!$A:$A))

DNR for List2:

Formula:

=Sheet1!$B$2:INDEX(Sheet1!$B:$B,COUNTA(Sheet1!$B:$B))

DNR for List3:

Formula:

=Sheet1!$C$2:INDEX(Sheet1!$C:$C,COUNTA(Sheet1!$C:$C))

Use List3 for your Data Validation List.

Formula:

=List3

Note: depending on how far you drag down the List 3 formula in column C, you may get some blank entries on the DV List.

You could also use the Named Ranges for List1 and List2 but the formula is a little messier:

Formula:

=IF(ROW(C2)<=COUNTA(List1)+1,INDEX(List1,ROW(A2)-1),IF(ROW(C2)<=(COUNTA(List1)+COUNTA(List2)+1),INDEX(List2,ROW(A2)-COUNTA(List1)-1),""))

You're welcome.