I am trying to create a chart that automatically updates when I fill in the
corresponding data. To do this I googled a bit and found a solution that used
named ranges with an offset function. So I implemented this into my excel
sheet with the following code:
=OFFSET(Cost!$A$2;1;0;COUNT(Cost!$A$3:$A$2004);1)
This works perfectly as for one thing, I cant seem to get the last entry
joined with the others! What am I doing wrong?
I am using excel 2003.

I still dont know why the offset formula didnt work, but i did get around the
problem by simply adding +1 after COUNT(Cost!$A$3:$A$2004) so that the entire
formula now reads:
=OFFSET(Cost!$A$2;1;0;COUNT(Cost!$A$3:$A$2004)+1;1 )

It now works beautifully.

"Martin" wrote:

I am trying to create a chart that automatically updates when I fill in the
corresponding data. To do this I googled a bit and found a solution that used
named ranges with an offset function. So I implemented this into my excel
sheet with the following code:
=OFFSET(Cost!$A$2;1;0;COUNT(Cost!$A$3:$A$2004);1)
This works perfectly as for one thing, I cant seem to get the last entry
joined with the others! What am I doing wrong?
I am using excel 2003.

Hi,

You may also convert the source range of the graph to to a List (renamed as
Table in Excel 2007) by selecting the range (including headers) and pressing
Ctrl+L. Now the range would auto expand

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Martin" wrote in message
...
I am trying to create a chart that automatically updates when I fill in
the
corresponding data. To do this I googled a bit and found a solution that
used
named ranges with an offset function. So I implemented this into my excel
sheet with the following code:
=OFFSET(Cost!$A$2;1;0;COUNT(Cost!$A$3:$A$2004);1)
This works perfectly as for one thing, I cant seem to get the last entry
joined with the others! What am I doing wrong?
I am using excel 2003.

Hi Ashish,

What a smart way of solving the problem! Whish i had known this before i set
out on my full workday quest of the auto expanding charts!
Well we live and learn.

Thank you for your reply!

/Martin

"Ashish Mathur" wrote:

Hi,

You may also convert the source range of the graph to to a List (renamed as
Table in Excel 2007) by selecting the range (including headers) and pressing
Ctrl+L. Now the range would auto expand

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"Martin" wrote in message
...
I am trying to create a chart that automatically updates when I fill in
the
corresponding data. To do this I googled a bit and found a solution that
used
named ranges with an offset function. So I implemented this into my excel
sheet with the following code:
=OFFSET(Cost!$A$2;1;0;COUNT(Cost!$A$3:$A$2004);1)
This works perfectly as for one thing, I cant seem to get the last entry
joined with the others! What am I doing wrong?
I am using excel 2003.