ExcelBanter - Creating a list (data validation) fromt wo different source ranges

"tony" wrote:
wondering if it was possible to create a drop-down list
out of two different source ranges?
like creating a list from range A1:A50 and A80:A120?
then these would just appear altogether in one drop-down list?

One way to get it to work ..

Assume the discontiguous source ranges* are in sheet: X
possibly housed within A1 down to say A200
*may comprise several ranges, not just 2 ranges

In a new sheet named simply as say: Z

Put in A1:
=IF(ROW()COUNT(B:B),"",INDEX(X!A:A,MATCH(SMALL(B: B,ROW()),B:B,0)))

Put in B1:
=IF(X!A1="","",ROW())

Select A1:B1, copy down to B200
(cover the expected extent in X's col A)

Then click Insert Name Define, input:
Names in workbook: MyRange (say)
Refers to:
=OFFSET(Z!$A$1,,,SUMPRODUCT(--(Z!$A$1:$A$200<"")))
Click OK

Test it out ... In any sheet, create the DVs with Allow: List, Source:
=MyRange. The DVs' droplists will display the entire listing that's within
X's col A, wo the blank/empty cells in-between the source ranges.

And if you were to subsequently insert or delete rows within X's A1:A200,
just remember to re-enter the top row formulas in Z's A1:B1, & re-fill the
formulas to cover accordingly, post-insertion/deletion.
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---