Dynamic Chart ranges
 

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

Dynamic Chart ranges
 

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$3 7))


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






"LiAD" wrote:

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

Dynamic Chart ranges
 

and set your series to
=workbookname.xls!chartdata

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Patrick Molloy" wrote in message
...
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$3 7))


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






"LiAD" wrote:

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

Dynamic Chart ranges
 

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$3 7))

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

"Patrick Molloy" wrote:

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$3 7))


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






"LiAD" wrote:

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

Dynamic Chart ranges
 

Go back and read carefully what Patrick said.
insertnamedefinename itput in formula (excel will add the sheet name for
you)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
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$3 7))

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

"Patrick Molloy" wrote:

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$3 7))


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






"LiAD" wrote:

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

Dynamic Chart ranges
 

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

"Don Guillett" wrote:

Go back and read carefully what Patrick said.
insertnamedefinename itput in formula (excel will add the sheet name for
you)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
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$3 7))

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

"Patrick Molloy" wrote:

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$3 7))


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






"LiAD" wrote:

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

Dynamic Chart ranges
 

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

Think i can get it from here.

Thanks for your help

"Don Guillett" wrote:

Go back and read carefully what Patrick said.
insertnamedefinename itput in formula (excel will add the sheet name for
you)

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"LiAD" wrote in message
...
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$3 7))

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

"Patrick Molloy" wrote:

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$3 7))


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






"LiAD" wrote:

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