dynamic range for excel chart

B

bobf

Hi all,
Does anyone know how I can set an EXCEL chart data range
to automatically adjust to the number of rows in my data,
which both increases and decreases over time. I can use
the COUNT function to determine the current number of
rows, but I don't know if I am allowed formulas in a
chart's data range.

thanks in advance.
 
D

David Jessop

Hi,

One way to do this is to use the OFFSET function. For example (from a SS
I'm using)

=OFFSET(Europe!$J$1,0,0,COUNT(Europe!$J:$J),1)

This gives you a range that changes size depending on the number of entries
in the J column. You'll have to play a little with the exact formula.
Annoyingly this doesn't work in SERIES, so we just use this function to
create a named range (Insert, Name, Define) for the X and Y values and then
in the macro call:

Sheets("Chart").Select
ActiveChart.SetSourceData _
Source:=Sheets("Europe").Range("DATE,Earnings_Yield"), _
PlotBy:=xlColumns

You could either have the macro run on opening the SS, or just add a button
which shows the chart (i.e. just switches to that tab), but runs the update
first.

HTH,

David Jessop
 
Top