Validation question

[Ladislav Ligart]

I have a State Lookup sheet containing states (natch):

AK
CA
TX

I have a Branch Lookup sheet containing branch offices:

Branch 1
Branch 2
Branch 3
Branch 4
Branch 5

Finally I have a State/City/Info Lookup sheet containing full info for
a given state & city:

AK Anchorage info info info
AK Junea info info info
AK Kodiak info info info
CA Hollywood info info info
CA Los Angeles info info info
CA Sacramento info info info
TX Austin info info info
TX Dallas info info info
TX Houston info info info

On a Primary sheet I want to be able to assign a given State/City/Info
record to one or more branches. Here's what the Primary sheet might
look like at a given point in time:

Branch 1 AK Anchorage info info info
Branch 2 AK Anchorage info info info
Branch 4 AK Anchorage info info info
Branch 1 CA Hollywood info info info
Branch 1 CA Los Angeles info info info
Branch 1 CA Sacramento info info info
Branch 4 TX Austin info info info
Branch 5 TX Austin info info info

Here's what I've got so far on the Primary sheet. In the 1st column a
Validation dropdown box allows choice of branch pulled from the Branch
Lookup sheet. In the 2nd column a Validation dropdown box allows
choice of state pulled from the State Lookup sheet. Here's the part
I'm stuck on: in the 3rd column I want a dropdown box showing ONLY
cities for that state. Once chosen, the remainder of the row is filled
in via VLookup from the State/City/Info Lookup sheet. (I know how to
do the VLookup).

The requirement of this exercise--and this is key--is that the
State/City/Info rows must remain TOGETHER as shown above on ONE lookup
sheet--no making separate ranges for each state thrown hither and yon
across the sheet (like all the fruit & color examples I've seen for
the Validation feature do). I need contiguous rows and everything in
its proper column so I can still sort and filter on the
State/City/Info Lookup sheet. This also means no splitting the
city/info records across separate state sheets.

:-G

 

[Jase]

have you tried using the indirect command in the third
validation box?

Ie. when the first two have been chosen you can have a
cell that does a vlookup on the data to determine which
range you want to use. Then in the data validation you
can view a list with the indirect lookup on the cell with
the vlookups in it. Hope this didn't confuse you to much

Jase
(e-mail address removed)