If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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! |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
PST file has reached maximum size | Jeff C | General Discussion | 2 | October 6th, 2005 01:35 PM |
Format on data to import to Access tables? (I need your advice) | Niklas Östergren | General Discussion | 5 | December 13th, 2004 02:54 PM |
Data Source issues | Philippe | Database Design | 1 | October 10th, 2004 09:45 PM |
Excel Chart Source Data | dilworth | Charts and Charting | 0 | June 18th, 2004 03:03 PM |
Change the source data in multiple charts | Bernard V Liengme | Charts and Charting | 2 | October 23rd, 2003 05:35 PM |