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

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.

0
PiotrPeter (10)
9/2/2008 2:58:13 PM
excel.charting 18370 articles. 0 followers. Follow

10 Replies
411 Views

Similar Articles

[PageSpeed] 13

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
news:1CCA1004-F338-4210-95E2-69F21D8A4FE2@microsoft.com...
> 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.
> 


0
jonxlmvpNO (4558)
9/2/2008 4:26:12 PM
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/2008/05/14/dynamic-charts/
> 
> - Jon
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
> _______
> 
> 
> "Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
> news:1CCA1004-F338-4210-95E2-69F21D8A4FE2@microsoft.com...
> > 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.
> > 
> 
> 
> 
0
PiotrPeter (10)
9/2/2008 7:10:06 PM
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/2008/05/14/dynamic-charts/
> > 
> > - Jon
> > -------
> > Jon Peltier, Microsoft Excel MVP
> > Tutorials and Custom Solutions
> > Peltier Technical Services, Inc. - http://PeltierTech.com
> > _______
> > 
> > 
> > "Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
> > news:1CCA1004-F338-4210-95E2-69F21D8A4FE2@microsoft.com...
> > > 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.
> > > 
> > 
> > 
> > 
0
PiotrPeter (10)
9/2/2008 7:16:13 PM
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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
news:05FA2E8E-3D32-4401-AB4B-280B6F5ECEE6@microsoft.com...
>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/2008/05/14/dynamic-charts/
>> >
>> > - Jon
>> > -------
>> > Jon Peltier, Microsoft Excel MVP
>> > Tutorials and Custom Solutions
>> > Peltier Technical Services, Inc. - http://PeltierTech.com
>> > _______
>> >
>> >
>> > "Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message
>> > news:1CCA1004-F338-4210-95E2-69F21D8A4FE2@microsoft.com...
>> > > 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.
>> > >
>> >
>> >
>> > 


0
jonxlmvpNO (4558)
9/3/2008 4:59:07 PM
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.

"Jon Peltier" wrote:

> 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
> -------
> Jon Peltier, Microsoft Excel MVP
> Tutorials and Custom Solutions
> Peltier Technical Services, Inc. - http://PeltierTech.com
>
> 
> 
> 
0
PiotrPeter (10)
9/3/2008 6:50:01 PM
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.

0
PiotrPeter (10)
9/5/2008 2:57:02 PM
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
_______


"Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
news:2766FD54-A58F-4EBB-A367-85CC74257D79@microsoft.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.
> 


0
jonxlmvpNO (4558)
9/5/2008 4:07:27 PM
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

"Jon Peltier" wrote:

> 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
> _______
> 
> 
> "Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
> news:2766FD54-A58F-4EBB-A367-85CC74257D79@microsoft.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.
> > 
> 
> 
> 
0
PiotrPeter (10)
9/7/2008 8:29:02 PM
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
_______


"Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message 
news:D86DB6F9-579B-4FC4-BE7D-6864040DEB76@microsoft.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
>
> "Jon Peltier" wrote:
>
>> 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
>> _______
>>
>>
>> "Piotr (Peter)" <PiotrPeter@discussions.microsoft.com> wrote in message
>> news:2766FD54-A58F-4EBB-A367-85CC74257D79@microsoft.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.
>> >
>>
>>
>> 


0
jonxlmvpNO (4558)
9/7/2008 9:42:34 PM
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:))):

>> > > Piotr -
>> > > 
>> > > You have defined Period_Invoiced as
>> > > 
>> > > =OFFSET(table!$H$9,0,0,COUNTA(table!$H$9:$H$17),1)
>> > > 
>> > > What if you define it as
>> > > 
>> > > =OFFSET(table!$H$9,0,0,COUNTA(table!$H$9:$H$20),1)
>> > > 
>> > > This will enable the range to include the last three months.
>> > > 
>> > > - Jon


> > Hi Jon,
> > 
> > Thank you for this hint. I have amended the range to row 20 
> > and hey presto!! it works:) However, what I don't like or 
> > rather my boss are these 2 empty months visible on the chart 
> > (Nov and Dec) after I added Oct. Is there anyway to get away 
> > with it? If I could hide those months without the data that 
> > would be fantastic. Though, I need this chart to look as it 
> > looked before the amendment in a form you originally 
> > received. Let's say some slide bar or something ( I have seen 
> > some examples but I will lose my secondary axis I am affraid). 
> > 
> > What could you suggest? 
> > 
> > Kind regards,
> > 
> > Piotr



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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


0
PiotrPeter (10)
9/8/2008 5:55:01 PM
Reply:

Similar Artilces:

DVD Copy Pro for Mac
[b]DVD Copy Pro for Mac[/b] is a DVD copy software that can support D9 to D5 compression. With simple operation, it can save DVD in four different ways including directly burning to DVD disc, saving as DVD folder, copying into DVD media format that can be played by DVD Player directly, and converting as ISO file for easy movie management. Now this cool Mac DVD Copy also supports the latest released Snow Leopard. You can download [b][url=http://www.dvd-copy-mac.net/download/dvd-copy-pro-mac.zip]DVD Copy Pro for Mac[/url][/b] freely right now. [b]More information:[/b][quote]http://www.dvd-...

Import/copy calendar in Outlook 2003
I got a new computer and installed Office 2003, and wanted to move my calendar to the new PC. I created a new data file called Personal Folders from Old PC. I wanted to copy my calendar OVER my current one in Personal Folders, but don't see a way to do so. I can copy my old calendar into the Pesonal Folders, but can't delete the new, empty Calendar. So I have Calendar, and Calendar 1 under my Personal Folders. Any way to just import/copy my reminders/appointments into my new calendar? Thanks for any advice. Ck G'Day Cathy, 1. Use the Folder List 2. Click on the OLD Cal...

add comment to marker in chart
hello dear i hope you are fine. i have problem.i read a lot post but this does not help me. i added comments to a lot cell then creat a chart,ok? now it will show a line with marker or point,when i move mouse pointer , it must show comment but it is number and series or anything? got it? i hope you understand my problem. thanks a lot You can manually add custom labels to data markers in a chart. This is a tedious process, but there are a couple of good, free utilities that can do it for you: Rob Bovey's Chart Labeler, http://appspro.com John Walkenbach's Chart Tools, http:...

Copy info from one workbook to another?
I have a problem I have a workbook with the function i want like a discribe here. The problem is i want to split the workbook but i don`t know how. Is it possible to have a main workbook with information from other workbooks at each worksheet? I have 20 workbooks with information. I would like to have a main workbook with on sheet1 every row from the 20workbooks with category1, on sheet2 every row from the 20workbooks with category2, etc, etc. The information in the 20workbooks in always only on sheet. The Colum in which the data is has no fix end. I go to fill in the rows trough a...

Secondary Copy
Hi, My customer using Great Plains modified report(Primary Copy). Now he wants new report something similar to the modified GP report with very few changes. I tried to copy the modified report and planned to use us a secondary copy. But the report has temp tables, so I am unable to make secondary copy. Can some one help me in how to make a new report something similar to the modified report, where to launch it. Whether it is possible throught VBA & Modifier? Regards Selvakumar.S A report with a temporary table can only be a primary copy. This is because only a primary copy can b...

local copy of mail
How do you store a copy of all your mail on you local computer when using Exchange? You setup an OST/Offline Folders file. Check with your mail admin if they support that configuration. "Brandon Baker" <brandon@discussions.microsoft.com> wrote in message news:epNKuJ19FHA.4004@TK2MSFTNGP14.phx.gbl... > How do you store a copy of all your mail on you local computer when using > Exchange? > ...

Need to change the display of a chart
Hello! I have several pie charts that I have created to show percentages. In several of them, there are multiple legend values for the same percentage (0%). Excel prints all of them stacked on top of each other, with their leader lines and in some cases it runs into the title of the chart. Is there any way to change this? Also, in some other cases, with the same situation, there are no leader lines displayed (even though I told it to under Chart Options) and the values all print on top of each other. Suffice it to say, these charts are not very readable. Can anyone help me on thi...

Outlook 2000 SP3
How can I copy my mail and calendar to CD so that I can take my received and sent mail history to a new computer with Outlook installed? Thanks. >-----Original Message----- >How can I copy my mail and calendar to CD so that I can >take my received and sent mail history to a new computer >with Outlook installed? Thanks. >. > All your data saves in C:\Documents and Settings\Username\Local Settings\Application Data\Microsoft\Outlook\archive.pst and C:\Documents and Settings\Evgeni\Local Settings\Application Data\Microsoft\Outlook\outlook.pst You can either install instan...

Creating a chart from a table of values
I would like to create a chart from a table of values using indexing so i can vary the range of the table. Can anyone help? Take a look at the dynamic charts here http://www.peltiertech.com/Excel/Charts/Dynamics.html#DynoCht "hydro1guy" wrote: > I would like to create a chart from a table of values using indexing so i can > vary the range of the table. Can anyone help? ...

Can I change vendors for items en mass
I need to change the vendor for about 1000 items, all to the same new vendor. Is there a way to do this other than 1 by 1? Yes. You can use a query in SO Admin (or HQ Admin, if you use HQ). Use a SELECT query to find the IDs of the old and new Suppliers. Then UPDATE the SupplierList table with the new SupplierID. If you need more detailed instructions, let me know. Tom -- Stop fishing for e-mail "Jim K" wrote: > I need to change the vendor for about 1000 items, all to the same new vendor. > Is there a way to do this other than 1 by 1? ...

CDaoTableDef
I'm developing an application that uses the MicroSoft Access Jet DB engine. This is being done using C++ in VS.net. I need to change the user's db when they use the new version of the program. In particular I need to change the length of a text field in a pre-existing db without loosing the user's data. When I wanted to add a new field I used CDaoTableDef and it worked fine, but I can't locate a method to just update an existing field's properties. Any Ideas on how this can be done?? Thanks, Neil Neil if you need to alter the attributes of a tabledef object, you ...

Moving Outlook to a new computer #2
I am trying to move my Outlook 2003 to a new computer and can not see how to move all of my customization with it. For example, the catagories additions I made and all of the rules I set up for lables. Can someone point me to an article that gives you the details? Thanks ...

Copy picture ossice wizard
I have project 2007 standard edition, but the button is not working at all? Any suggestions Hi, The wizard is IMHO useless. Go to Roport, Copy Picture and do without the Wizard what millions have done before the Wizard existed. HTH -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "Enrique" <Enrique@discussions.microsoft.com> wrote in message news:72B5AA6C-67AE-468D-B6AE-FF16F52E57BD@microsoft.com... >I have project 2007 standard edition, but the bu...

Copy and paste versus copy and insert copied cells
What is the difference between these commands? Try them, it is pretty easy to see the difference -- Regards, Peo Sjoblom "Alana" <Alana@discussions.microsoft.com> wrote in message news:879FED29-5541-41B0-BB2C-7108D42ED593@microsoft.com... > What is the difference between these commands? ...

How do I change the text in a workbook from upper to lower case
Hi Please can anyone help with this one! I have a large workbook that I have created (just with text - no formulas) and I have done it in caps but now want to change all the text to lower case without having to re-type it all. Excel hslp is confusing so can anyone tell me how to do it please Thanks Mike Hi see: http://www.cpearson.com/excel/case.htm -- Regards Frank Kabel Frankfurt, Germany mike wrote: > Hi > > Please can anyone help with this one! > > I have a large workbook that I have created (just with text - no > formulas) and I have done it in caps but now...

Create New Report
Hi there, To set the scene, i'm using web client of 4.0, and i'm not a programmer. Currently, out-of-the-box functionality allows you to run a report called "Quote" which presents the data in a good format, almost good enough to hand to a customer. What I need is this report, but with our logo's embedded, and a few fields added/moved/removed, but i can't figure out how to edit layout options or content options, and I can't even seem to create a similar report from scratch (as they all appear to relate to exisitng XML reports out of the box). I'll be...

subtotal copy and paste
Hi I have used the subtotal option in Excel and this is fine. I have also collasped the list so only the subtotal for each category is visible. However I want to copy and paste this list (just the subtotal) into another sheet When I do this it expands and shows all the results. Is it possible to just copy the visible bit. I have looked in paste special but no luck Cheers Hager On Jan 21, 6:38=A0am, Hager <Ha...@discussions.microsoft.com> wrote: > Hi > > I have used the subtotal option in Excel and this is fine. I have also > collasped the list so only the...

3D Column Chart #2
How do I create a 3D column chart and have three axis? ...

Copy above fails one way, copy below fails another way
I have a table of blood pressure readings that looks something like this: A B C D E 11 12 Averages: 134 83 74 13 R# Date Sys Dia Pul 14 4 8/17/08 138 87 72 15 3 8/15/08 130 84 76 16 2 8/12/08 137 83 70 17 1 8/11/08 129 79 76 Cell A14 contains =A15+1 Cell C12 contains =AVERAGE(C14:C17) Cell D12 contains =AVERAGE(D14:D17) Cell E12 contains =AVERAGE(E14:E17) Every time I take a new reading, I want to add a row to the top of the table, between row 13 (the header) and row 14 (the previous top row). I want to push a...

Business Portal "Copy to Excel" not working in Query pages
Hi, I am using Business Portal 4.0. When browsing through one of the queries pages in Business Portal, i clicked "Copy to Excel" icon. I got an error saying "The Office Web Components (OWC) must be installed to copy results to Excel." at the bottom. I am using Office 2007 on my machine. I came to know that Microsoft Office Web Components will no longer be shipping in Microsoft Office. I tried installing Office 2003 Add-in: Office Web Components from http://www.microsoft.com/downloads/details.aspx?familyid=7287252C-402E-4F72-97A5-E0FD290D4B76&displaylang=en a...

line column Pivot Charts
Access 2003 does not support combined Line/Column Charts, but Excel does. Has the capability been added to Access 2007? Access does support a series displayed as a bars and another series as line. -- Duane Hookom MS Access MVP Help me support UCP http://www.access.hookom.net/UCP/Default.htm "John" <John@discussions.microsoft.com> wrote in message news:A0DD25F6-D99A-44C3-812B-416776BED8AF@microsoft.com... > Access 2003 does not support combined Line/Column Charts, but Excel does. > Has > the capability been added to Access 2007? ...

Multivari Chart
I am looking to learn how to create a "Multivari" chart in Excel. Can anyone help me with the steps to create one? I am having troube making the vertical stack of individual data points across groupings. Thanks, Tony Not sure what a multivari chart is, but as a guess try http://www.bmsltd.ie/Excel/SBXLPage.asp#Charting and see FunChrt4.zip -- Regards, Tushar Mehta www.tushar-mehta.com Microsoft MVP -- Excel In article <106uvtjim536nb4@corp.supernews.com>, orzechow@execpc.com says... > I am looking to learn how to create a "Multivari" chart in Excel. C...

Copying graphs
Hi Is it possible to range value a graph ie I would like to copy the graph to another workbook, show all the information but without the links? Thanks You could copy the chart as a picture, then paste it wherever you want. Select the chart, hold Shift while clicking on the Edit menu, Choose Copy Picture, and select the On Screen and As Picture options, then go to the other workbook/sheet and Paste. - Jon ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions Peltier Technical Services, Inc. - http://PeltierTech.com _______ "Dixie" <Dixie@discussions.micr...

Changing font of CListCtrl's header
Hi All, I have a CListCtrl instance. I can get the pointter to the CHeaderCtrl of the CListCtrl using GetHeaderCtrl(). My requirement is to change the font of the header items.How do I do that? Any clues/suggestions are heartfully appreciated. Regards, Vinodh Kumar P I think simple subclassing the headercontrol and changing its font should do. See if the following helps you: http://www.codeguru.com/listview/HeaderCtrlEx.shtml -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "Vinodh Kumar" <thecomdeveloper@yahoo.com> wrote in message news:bdr48r$6im$1@news.m...

How do I change cell that contains a formula to an actual number
I would like to use the results of a formula in a cell as an actual number. Any help would be appreciated? Hi copy the cell and use 'Edit - Paste Special - Values' -- Regards Frank Kabel Frankfurt, Germany "Carlton A. Barlow" <CarltonABarlow@discussions.microsoft.com> schrieb im Newsbeitrag news:D8B1F514-E50A-4CB8-98B5-50D6FAA4B739@microsoft.com... > I would like to use the results of a formula in a cell as an actual number. > Any help would be appreciated? An easy way to do this is when editing the formula just type cntl+"=". It immediately chan...