how do I define a chart series with an indirect reference

[tvanellen]

I've made a XY scattergram that I want to copy for use to other workbooks
with other data series. The exact part of the data series to be displayed in
the chart varies between workbooks. In the data series for the chart, I'd
like to include an indirect reference that will define the first and the last
dat point to be displayed. How can I achieve this?

A related question: how can I copy charts beween workbooks in a way that the
reference to the original workbook is not being copied at the same time. I.e.
I want the displayed data to be taken from the workbook I'm copying the chart
to, and not from the workbook I copy the chart from?

Thank you for yr help.

 

[Tushar Mehta]

On the question of indirect references. Create named formulas.
Suppose you have data in Book11 sheet1 A1:A5 and sheet2 A1:A10.
Suppose you want to put the charts in another workbook (say Book10) and
you want to specify the various indirect references through cells in
Sheet1: say the workbook name is in A1, the sheet name in A2, the first
cell in A3, and the last cell in A4. Then, in Book10 (the book that
will contain the chart) create a name (Insert | Name > Define...)

aRng
=INDIRECT("'["&Sheet1!$A$1&"]"&Sheet1!$A$2&"'!"&Sheet1!$A$3&":"&Sheet1!
$A$4)
[Note carefully the use of both double-quotes and single-quotes.]

Put legitimate values in those cells, say, A1 contains Book11, A2
sheet2, A3 A1 and A4 A5. Create a chart using the named formula. For
how see
Names in Charts
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

Now, the chart will plot [book11]sheet2!A1:A5. Change any of the
values in book10 sheet1 cells A1:A4 and the chart will correctly
reflect the new values. Note that the values must come together to
form a legitimate range reference.

You can also specify the starting point and the number of cells to
plot. Suppose Book10 Sheet1 cell B4 contains the number of cells. In
Book10, create a new named formula
aRng2
=OFFSET(INDIRECT("'["&Sheet1!$A$1&"]"&Sheet1!$A$2&"'!"&Sheet1!$A
$3),0,0,Sheet1!$B$4,1)

and plot it.

The second issue you raise. The only simple way I know of how to do
that works with an embedded cell that only plots data in its parent
worksheet. Just copy the entire worksheet to the other workbook. Now
replace the data in this sheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[tvanellen]

thank you, this seems to be quite helpful. Nice website

On the question of indirect references. Create named formulas.
Suppose you have data in Book11 sheet1 A1:A5 and sheet2 A1:A10.
Suppose you want to put the charts in another workbook (say Book10) and
you want to specify the various indirect references through cells in
Sheet1: say the workbook name is in A1, the sheet name in A2, the first
cell in A3, and the last cell in A4. Then, in Book10 (the book that
will contain the chart) create a name (Insert | Name > Define...)

aRng
=INDIRECT("'["&Sheet1!$A$1&"]"&Sheet1!$A$2&"'!"&Sheet1!$A$3&":"&Sheet1!
$A$4)
[Note carefully the use of both double-quotes and single-quotes.]

Put legitimate values in those cells, say, A1 contains Book11, A2
sheet2, A3 A1 and A4 A5. Create a chart using the named formula. For
how see
Names in Charts
http://www.tushar-
mehta.com/excel/newsgroups/dynamic_charts/names_in_charts.html

Now, the chart will plot [book11]sheet2!A1:A5. Change any of the
values in book10 sheet1 cells A1:A4 and the chart will correctly
reflect the new values. Note that the values must come together to
form a legitimate range reference.

You can also specify the starting point and the number of cells to
plot. Suppose Book10 Sheet1 cell B4 contains the number of cells. In
Book10, create a new named formula
aRng2
=OFFSET(INDIRECT("'["&Sheet1!$A$1&"]"&Sheet1!$A$2&"'!"&Sheet1!$A
$3),0,0,Sheet1!$B$4,1)

and plot it.

The second issue you raise. The only simple way I know of how to do
that works with an embedded cell that only plots data in its parent
worksheet. Just copy the entire worksheet to the other workbook. Now
replace the data in this sheet.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

I've made a XY scattergram that I want to copy for use to other workbooks
with other data series. The exact part of the data series to be displayed in
the chart varies between workbooks. In the data series for the chart, I'd
like to include an indirect reference that will define the first and the last
dat point to be displayed. How can I achieve this?

A related question: how can I copy charts beween workbooks in a way that the
reference to the original workbook is not being copied at the same time. I.e.
I want the displayed data to be taken from the workbook I'm copying the chart
to, and not from the workbook I copy the chart from?

Thank you for yr help.

 

[Tushar Mehta]

You are welcome. Glad to be of help.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Multi-disciplinary business expertise
+ Technology skills
= Optimal solution to your business problem
Recipient Microsoft MVP award 2000-2005

thank you, this seems to be quite helpful. Nice website

{snip}

 

[tvanellen]

Mr Meshta, please allow me to return to this question for which you provided
an answer some months ago.

I'd like to use the named formula in sheets with quite long names. Is there
a way to adapt the formula in way that it will alwys work in the current
sheet (the sheet in which it is called), independent of the sheets name?