Dynamic Chart ranges

[LiAD]

Hi,

I have a macro which is taking data from one region on a sheet, (EB7-FK31),
then copy pasting the values to another region (AA7) and then deleting the
empty rows.

From the reduced table I would like to plot a chart of the data, without a
lot of empty rows if possible. So there can be 1 to 23 rows to plot.

Which is the easiest way to achieve this?

- Have a chart on the page which gets updated to the new range every time
data is added, or,
- A macro that deletes the current chart and then replots a new one to the
nex range

For info when setting the chart properties I would like to have presets for
title fonts, line colours etc.

Thanks for your help
LiAD

 

[Patrick Molloy]

make the table at AA7 a dynamic range and set the chart's data to the range

Insert / Name / define

Name: ChartData

=OFFSET(Sheet1!$AA$7,0,0,COUNTA(Sheet1!$AA$7:$AA$37))


So it AA7 and the next three rows are A,B,C,D


then =ROWS(ChartData) has teh value 4

if I add E,F G to the next three rows ( AA13:= G) the formual shows 7


check this out on www.cpearson.com

 

[Don Guillett]

and set your series to
=workbookname.xls!chartdata

 

[LiAD]

If I have understood what u've told me to do its

Select my data to be plotted and call it a name - ChartData
In the lower part of the box for setting the name enter the formula
=OFFSET(Sheet1!$AA$7,0,0,COUNTA(Sheet1!$AA$7:$AA$37))

On the chart wizard set the data range to =(ChartData). Or is this Dons
comment below am I supposed to put workbookname.xls!ChartData (in this case
Planning.xls!ChartData)?

Then when the table changes size the chart will update.

Is this what you mean?

Sorry I've never used (or at least not knowingly) dynamic ranges.

Thanks

 

[Don Guillett]

Go back and read carefully what Patrick said.
insert>name>define>name it>put in formula (excel will add the sheet name for
you)>>

 

[LiAD]

The first bit I got.

Its how to get a chart going after that thats not flying for me at the
moment. It wont accept the named range as the data range

 

[LiAD]

Found how it works with a simpler example than my table.

Think i can get it from here.

Thanks for your help