Data Validation question

[LGMan]

Hi, I've got a list of retailer addresses held within 2 columns. Col A holds the city, while col B holds the retailer.
I'd like to create a couple of Validation Lists, so that Validation 1 would allow you to select the city, and then Validation 2 would display only those retailers within the chosen city.

I've done this before, but my data was arranged differently. where my range of Cities would be A1:V1 and A2:A9 was a Range called London and held the Retailers. Range Paris was B2:B9 and so on. I used the =Cities as the source of the first Validation, and =INDIRECT(SUBSTITUTE(B2," ","_")) as the source of the second validation.

So my question, can validation lists work if the data is only in a range of A:B so doesn't have the named ranges of the previous paragraph?
If not could I get Excel to rearrange my data to the format with it's named ranges as descibed in the previous paragraph?
or is there another method that I should use.?

Thanks in advance

 

[SkipVought]

Hi,

What a mess to have separate ranges for each city!

One column with a named range CITY, and the other named range RETAILER.

THen use the OFFSET() function to return the sub range for the first DV selection as the DV - list for the second DV.

Example:
thread68-1688815


Skip,

_{Just traded in my old subtlety...
for a NUANCE!}