Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.charting
|
|||
|
|||
extending source data in several charts
I have a big Excel doc with numerous charts, each with numerous lines.
About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! |
#2
Posted to microsoft.public.excel.charting
|
|||
|
|||
extending source data in several charts
if you don't like defining the ranges via the offset method, you might try
renaming all the series as names and creating the charts via a macro "Mike" wrote: I have a big Excel doc with numerous charts, each with numerous lines. About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! |
#3
Posted to microsoft.public.excel.charting
|
|||
|
|||
extending source data in several charts
I suggest using Dynamic Named Ranges. Debra Dalgleish discusses these he
http://www.contextures.com/xlNames01.html Dynamic Named Ranges use worksheet functions (particularly the Offset function) in the range's RefersTo property to enable automatic adustment of the range definitions - i.e. they update automatically. Once established, they should be maintenance free. The example below assumes the chart is XY Scatter. Also assumed is that the sheet is named "Sheet1" and that the x values are in column A starting in A1 and the y values are adjacent in column B. Also required for the example is that there be no other data in these columns. Example: 1. Open the Define Name dialog: Insert Names Define 2. Create the x values range: i. In the "Names in workbook:" window enter the name "XVals" ii. In the "Refers to:" window enter the following formula: =OFFSET(Sheet1!$A$1, 0, 0, COUNT(Sheet1!$A:$A), 1) iii. Click the Add button 3. Now create another range for the y values: i. In the "Names in workbook:" window enter the name "YVals" ii. In the "Refers to:" window enter: =Offset(XVals, 0, 1) iii. Click the Add button 4. Click the Close button 5. Now change the chart's source data to reference these names: i. Right-click the chart and select Source Data ii. Select the Series tab (if not already active) iii. Ensure that "Series1" is selected in the "Series" window iv. In the "X Values:" window enter: =Sheet1!XVals v. In the "Y Values:" window enter: =Sheet1!YVals The Copy and Paste functions are not available when either the Define Names and Source Data dialogs are active. To copy use: <Ctrl + C To paste use: <Ctrl + V Regards, Greg "Mike" wrote: I have a big Excel doc with numerous charts, each with numerous lines. About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! |
#4
Posted to microsoft.public.excel.charting
|
|||
|
|||
extending source data in several charts
Mike,
Your mention of the Offset function likely was in reference to previous posts describing Dynamic Named Ranges. However, I'm not suggesting that your father maintain these. I suggest that you set it up and hencefourth it should be maintenance free so long as no new charts are added. If your data ranges are all in some sensible order, after creating the first named range, the others should be easy. Examples of range definitions: Name : Definition in RefersTo window Chart 1: XVals1: =Offset(Sheet1!$A$1, 0, 0, Count(Sheet1!$A:$A), 1) YVals1: =Offset(XVals1, 0, 1) Chart 2: XVals2: =Offset(XVals1, 0, 2) YVals2: = Offset(XVals1, 0, 3) Chart 3: XVals3: =Offset(XVals1, 0, 4) YVals3: = Offset(XVals1, 0, 5) etc... The macro route is another possibility. But I would use this as the first option. For the macro route, I think I'd use the WorksheetChange event to loop through all the ranges and reset the source data for all charts. A bit slower than the first option. Regards, Greg "Mike" wrote: I have a big Excel doc with numerous charts, each with numerous lines. About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! |
#5
Posted to microsoft.public.excel.charting
|
|||
|
|||
extending source data in several charts
Oops,
This would require that all the charts have the same number of data points. This is not likely the case here. So disregard my previous post. Greg "Greg Wilson" wrote: Mike, Your mention of the Offset function likely was in reference to previous posts describing Dynamic Named Ranges. However, I'm not suggesting that your father maintain these. I suggest that you set it up and hencefourth it should be maintenance free so long as no new charts are added. If your data ranges are all in some sensible order, after creating the first named range, the others should be easy. Examples of range definitions: Name : Definition in RefersTo window Chart 1: XVals1: =Offset(Sheet1!$A$1, 0, 0, Count(Sheet1!$A:$A), 1) YVals1: =Offset(XVals1, 0, 1) Chart 2: XVals2: =Offset(XVals1, 0, 2) YVals2: = Offset(XVals1, 0, 3) Chart 3: XVals3: =Offset(XVals1, 0, 4) YVals3: = Offset(XVals1, 0, 5) etc... The macro route is another possibility. But I would use this as the first option. For the macro route, I think I'd use the WorksheetChange event to loop through all the ranges and reset the source data for all charts. A bit slower than the first option. Regards, Greg "Mike" wrote: I have a big Excel doc with numerous charts, each with numerous lines. About once per month, I need to extend the charts for about a month. This is a very painful process for each graph (multiplied by numerous graphs!). Does anyone have any simple method to do a mass change, standard or VBA? This would be extremely helpful. I also noticed some posts using OFFSET, but this would be too confusing for my father (for whom I am doing this) and too cumbersome. Something like a standard function or a VBA macro that I can program would make his life so much easier. Thanks much! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I want chart source data to be relative references, not absolute. | Charts and Charting in Excel | |||
Adding more source data to existing scatter plot | Charts and Charting in Excel | |||
Need Formula to display pivot table source data | Excel Worksheet Functions | |||
Extending a Chart Data Series from an Array - Can it be done? | Charts and Charting in Excel | |||
Named dynamic ranges, copied worksheets and graph source data | Charts and Charting in Excel |