Cascading Combo for Chart Source

  • Thread starter Thread starter Astley Le Jasper
  • Start date Start date
A

Astley Le Jasper

I have found some examples of how to use a combo to set the data
source of an excel chart (see http://peltiertech.com/Excel/Charts/ChartByControl.html),
using a combo to set an offset.

In this case I'd have a combo box of counties which return number
which is used as the offset. The relevant row is then used as the
source for the chart.

COUNTY WEEK01 WEEK02 WEEK03 WEEK04
Staffordshire 1.1 1.2 1.4 2.1
Derbysire 2.1 3.3 4.1 2.2
....

However, I have another spreadsheet that has the monitoring stations
within counties. There are hundreds so it wouldn't be great to have in
a combo. For example:

COUNTY STATION WEEK01 WEEK02 WEEK03 WEEK04
Staffordshire Station01 0.02 0.02 0.02 0.02
Staffordshire Station02 0.02 0.02 0.02 0.02
Staffordshire Station03 0.02 0.02 0.02 0.02
Staffordshire Station04 0.02 0.02 0.02 0.02
Derbyshire Station02 0.02 0.02 0.02 0.02
....

<<<<<<<< QUESTION >>>>>>>>
Is there are way of selecting a county from one combo, which then
populates a second combo with all the stations. Then somehow that
would return a appropriate value that could be used to set the source?

Cheers

ALJ
 
Jon used a dropdown from the Forms toolbar as the dropdown in that workbook, but
it looks that the linked cell was the important piece of info used.

Debra Dalgleish shows how to use dependent lists with data|validation here:
http://contextures.com/xlDataVal02.html

It looks like you could either replace the dropdown with a data|validation cell
and then use Debra Dalgleish's technique or you could use Debra's technique to
name the possible lists (in a different worksheet) and use that for the input
range of the second dropdown.
 
Back
Top