Dynamic Charting

[sergv]

I am trying to set up dynamic chart based on a pull from
database...the results look something like so: volume across wit
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
chart that displays the volumes. I would like for the chart to chang
automatically when a new month is added. I tried to use range names b
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, nothin
happens! and the range definition changes all the time (for example,
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 wh
the range definition changes...Please help

 

[Tushar Mehta]

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/newsgroups/dynamic_charts/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

 

[Robert_Steel]

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'!myGraph,1)

Post back if you still have problems

hth RES