Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Data Validation List
I want to be able to enter a Post Code (Zip Code) in a cell and then using
the Vlookup function find the first occurence of that in the post code list (sorted by code), then find the last occurence, and use these two values as the start end end points for a data validation list so that the user can select the desired location from the list of locations having the same post code. |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Data Validation List
You can use an OFFSET formula to create the data validation list. For
example, if Post Codes are in column A, and locations are in column B, select to Allow: List, the enter this formula in the Source box: =OFFSET($A$1,MATCH(D2,$A:$A,0),1,COUNTIF($A:$A,D2) ,1) where the selected Post Code is in D2 Ken G. wrote: I want to be able to enter a Post Code (Zip Code) in a cell and then using the Vlookup function find the first occurence of that in the post code list (sorted by code), then find the last occurence, and use these two values as the start end end points for a data validation list so that the user can select the desired location from the list of locations having the same post code. -- Debra Dalgleish Contextures http://www.contextures.com/tiptech.html |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Dynamic Data Validation List
Assume your table is in the range A1:B15
D1 = zip code Source for the drop down list: =OFFSET(B$1,MATCH(D$1,A$1:A$15,0)-1,,COUNTIF(A$1:A$15,D$1)) Biff "Ken G." wrote in message ... I want to be able to enter a Post Code (Zip Code) in a cell and then using the Vlookup function find the first occurence of that in the post code list (sorted by code), then find the last occurence, and use these two values as the start end end points for a data validation list so that the user can select the desired location from the list of locations having the same post code. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Manual control of link updating for downloaded quotes? | Excel Discussion (Misc queries) | |||
data validation invalid in dynamic validation list | Excel Worksheet Functions | |||
Data Validation List Length | Excel Worksheet Functions | |||
Printing data validation scenarios | Excel Worksheet Functions | |||
named range, data validation: list non-selected items, and new added items | Excel Discussion (Misc queries) |