Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dynamic chart ranges | Excel Discussion (Misc queries) | |||
Dynamic chart ranges (slight variation) | Charts and Charting in Excel | |||
auto-expanding chart- dynamic ranges | Excel Worksheet Functions | |||
trying to use dynamic ranges and names for dumb bar chart | Excel Programming | |||
Dynamic chart: Changing Ranges | Charts and Charting in Excel |