Formatting Output Range in Combo Box

[V2]

I am trying to make the list presented in a combo box dynamic based on the
selection of from another combo box. Here is the scenario... I have three
clients each having an associated list of offices. By selecting the client
name in combo box1 I would like the list of offices to change in combo box2.

Assuming I get the syntax right can I use the INDIRECT function to vary the
input range of a combo box. I am sure there is a way to do this using VBA
but I am not a VBA user.

 

[Shane Devenshire]

Hi,

Its easy to do this with Data, Validation. Here are the steps:

Here are the steps for creating a dynamic data validation list:

1. Create three or more list in the spreadsheet. For example:
A B C
New York Quebec Canada
Seattle Toronto US
Miami Victoria

You would name lists: US (A1:A3), Canada (B1:B3), Countries (C1:C2) this
last is optional

2. Select the cell where you want the initial validation list, for this
example, D1
3. Choose Data, Validation
4. From the Allow drop-down and choose List
5. In the Source box enter the following formula
=Countries
6. Move to the location where you want the dynamic (changing) list to
appear, say for example E1
7. Choose Data, Validation, List and in the Source box enter the formula
=INDIRECT(D1)

You can also make a self-referential dynamic data validation list.

 

[V2]

Shane:

Thanks for the very prompt response... I was actually hoping to use the
combo box control as I need the selection of a specific office to drive data
entry from a very large table of data. By flagging the choice of a specific
office (or row in the table) I can pluck data as needed.

Do you know if I can use the INDIRECT function in the control format
dialogue? Is there a trick because whenever I try I get an invalid reference
error.

Thanks,