Datavalidation by formula

[Flemming Dahl]

Hi all

I have a named list in one sheet2 with zipcodes and cities.
The zipcodes are NOT unike.....

In sheet1 I enter a zipcode in A1, and in B1 I would like a datavalidation
that gives me like the five cities that all have the zipcode from A1.

With witch formula can make the datavalidation show only the cities that
have the entered zipcode ?

Thanks,
Flemming

 

[Peo Sjoblom]

You can't use validation since you cannot name lists using a string of
numbers (zip codes)
Maybe by using filters and VBA you could do it but it won't be a one minute
solution

 

[Flemming Dahl]

Thanks Peo

I have the code for doing it with filters and VBA, I was hoping for the
posibility to insert a formula into datavalidation... but maybe not.

I know i can make named-ranges using a formula as the range for the
named-range so this named-range may be a different area each time. I might
have go that way then to avoid VBA for this solution.

Flemming

 

[Bernie Deitrick]

Flemming,

Let's say that your non-unique zipcodes are in the named range
ZipTable, which is a two column table with the zips in the first
column and the your city names in the second column.

Use this array formula (enter it with Ctrl-Shift-Enter), entered into
cell C1 on Sheet1:
=IF(LARGE((ZipTable=$A$1)*ROW(ZipTable),ROW())<>0,OFFSET(ZipTable,LARG
E((ZipTable=$A$1)*ROW(ZipTable),ROW())-ROW(ZipTable),1),"")
(Watch the line wrapping, and note that this formula is written to be
placed in a cell in row 1.)

Copy that formula down for at least as many rows as your maximum
number of city names for a single zipcode.

Then for cell B1, use a data validation linked to the list of city
names returned by the formulas starting in cell C1.

When the value in A1 changes, the list will change, and the data
validation list will change as appropriate.

HTH,
Bernie
MS Excel MVP

 

[Flemming Dahl]

Hi,

I have solved it this way:
Created a named cell as rCityList
The edited rCityList and changed the reference to:
=OFFSET(INDIRECT(ADDRESS(MATCH(Sheet1!$A$2;Sheet2!$A:$A;0);1;;;"Sheet2"));0;
1;COUNTIF(Sheet2!$A:$A;Sheet1!$A$2);1)

Made a Datavalidation with a list refering to rCityList

That did the job so very fine
Flemming