Replace element of chart source?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I need a one time solution to this problem, does'nt have to be elegant or
repeatable!:
I need to be able to change the sheet part of the chart source property
whilst not touching the cell references, which are currently correct.

My ideal solution is: to have a macro open up the chart and perform a 'find
and replace' action on the sheet source, leaving the whoel rest of the string
alone.


I am new to programming excel (though have a little exp in Access), so if
you DO have any suggestions please make them self explanatory!


Thanks in advance
 
This worked for a line chart:

Sub G()
Dim ch As Chart
Dim s As String, s1 As String
Dim ser As Series
Set ch = ActiveChart
For Each ser In ch.SeriesCollection
s = ser.Formula
s1 = Application.Substitute(s, "Sheet1", "Sheet2")
ser.Formula = s1
Next
End Sub
 
Thanks Tom, that worked a treat!

Tom Ogilvy said:
This worked for a line chart:

Sub G()
Dim ch As Chart
Dim s As String, s1 As String
Dim ser As Series
Set ch = ActiveChart
For Each ser In ch.SeriesCollection
s = ser.Formula
s1 = Application.Substitute(s, "Sheet1", "Sheet2")
ser.Formula = s1
Next
End Sub
Click to expand...
 
Tom,

That's a nice solution for copying a whole sheet of data and charts, and
changing the copied chart data source to the new sheet. It helped me do just
that, but I found an unexpected limitation. If the sheet name is hyphenated,
the <ser.Formula=s1> line seems to interpret it as a minus sign and the macro
halts at that line. Renaming the sheet is the obvious fix.
 
Sheet names with special characters, including spaces and hyphens, need to
be enclosed in single quotes:

'My Sheet'
'Your-Sheet'

I have a Chart Formula Editor on my web site that can change these aspects
of a series formula. When you enter your sheet names in the Change From and
Change To fields, it the sheet name needs single quotes, enter them around
the sheet name, and the editor will work.

http://peltiertech.com/Excel/Charts/ChgSrsFmla.html

- Jon
 
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
 
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
 
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. ;)
 
Back
Top