Thread: 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

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