Sheet level references in charts

[amcb]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have the same problem as the original post linked below.

<http://www.officeformac.com/ProductForums/Excel/1609/0>

In case you can't open that, my problem is I have data on a multiple sheets and want to xy scatter plot it. If I create a plot in sheet1 and copy it to sheet2, the source data of the new plot is linked to sheet1. I want the source data of the copied plot to be linked to the same rows and columns on sheet2, not sheet1. Mr. Greenblatt appears to have answered my question in terms of what I need to do. I just don't know how to do it.

How do I use sheet level names or references in plots?

Thanks.

 

[Laroche J]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I have the same problem as the original post linked below.

<http://www.officeformac.com/ProductForums/Excel/1609/0>

In case you can't open that, my problem is I have data on a multiple sheets
and want to xy scatter plot it. If I create a plot in sheet1 and copy it to
sheet2, the source data of the new plot is linked to sheet1. I want the source
data of the copied plot to be linked to the same rows and columns on sheet2,
not sheet1. Mr. Greenblatt appears to have answered my question in terms of
what I need to do. I just don't know how to do it.

How do I use sheet level names or references in plots?

Thanks.

The first time you use a certain name for a range in a workbook, it is
defined as a global name. If you then select any other sheet than the one
where the name is defined and define a range with the same name, that new
named range will have a sheet level name.

So what you have to do is go to the first sheet, name a range, then go to
the second (and third,...) sheet and define the same name for the same
address range. Note that for defining the same name after the first time you
CANNOT use the address box of the formula bar, you have to go to Insert,
Name, Define.

Next, for the charts on the first sheet, select every data set one per one
and in the formula bar replace the A1-type addressing by the name of the
range. For a whole chart you can also use Chart, Source Data.

When all is done, copy your charts to the other sheets.

The next time, make your life easier and create your model sheet WITH the
charts before copying it. The data sources will automatically refer to the
sheet the chart is on, and no name will be required. A variant of it, if
it's too late, is now to create copies of a sheet with its charts, then copy
and paste data on the copies from the similar sheets that you will discard
afterwards. An example in other words, copy Sheet1 to Sheet1 (2) and Sheet1
(3), copy data from Sheet2 to Sheet1 (2) and from Sheet3 to Sheet1 (3),
delete Sheet2 and Sheet3, and rename the new sheets with the previous names,
if this is all possible. It might be easier than defining the named ranges
on all those sheets. You might want to work on a copy of the original file
in case something goes wrong.

JL
Mac OS X 10.4.11
Office v.X 10.1.9, Office 2008 trial