Home |
Search |
Today's Posts |
#1
|
|||
|
|||
linking/updating two files for charting
I have two files, one with data that is for, say, A, B,
C, D periods. I have another file which has a chart which refers to file 1, and only takes data points A-C. I regularly have to go into the chart file and update the place to which it links, and it shifts by one place each time I have to update. In other words, first quarter, I need it to take A-C, but next quarter I need the same graph to take points B-D. How dow I set up a situation where I have either a pointer which the chart refers to (which tells it which of the cells from file 1 to take), or have some '1' indicator in the first file, telling the chart where to look, or a named range that the chart refers to which I can update to include the right cells in the range? The problem I am running into is that no matter how I get the source data for the chart to start (named range, indirect reference to the text name of the cells ("Book1!Sheet1" etc)) the chart converts that into an actual cell reference, so that no matter what I change in the first file later (names of ranges, etc) the source data is already defined as a specific cell set. Thanks for any help. Boris |
#2
|
|||
|
|||
Hi Boris -
You can set up dynamic named ranges in the sheet, which can be based on a value in a cell. The chart in turn can reference these names for its source data. For example, the following refers to A1:C1 if E1=0, or B1:D1 if E1=1. =offset(Sheet1!A1,0,Sheet1!E1,1,3) For examples and links on dynamic charts: http://peltiertech.com/Excel/Charts/Dynamics.html More hints, for charting data from different sheets (and it works for different workbooks, too): http://peltiertech.com/Excel/ChartsH...iffSheets.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ boris wrote: I have two files, one with data that is for, say, A, B, C, D periods. I have another file which has a chart which refers to file 1, and only takes data points A-C. I regularly have to go into the chart file and update the place to which it links, and it shifts by one place each time I have to update. In other words, first quarter, I need it to take A-C, but next quarter I need the same graph to take points B-D. How dow I set up a situation where I have either a pointer which the chart refers to (which tells it which of the cells from file 1 to take), or have some '1' indicator in the first file, telling the chart where to look, or a named range that the chart refers to which I can update to include the right cells in the range? The problem I am running into is that no matter how I get the source data for the chart to start (named range, indirect reference to the text name of the cells ("Book1!Sheet1" etc)) the chart converts that into an actual cell reference, so that no matter what I change in the first file later (names of ranges, etc) the source data is already defined as a specific cell set. Thanks for any help. Boris |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Cannot access read-only documents. | Excel Discussion (Misc queries) | |||
Automatic Data Import | Excel Discussion (Misc queries) | |||
multiple text files URGENT | Excel Discussion (Misc queries) | |||
importing multiple text files URGENT!!! HELP | Excel Worksheet Functions | |||
importing multiple text files??? | Excel Discussion (Misc queries) |