Uniquely Identify Data for Charting with a List Box

[Guest]

Gang,

The Internet is a great thing. I found a charting example that does nearly
every thing I want to do. Amazingly enough, I was able to figure out how the
thing works! The chart is fairly simple. It is a X*Y line chart with 2
series. I found the chart here:

http://peltiertech.com/Excel/ChartsHowTo/ChartFromDiffSheets.html - The
chart is called "ChartAgainstStandard.zip."

One of the 2 series plotted is a "standard" and the other changes based on
the selection made in the drop down list. Amazingly enough, I was able to
follow the thing completely and I would like to model one after that. Mine,
however, will be a little different (aren't they always). Whom ever created
the chart, had each of the 4 series on separate sheets (names of sheets
corresponded to the names in the drop down list). That unique name was used
in the cell reference to point to the correct sheet for charting. This was
the formula used
:INDIRECT("'"&INDEX($O$3:$O$6,$O$1)&"'!"&ADDRESS(ROW(B1),COLUMN(B1))).
Unfortunately, my data is not set up in separate sheets and I'm at a loss as
to how to uniquely identify the correct data for plotting. My only thought
was to use named ranges.

Here's a chunk of data. All the data is in a single worksheet, in fact, it
is inside a pivot table. Mind you, it doesn't have to be. I can pull it
from two worksheets if need be. I put it all into a single table because I
thought it would make it easier to plot (certainly to look at). Anyhow, the
data that I want to plot came from two pivot tables.

CountyName ModelSource 1981 1982 1983 ... 2006
Adams ASM 220 300 400 5,500
Adams DLW 140 200 275 4,400
Allen ASM 220 300 400 5,500
Allen DLW 140 200 275 4,400
Ashalnd ASM 220 300 400 5,500
Ashland DLW 140 200 275 4,400
..
..
..
Wyandot ASM 220 300 400 5,500
Wyandot DLW 140 200 275 4,400


As you might imagine, year will be the x-axis and the population estimates
will be my y-axis. There will be 2 series - ASM and DLW, population
estimates derived from different models. If anyone can help me with some
ideas or examples on how I might "point" to the correct data for plotting, I
would really appreciate it.

Regards,

Mike

 

[Del Cotter]

Gang,

The Internet is a great thing. I found a charting example that does nearly
every thing I want to do.

Whom ever created the chart, had each of the 4 series on separate
sheets (names of sheets corresponded to the names in the drop down
list).

It's Jon Peltier, who contributes to this newsgroup regularly.