I have a spreadsheet that lists 575 departments with monthly totals for each
department. I need to create individual charts for each department that show
the monthly totals. I was able to create a dropdown list where I can pick
the department but I can't figure out how to correlate the correct row of
data to that department when I change it.

Example:

Dept Jan Feb Mar Apr May, etc.
500 x x x x x
501 x x x x x
502 x x x x x
503 x x x x x
504 x x x x x
etc.

I need to be able to have one chart, then a dropdown (or some selection)
that I can use to pick which department I want to chart, and upon making that
selection - the chart data changes to reflect the correct numbers for that
particular department. Any help would be greatly appreciated.

Hi BUD1888,

Go here http://edferrero.com/ExcelCharts/tabid/102/Default.aspx

Download 'Chart Selector'

Ed Ferrero
www.edferrero.com

Hi,

You didn't tell us what kind of drop down list you used, let's suppose it
was a combobox for the Forms toolbar, it can have a linked cell which return
a number to the spreadsheet which indicates which item was picked. Suppose
A2 is the row for the first department with the name in column A and the 12
months in B2:M2. The plot range for the chart could contain formulas such as

=OFFSET(A1,LinkCell,COLUMN(A1))

You can copy this formula across the range you intend to plot and it will
pick up the Name and the 12 months worth of data for the department pick in
the dropdown. Note, LinkCell just represents the cell that the Combobox
control is linked to.

If this helps, please click the Yes button.


"BUD1888" wrote:

I have a spreadsheet that lists 575 departments with monthly totals for each
department. I need to create individual charts for each department that show
the monthly totals. I was able to create a dropdown list where I can pick
the department but I can't figure out how to correlate the correct row of
data to that department when I change it.

Example:

Dept Jan Feb Mar Apr May, etc.
500 x x x x x
501 x x x x x
502 x x x x x
503 x x x x x
504 x x x x x
etc.

I need to be able to have one chart, then a dropdown (or some selection)
that I can use to pick which department I want to chart, and upon making that
selection - the chart data changes to reflect the correct numbers for that
particular department. Any help would be greatly appreciated.