One Chart, Same Data ranges, different source sheets

  • Thread starter Thread starter Greg in CO
  • Start date Start date
G

Greg in CO

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!
 
--
Greg


Ed Ferrero said:
Hi Greg in CO,

The ADDRESS function will accept a sheet name from a cell reference
=ADDRESS(rowNo,columnNo,1,0,SheetName)

Combine this with the INDIRECT function to get the value of the cell with
that address.

There is a sample showing how to build charts using this method at
http://edferrero.com/ExcelCharts/tabid/102/Default.aspx

Look for the 'Reporting' sample.

Ed Ferrero
www.edferrero.com


.
Hi Ed! Thanks for the info...those examples are great....however, not quite where I was going. What I am trying to do, in a nutshell, is modify this example:
Click to expand...

I have a set of Summary sheets named RegionA, RegionB, RegionC, etc. Each
of these sheets is exactly the same in layout, except for the Sheet name.
Each of these sheets is fed by subordinate sheets, but the subordinate sheets
do not affect the charts.

I have a sheet of charts, fed by a summary sheet. Each Summary sheet needs
the same set of charts. Rather than create a set of charts for each Summary
sheet, I would like to do the following, if possible:

Charts! - the name of the Chart sheet
RegionA - the name of the Summary sheet for RegionA
Sample series data from one of the Charts on the Charts sheet:

Series Name: ="RegionA Wigets" (It's hard coded for the moment...ideally I
would link this to a dynamic cell or make it generic)

Series: =RegionA!$K$192:$AD$192

Desired behavior: Using a drop menu selection, the Series entry for
"RegionA!" would change to whatever was selected, thereby changing the entire
set of charts on the Charts sheet. So, after selecting the Drop Menu item
"RegionB", the Series would now read:

=RegionB!$K$192:$AD$192

This would apply to all the charts on the Charts page, allowing the user to
switch between regions without having to select other tabs or have the
associated workbook size for one with multiple chart sheets.

Ed, if your suggestion would make the change noted above, my apologies - I
didn't see how it worked in your example. Could you elaborate?

Thanks for your guidance and patience! :)
 
Hi Ed! I have been trying to modify the formulas from your example
spreadsheet, using INDIRECT and ADDRESS - no luck.

I have not used address before - I can get the formula to return a cell
value (the Cell address where the drop menu populates the sheet names from
the drop menu), but I am not sure how to integrate this with an INDIRECT
argument. Would that go into a Named Range formula? Would it go into the
SERIES formula?

I've tried various options - no luck.

I am baffled that trying to change the Sheet! reference in a chart has
turned out to be so complicated. I know there are 3rd party add-ins or VB
options in a macro - but I am trying to keep it simple for the end users to
maintain.

Thanks!

:)
 
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
 
Back
Top