Don't know whether this is what you already tried, but my approach would be:

Create a drop down on the chart sheet (dashboard), possibly using a
compbo box but knowing me more likely using data validation.
Either list the sheets directly, or in another hidden cell do a lookup
to a table to map the selection to s aheet name (eg "USA & Canada" might
map to a sheet just called "NorthAmerica")
Create named ranges which are local to your dashboard sheet (not
workbook scope) using offset and indirect, the indirect using the
selected sheet name. You need to do this for every series of every
[dynamic] chart.

So eg a range for Chart1_SalesSeries which uses OFFSET to select the
range on a sheet specified by INDIRECT plus some concatenated stuff for
the start cell reference.
Your indirect would look something like:
INDIRECT("'"&$M$22&"'!$C$7")
Where M22 on the current sheet has the name of the source data summary
sheet (and is probably better as a named range itself), and C7 is the
starting cell for the summary data range on that source sheet, from
which you will OFFSET by an appropriate amount for each series of data.
Note the single quote marks to wrap round any names with spaces and
other awkward characters.

For each series, replace the bits in the formula bar for the data values
with you new named range.
Wash, rinse, repeat. Tedious, but once built this will be pretty solid.

Alternative - use a single summary sheet to pull data into a single
table for all regions in a "normalised" layout rather than report style.
Use PivotCharts instead of normal charts, filter for regions (also added
bonus of being able to show totals, individuals entires, multiple
entries together etc.

Hope this helps
Adam

On 04/03/2010 22:30, Greg in CO wrote:
Hi All!

I have a sheet with numerous charts. The charts are standard, much like a
dashboard. The charts are fed from one sheet which is a rollup of summary
data from many other sheets. I need to create a similar summary sheet for
each of the remaining regions, but only need one sheet with charts. Is there
a way to make the SHEET reference for the charts dynamic - say, populated by
the user selecting an entry from a drop menu? The desired behavior is as
follows:

The Chart Sheet show 10 charts reflecting various data for Region A. I want
to be able to change all the charts to reflect the data from Region B, C, D,
E, etc., in lieu of having a chart sheet for each region (thus creating a
larger workbook).

The Region sheets are all exactly the same, the data ranges are the same for
all Region sheets, the chart types do not need to change. The only change in
the Charts is the sheet name for the source data.

I have tried named ranges, indirects, both of those together, Cell
references. I have found threads on changing data ranges, X/Y axis settings,
etc., but nothing on feed one set of charts by selecting the name of the
source sheet, thus changing the entire set of charts.

All help is appreciated!

Thanks!