Indirect/Substitute combobox to populate graph

**PRodgers** · 09-01-2009, 10:05 AM

I have created a worksheet that uses two comboboxes to generate a chart based on the values that have been selected and this is working well. However i would like to use the INDIRECT/SUBSTITUTE function to display specific values based on the selection of a combobox. I have used the INDIRECT function before using the validation list option but i want to use it with a combobox, can anyone help me/provide me with some advice on how to do this?

My workbook at present:

**NBVC** · 09-01-2009, 10:14 AM

Can you elaborate with an example of what you mean?

**PRodgers** · 09-01-2009, 10:27 AM

Originally Posted by NBVC

Can you elaborate with an example of what you mean?

NBVC i basically want two comboboxes one "Hospital" and the other "Ward", i want when i user selects a hospital only the wards in this hospital are available. The above workbook that I posted is the same kind of idea of what i want but i want to use the INDIRECT/SUBSTITUTE function to restrict what is being displayed in the comboboxs, im unsure how to use this function within a combobox. Any help/advice is much appreciated.

**NBVC** · 09-01-2009, 10:55 AM

Create a named range for the dynamic range.

So If, for example on your attached example you have the Vehicle combobox and say you have different combinations of fuel types based on vehicle chosen.. so now you would have separate named ranges for Motorcyle, Car, Bus, etc. listing the fuel types....

Then you can use the B2 result (which displays the choice of vehicle you made in a cell)... and name a range, eg. DynamicRange and use formula =Indirect($B$2)

Now, the Fuel combobox, input range would be DynamicRange

I did a quick sample on the attached...

**PRodgers** · 09-02-2009, 05:30 AM

Originally Posted by NBVC

Create a named range for the dynamic range.

So If, for example on your attached example you have the Vehicle combobox and say you have different combinations of fuel types based on vehicle chosen.. so now you would have separate named ranges for Motorcyle, Car, Bus, etc. listing the fuel types....

Then you can use the B2 result (which displays the choice of vehicle you made in a cell)... and name a range, eg. DynamicRange and use formula =Indirect($B$2)

Now, the Fuel combobox, input range would be DynamicRange

I did a quick sample on the attached...

NBVC thanks for you help, thats works exactly the way i want it.