I have a spreadsheet that does something similar to what
you're asking...
On sheet two, make the first column your lookup range. In
cell A1, you want to refer back to the cell on sheet one
in which the state is typed. For example, if the state is
entered in A1, then enter =Sheet!A1 in cell A1 of sheet
two. In the cells below, enter a lookup formula that's
going to refer to the columns to the right. For example,
if your states are listed in C1:H1 with the respective
cities listed below, enter this formula in A2: =Lookup
($A$1,$C$1:$H$1,C2:H2) Copy this formula down column A
for as many cities as you have in the longest column. So
now whatever state is entered in A1 on sheet one will
drive the data in column A of sheet two. Name the range
from A2 to A50 (or whatever) as States.
On sheet one, you'll set up your data validation, say in
cell C1. Choose Data -- Validation -- List and for the
source, type in =States
When the state changes in cell A1, the list will change in
C1.
Hope that helps!
-----Original Message-----
I have a workbook with 2 worksheets. The first worksheet
has two columns for data entry, "States" and "Cities".
The second worksheet has a list of all the Cities and
their corresponding State. In the first worksheet under
the States column, I've set up a data validation list with
all 6 possible states to choose from. Let's say each
state has 10 cities each. Instead of having a data
validation list of all 60 possible cities, I was wondering
if there is a way to have my drop down list for cities
show only the choses that relate to the state chosen in
the first column, meaning just the 10 cities that relate
to the state picked in column A. I didn't know if there
was a way to do a v-lookup with a data validation list or
if there's another way around this. Thank you.
