Hi Jon! Thanks. I tried to create the logic where a Series in a chart on
the Chart Page would refer to the drop menu cell on the Chart Page, but no
dice. I tested the INDIRECT reference in a plain cell on the Chart Page
which worked fine. However, in trying to make one of the Series in one of
the Charts refer to the Chart Page to pick up the entry in the drop menu to
feed the INDIRECT, it threw an error.

I tried this:

Cell A6 on Chart Page - Drop Menu of Region Sheets to feed the Charts using
a Named List/Range

In Chart A - Regional Widgets, in Series Values for Series 1 (pulls from the
Region A Summary sheet), where it currently reads:

=RegionA!$K$189:$AD$189

I tried to insert an INDIRECT formula to refer to Cell A6 on the Chart Page
to trigger a dynamic reference to the various summary sheets for the regions:

=CHARTPAGE!(INDIRECT("'"&!$A$6&"'!$K$189:$AD$189") )
in hopes that the =CHARTPAGE!(INDIRECT("'"&!$A$6 portion would resolve to
RegionA! or RegionB!, etc. when the drop menu on Chart Page was changed.

Excel spit up on me. ;)
--
Greg


"Jon Peltier" wrote:

It's going to require at least a little bit of code, to populate a
dropdown with sheet names, unless sheets are never added or deleted.

You can use the dropdown to change the sheet name in a cell, then use
INDIRECT() to construct and address using this sheet name and the
relevant cell range.

The INDIRECT can be in the chart source data range. The dropdown changes
the INDIRECT address, this changes the source data, and this changes the
chart.

- Jon
-------
Jon Peltier
Peltier Technical Services, Inc.
http://peltiertech.com/


On 3/6/2010 7:31 PM, Greg in CO wrote:
Hi Jon!

Can you recommend a macro or formula which will execute the same
functionality as in your spiffy Chart Formula Editor (the change Sheet Name
function specifically) that could be executed on the Chart Page via button or
drop menu vs. using an Add-In. So when the charts are fed by SheetA, I
select another Sheet Name (SheetB) from a drop menu and when that name is
displayed in a cell (or after I display it and push a button) all the charts
now pull from SheetB (all ranges, series, data being the same of course)

I have posted a similar question over on the Charts thread, as I was not
sure a macro would be the best solution, as my users will have to maintain
the workbooks.

Thanks again! Cool Add-in!

:)

Greg
.