Thread: One Chart, Same Data ranges, different source sheets

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

--
Greg


"Ed Ferrero" wrote:

> 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:

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!


--
Greg


"Greg in CO" wrote:

>
> --
> Greg
>
>
> "Ed Ferrero" wrote:
>
> > 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:
>
> 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!