I have few hundred cities & provinces in one column
Eg: Calgary, AB (City, province)
Next column has 'store names'
eg: Store 1
On the main page, user can type in/choose city via data validation.
Now I want to give the user a chance to select the store location via another data validation list but those that comes in within the user-selected city, province only
Whats the best way to do this without going into VBA? (using formulas only)
One way is, assign stores with same city under a named range
Say I create a named range of "B2:B15" as 'Calgary, AB'
Assuming user chooses city on cell A1 and store on cell A2
on A2, I can do data validation as
However, this would require 100s of named ranges (or 1 for each city)
Any other ways without using macros?