Dynamic Charting
 

I am trying to set up dynamic chart based on a pull from a
database...the results look something like so: volume across with
months on the top:
Jan | Feb | March
123 | 234 | 345

This info is being pulled monthly by an end user and I want to have a
chart that displays the volumes. I would like for the chart to change
automatically when a new month is added. I tried to use range names by
defining it in the following fashion:

=OFFSET('Volume'!$A$2, 0, 0, 1, COUNTA('Volume'!2:2))

This works just fine once, but as soon as I add another column, nothing
happens! and the range definition changes all the time (for example, i
get this: =OFFSET('Volume'!$A$4, 0, 0, 1,
COUNTA('Volume'!65527:65527)))

No idea why it does not automatically update the chart & no idea why
the range definition changes...Please help!


--
sergv
------------------------------------------------------------------------
sergv's Profile: http://www.excelforum.com/member.php...o&userid=26927
View this thread: http://www.excelforum.com/showthread...hreadid=401480

Use an absolute row address $2:$2.

If you use a name in the chart wizard, XL replaces the name with the
*current* range reference. You must use the name in the series formula
outside of the wizard. For more see
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

particularly, the first 2 links ('named formulas' and 'using these
named formulas in charts').

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

I am trying to set up dynamic chart based on a pull from a
database...the results look something like so: volume across with
months on the top:
Jan | Feb | March
123 | 234 | 345

This info is being pulled monthly by an end user and I want to have a
chart that displays the volumes. I would like for the chart to change
automatically when a new month is added. I tried to use range names by
defining it in the following fashion:

=OFFSET('Volume'!$A$2, 0, 0, 1, COUNTA('Volume'!2:2))

This works just fine once, but as soon as I add another column, nothing
happens! and the range definition changes all the time (for example, i
get this: =OFFSET('Volume'!$A$4, 0, 0, 1,
COUNTA('Volume'!65527:65527)))

No idea why it does not automatically update the chart & no idea why
the range definition changes...Please help!


--
sergv
------------------------------------------------------------------------
sergv's Profile: http://www.excelforum.com/member.php...o&userid=26927
View this thread: http://www.excelforum.com/showthread...hreadid=401480

Sergv
It does seem to work for me if I do the following

Assume data is in a range A1:C2 on Sheet1 in file Book1.xls
Jan | Feb | March
123 | 234 | 345
Define the following names
myGraph=OFFSET(Sheet1!$A$1, 1,0, 1, COUNTA(Sheet1!$2:$2))
myTitle=OFFSET(mygraph,-1,0)

Then set up your graph
Series1=SERIES(,'Book1.xls'!myTitle,'Book1.xls'!my Graph,1)

Post back if you still have problems

hth RES