A copy of a chart doesn't change when the original has new range

[Piotr (Peter)]

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.

 

[Jon Peltier]

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/2008/05/14/dynamic-charts/

- Jon

 

[Piotr (Peter)]

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

 

[Piotr (Peter)]

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).

 

[Jon Peltier]

The question arises, why do you need two copies of the same chart?

There is a possible solution, which involves the Camera tool. This allows
you to take a picture of a range, and place the picture somewhere else. and
it will always be an up-to-date picture of the range, no matter how you
change it. Simply arrange the chart over a range, and use the camera to take
a picture of the range below the chart.

The camera tool has to be added to a toolbar somewhere. Right click on the
toolbar area and choose Customize. On the Commands tab, choose Tools in the
left list, then scroll 2/3 to the end of the right list, click on the Camera
tool, and drag it to a convenient place on a toolbar.

- Jon

 

[Piotr (Peter)]

Hi Jon and thank you for your response,
When I was creating all my pivot tables and charts based on the cell
reference table I had to have all the charts created before I started
creating a report as it was made from the scratch and even my boss didn't
exactley know what she wanted on the final report. Therefore, I needed all
sort of charts before placing their copies on to the report and manipulating
their order, place etc. My initial thought was, that once I had new sort of
data I would put new range in the originals and then copies would be updated
automatically as the originals are in the some workseets as tables so it is
easier to navigate. Now I think due to linking like problem I will be just
updating copies and abandon the originals. I wanted to automate the process
of doing this report and similar tables could be achieved by using in most
instances suproduct function with several conditions but even with pivot
tables that report was time consuming so didn't really consider formulas as
this would take much more time. Yet I think to give it a go and see how it
goes. I need this report to show invoicing in the company for different
businesses where we distict service A per country a b c... in months and
sometimes it just a service as total in the following months. So It looks
like a chart "service A-country a" and month Jan Feb...current month. Then I
show total for Service A in Jan.....current month. One report is for Head of
Depts and one collating everything for Heads of Division. I have over 100
charts so creating speparate tables was tiresome when using a formula that
worked but I had to amend little details which I didn't have to in pivots.
Additionaly I have to include tables that show, on top of graphs, paid,
unpaid invoices and their numbers, receivable days etc. but this easy. So
writing some VBA where my report is not static but changes (page breaks,
bigger, smaller invoicing tables (the easy ones) so I can't just aks somebody
to write a code that will replace/add data and correctly place everything
again and again. Possibly I wrong with this VBA nature as I just don't do VBA
at all.

Regards,
Piotr
I know this is a long story but hopefully this will give you better overview.

 

[Piotr (Peter)]

Hi Jon,
Ok so I have read about 6 different tutorials including yours about dynamic
ranges, however as an intermediate user I am still confused.
I have a table G39:K50:

Invoices [GBP] Payments [GBP]
Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs.
Invoiced
1 2008 26,643.26 26,643.26 0.00 100%
2 2008 17,596.49 17,596.49 0.00 100%
3 2008 45,565.64 31,987.15 13,578.49 70%
4 2008 34,258.51 22,766.73 11,491.78 66%
5 2008 30,873.22 18,138.02 12,735.20 59%
6 2008 39,550.61 22,695.54 16,855.06 57%
7 2008 37,848.49 11,041.48 26,807.01 29%
(blank) 0.00 0.00 0.00 #DIV/0!
(blank) 0.00 0.00
0.00 #DIV/0!


This table ends in a row G50 where December will appear. All cells contain
formulas that either displays month and a year when found in a source or sum
up specified values in a range, so there are "blanks" and "zeros' as the
formulas have been copied down in order to show dispalay new month and sum up
what I need.

Having done little exercise when creating a Dynamic Range according
www.contextures.on.ca/xlNames01.html I could see the expanded range when I
typed new name. Fantastic!. Nonetheless, what I would like to achieve is
whenever new values appear automatically (as formulas will find them in a
table), then the range is expanded.

One of my ranges looks as follows:
Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1) G is
obvoiusly changed for H, I, J, K. I was just to confused with this formula
when using Named Ranges so I left it as it is.
My concern though, is my source data for a chart: Payment
Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45,
Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary
X)=table!$G$39:$G$45.

When new data appears in a table found by my formulas, the ranges do not
expand. I am doing something wrong here. Possibly I should have change the
ranges all the way down to a row 50 but when I did that, also no avail.I
can't add every single time data by hand as I said I have over 100 charts and
I would like to create a tool that will save my time. Aslo all my charts will
be "chart sheets".

I spent all my day trying to sort this out and I just can't get this right.
I fyou could assist me again I would be obliged.
Sorry for the long post but I wanted to explain this correctly.

 

[Jon Peltier]

The formulas under the linked data are messing you up, I fear. Could you put
the totals above the range, or below the entire range, not three cells below
the last month?

If you know how many months of data you have, use that number in place of
COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define
it once, then define the others like so:

Name: FirstRange
Refers To:
=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1)

Name: SecondRange
RefersTo:
=OFFSET(FirstRange,0,1)

Name: ThirdRange
RefersTo:
=OFFSET(FirstRange,0,2)

etc.

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

Hi Jon,
Ok so I have read about 6 different tutorials including yours about
dynamic
ranges, however as an intermediate user I am still confused.
I have a table G39:K50:

Invoices [GBP] Payments [GBP]
Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs.
Invoiced
1 2008 26,643.26 26,643.26 0.00 100%
2 2008 17,596.49 17,596.49 0.00 100%
3 2008 45,565.64 31,987.15 13,578.49 70%
4 2008 34,258.51 22,766.73 11,491.78 66%
5 2008 30,873.22 18,138.02 12,735.20 59%
6 2008 39,550.61 22,695.54 16,855.06 57%
7 2008 37,848.49 11,041.48 26,807.01 29%
(blank) 0.00 0.00 0.00 #DIV/0!
(blank) 0.00 0.00
0.00 #DIV/0!


This table ends in a row G50 where December will appear. All cells contain
formulas that either displays month and a year when found in a source or
sum
up specified values in a range, so there are "blanks" and "zeros' as the
formulas have been copied down in order to show dispalay new month and sum
up
what I need.

Having done little exercise when creating a Dynamic Range according
www.contextures.on.ca/xlNames01.html I could see the expanded range when I
typed new name. Fantastic!. Nonetheless, what I would like to achieve is
whenever new values appear automatically (as formulas will find them in a
table), then the range is expanded.

One of my ranges looks as follows:
Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1) G is
obvoiusly changed for H, I, J, K. I was just to confused with this formula
when using Named Ranges so I left it as it is.
My concern though, is my source data for a chart: Payment
Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45,
Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary
X)=table!$G$39:$G$45.

When new data appears in a table found by my formulas, the ranges do not
expand. I am doing something wrong here. Possibly I should have change the
ranges all the way down to a row 50 but when I did that, also no avail.I
can't add every single time data by hand as I said I have over 100 charts
and
I would like to create a tool that will save my time. Aslo all my charts
will
be "chart sheets".

I spent all my day trying to sort this out and I just can't get this
right.
I fyou could assist me again I would be obliged.
Sorry for the long post but I wanted to explain this correctly.

 

[Piotr (Peter)]

Hi Jon,
Well, I moved away Gross Amount since I don't have any TOTALS below my
ranges. Please have a look here for better overview:
http://img398.imageshack.us/my.php?image=tableandchartandrangesqk9.jpg
(obviously SUMPRODUCT formula has just different conditionals for each column)

As you can see in the picture my ranges look as you told me to do so aside
from COUNTA (I have put there a number of rows rather than COUNTA) but no
avail anyway when formulas "enter " values. I suspect the culprit to be
formulas since thay show 0.00 when no data is available and the date is
"blank" because the formula is defined to do so. Maybe there is a way to
"REFRESH THE RANGES" like in a web browser by pressing F5?? and then it
will trigger them

I don't what to do now. Every single time I had"created" new values in a
source worksheet and formulas showed them, ranges didn't expand.

Any furhter suggestions Jon, as I still have a hope?

Thanks,
Piotr

The formulas under the linked data are messing you up, I fear. Could you put
the totals above the range, or below the entire range, not three cells below
the last month?

If you know how many months of data you have, use that number in place of
COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just define
it once, then define the others like so:

Name: FirstRange
Refers To:
=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1)

Name: SecondRange
RefersTo:
=OFFSET(FirstRange,0,1)

Name: ThirdRange
RefersTo:
=OFFSET(FirstRange,0,2)

etc.

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

Hi Jon,
Ok so I have read about 6 different tutorials including yours about
dynamic
ranges, however as an intermediate user I am still confused.
I have a table G39:K50:

Invoices [GBP] Payments [GBP]
Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs.
Invoiced
1 2008 26,643.26 26,643.26 0.00 100%
2 2008 17,596.49 17,596.49 0.00 100%
3 2008 45,565.64 31,987.15 13,578.49 70%
4 2008 34,258.51 22,766.73 11,491.78 66%
5 2008 30,873.22 18,138.02 12,735.20 59%
6 2008 39,550.61 22,695.54 16,855.06 57%
7 2008 37,848.49 11,041.48 26,807.01 29%
(blank) 0.00 0.00 0.00 #DIV/0!
(blank) 0.00 0.00
0.00 #DIV/0!


This table ends in a row G50 where December will appear. All cells contain
formulas that either displays month and a year when found in a source or
sum
up specified values in a range, so there are "blanks" and "zeros' as the
formulas have been copied down in order to show dispalay new month and sum
up
what I need.

Having done little exercise when creating a Dynamic Range according
www.contextures.on.ca/xlNames01.html I could see the expanded range when I
typed new name. Fantastic!. Nonetheless, what I would like to achieve is
whenever new values appear automatically (as formulas will find them in a
table), then the range is expanded.

One of my ranges looks as follows:
Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1) G is
obvoiusly changed for H, I, J, K. I was just to confused with this formula
when using Named Ranges so I left it as it is.
My concern though, is my source data for a chart: Payment
Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45,
Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary
X)=table!$G$39:$G$45.

When new data appears in a table found by my formulas, the ranges do not
expand. I am doing something wrong here. Possibly I should have change the
ranges all the way down to a row 50 but when I did that, also no avail.I
can't add every single time data by hand as I said I have over 100 charts
and
I would like to create a tool that will save my time. Aslo all my charts
will
be "chart sheets".

I spent all my day trying to sort this out and I just can't get this
right.
I fyou could assist me again I would be obliged.
Sorry for the long post but I wanted to explain this correctly.

 

[Jon Peltier]

This has been going on long enough. Email me your workbook and I'll set up
the dynamic ranges.

jon at peltier tech dot com

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

Hi Jon,
Well, I moved away Gross Amount since I don't have any TOTALS below my
ranges. Please have a look here for better overview:
http://img398.imageshack.us/my.php?image=tableandchartandrangesqk9.jpg
(obviously SUMPRODUCT formula has just different conditionals for each
column)

As you can see in the picture my ranges look as you told me to do so aside
from COUNTA (I have put there a number of rows rather than COUNTA) but no
avail anyway when formulas "enter " values. I suspect the culprit to be
formulas since thay show 0.00 when no data is available and the date is
"blank" because the formula is defined to do so. Maybe there is a way to
"REFRESH THE RANGES" like in a web browser by pressing F5?? and then it
will trigger them

I don't what to do now. Every single time I had"created" new values in a
source worksheet and formulas showed them, ranges didn't expand.

Any furhter suggestions Jon, as I still have a hope?

Thanks,
Piotr

The formulas under the linked data are messing you up, I fear. Could you
put
the totals above the range, or below the entire range, not three cells
below
the last month?

If you know how many months of data you have, use that number in place of
COUNTA(table!$G$39:$G$45) in your dynamic range formula. Also, just
define
it once, then define the others like so:

Name: FirstRange
Refers To:
=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1)

Name: SecondRange
RefersTo:
=OFFSET(FirstRange,0,1)

Name: ThirdRange
RefersTo:
=OFFSET(FirstRange,0,2)

etc.

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

Hi Jon,
Ok so I have read about 6 different tutorials including yours about
dynamic
ranges, however as an intermediate user I am still confused.
I have a table G39:K50:

Invoices [GBP] Payments [GBP]
Per Invoiced Gross Amount Payment received Pay yet to receive Rec. vs.
Invoiced
1 2008 26,643.26 26,643.26 0.00 100%
2 2008 17,596.49 17,596.49 0.00 100%
3 2008 45,565.64 31,987.15 13,578.49 70%
4 2008 34,258.51 22,766.73 11,491.78 66%
5 2008 30,873.22 18,138.02 12,735.20 59%
6 2008 39,550.61 22,695.54 16,855.06 57%
7 2008 37,848.49 11,041.48 26,807.01 29%
(blank) 0.00 0.00 0.00 #DIV/0!
(blank) 0.00 0.00
0.00 #DIV/0!


This table ends in a row G50 where December will appear. All cells
contain
formulas that either displays month and a year when found in a source
or
sum
up specified values in a range, so there are "blanks" and "zeros' as
the
formulas have been copied down in order to show dispalay new month and
sum
up
what I need.

Having done little exercise when creating a Dynamic Range according
www.contextures.on.ca/xlNames01.html I could see the expanded range
when I
typed new name. Fantastic!. Nonetheless, what I would like to achieve
is
whenever new values appear automatically (as formulas will find them in
a
table), then the range is expanded.

One of my ranges looks as follows:
Period_Invoiced=OFFSET(table!$G$39,0,0,COUNTA(table!$G$39:$G$45),1) G
is
obvoiusly changed for H, I, J, K. I was just to confused with this
formula
when using Named Ranges so I left it as it is.
My concern though, is my source data for a chart: Payment
Received=table!$I$39:$I$45, Payment_yet_to_receive=table!$J$39:$J$45,
Received vs. Invoices=table!$K$39:$K$45 and Category X (also secondary
X)=table!$G$39:$G$45.

When new data appears in a table found by my formulas, the ranges do
not
expand. I am doing something wrong here. Possibly I should have change
the
ranges all the way down to a row 50 but when I did that, also no
avail.I
can't add every single time data by hand as I said I have over 100
charts
and
I would like to create a tool that will save my time. Aslo all my
charts
will
be "chart sheets".

I spent all my day trying to sort this out and I just can't get this
right.
I fyou could assist me again I would be obliged.
Sorry for the long post but I wanted to explain this correctly.

 

[Piotr (Peter)]

Below there is a correspondence between Jon and myself as I couldn't show
exactely what I need. Jon helped anyway and here are his answers so others
can possibly benefit it future (I hope you don't mind Jon)):



Yes, my first suggestion counts any cell with any content, including a
formula that returns "". This should do it finally:

=OFFSET(table!$H$9,0,0,SUM(IF(LEN(table!H9:H20)>0,1,0)),1)

- Jon