Dynamic Data Validation List

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
 
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
 
Back
Top