Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Creating a list (data validation) fromt wo different source ranges
hi everybody. i was 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? thanks. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Adding a blank in Data Validation List? | Excel Discussion (Misc queries) | |||
Data Validation Source Box | Excel Worksheet Functions | |||
Dependent List (via Data Validation) Error | Excel Worksheet Functions | |||
Creating a summary list from source data - can you?? | Excel Discussion (Misc queries) | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |