I didn't mention that I use Excel version 2002. In my opinion this should be
addressed by MS as it would be much easier. Especially when you create an
exact copy of the original (they should behave in a way like - what first
has and does the second one repeats).
"Piotr (Peter)" wrote:
Thank you Jon for your prompt response. I have just started reading your
website. The only think is that my data is in a table that is in turn based
on cell reference to pivot table. So it is a massive table where I get my
ranges from, which expands when the pivot table does. Therefore I am just not
sure if I can use the solution from your website. I can e-mail a picture of
what I mean if I complicated my description.
The amount of graphs I had to create based on my data just crossed out
typical formula approach that would gather everything in other tables. It was
just to complex for me... However, thank you once again
"Jon Peltier" wrote:
Each chart's ranges are independent of other charts' ranges. Say the series
formula says:
=SERIES(Sheet1!$B$1,Sheet1!$A$2:$A$10,Sheet2!$B$2: $B$10,1)
If you change this to include down to row 12 in one chart, the other chart
has no knowledge of this.
If you were to define dynamic ranges, e.g., XData and YData, which counted
the number of rows to use (or used a value you entered into a cell), then
the series formula for both charts would say:
=SERIES(Sheet1!$B$1,Sheet1!XData,Sheet2!YData,1)
and both would always show the same data.
Here is a blog entry about dynamic ranges as chart series source data:
http://peltiertech.com/WordPress/200...ynamic-charts/
- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______
"Piotr (Peter)" wrote in message
...
So I have a chart that is based on data until october:
paid unpaid total paid vs unpaid
january 1 65 66 2%
february 3 5484 5487 0%
march 564 2621 3185 18%
april 561 56194 56755 1%
may 516 548 1064 48%
june 61 64 125 49%
july 61 894894 894955 0%
august 6 9494 9500 0%
september 161 48949 49110 0%
october 9616 949189 958805 1%
november 0 #DIV/0!
december 0 #DIV/0!
Then I create a copy of this chart in new worksheet. However, when I
update
range of the original chart until December (specifing new range) with
updated
figures, the copy doesn't update itself and I have to also manually update
the copy. Everything is fine with the copy when only figures change
(without
changing the range), but when a new range is specified then there is a
problem.
My newly created report has lots of graphs like that and I am doomed now.
Is there a wa to hard link these charts?? I add that I don't do VBA but
maybe if that would be only solution I could ask somebody from work to
this
if you have an idea.
Any help appreciated.