Hard-code source data

[Guest]

Is there any method other than using Dynamic Names/OFFSET to force a chart to
always use specific columns and rows, regardless of whether or not new ones
are added.

We have multiple spreadsheets that track monthly metrics. Each month a new
column is added representing a new month of data. I always want to show
columns B-M to refelct the past twelve months. Currently when I add a new
column representing a new month,the charts dymanically change to show columns
C-N and I have to change over 20 charts back to B-M.

Thanks!
Melanie Martin

 

[Jon Peltier]

Melanie -

The way to handle this is with dynamic names, or with VBA. Is there a
problem with using the dynamic names?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______

 

[William]

Hi Melanie

If you do not want to use dynamic names and you do not mind a macro doing
the job for you once a month, then...
1) Create a list of the names of each chart in, say, a new worksheet - name
the range, (without inverted commas), "chartlist".
2) In the column to the right ot your "chartlist" range, enter the source
data range for each chart (eg B2:M3)..
3) In the third column, enter the sheet name where the chart is located.

Then try the following code.....

Sub ChartRangeTest()
Dim c As Range, r As Range
Set r = Sheets("Sheet2").Range("chartlist")
For Each c In r
Sheets(c.Offset(0, 2).Text).ChartObjects(c.Text).Chart.SetSourceData _
Source:=Sheets(c.Offset(0, 2).Text).Range(c.Offset(0, 1))
Next c
End Sub

--


XL2003
Regards

William
(e-mail address removed)

 

[Guest]

Jon:
Thanks for responding. The only problem I have is the quantity of work I now
have to do. In researching this problem, I located an old post on this
problem in which you responded--so I went to your website to read the
resolution. I tried it and it works, but I just have soooo many to do, I was
wondering if there was a shorter way to accomplish the task. I know it's a
bit greedy but I thought it couldn't hurt to ask.

I am not a programmer, so if a task involves me writing code, I am at a
loss. I can go through and manipulate examples though.

Thanks again.
Melanie

 

[Guest]

Awesome. Thank you William for your help.
Melanie

 

[Jon Peltier]

Melanie -

Sure, it will take some time now, but every time these charts need to be
updated in the future, it will happen automatically.

William's approach works fine, except that you must remember to run the
macro. And in general, an approach without a macro is preferrable.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Peltier Technical Services
Tutorials and Custom Solutions
http://PeltierTech.com/
_______