charting with 3-D referencing

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

I am trying to create a simple line chart for the same
cell on many sheets. So far, everything I have tried does
not work. Even when I use the mouse to define the cells
to be charted (the supposed 'fool-proof' way), I get the
message that the referece is invalid.

Also - when copying a formula that contains 3-D
referencing, the cpoied formula does not translate from
sheet to sheet. For example - if I have 3-D referencing
in several cells on sheet #1, then I try to copy those
cells to sheet #2, the formulas retain the 3-D references
to sheet #1 and they do not get translated to sheet #2.

Anybody have any ideas? (I am using Excel 2002).

Thanks,

Tom
 
A chart cannot use cells from multiple sheets as the source of a single
x- or y- specification.

I suspect that the other problem is that XL behaves as designed. After
copying the formulas, you will have to edit them by hand.

--
Regards,

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

You can use formulas to get all the data onto a single sheet for
plotting.. Suppose you want to track the values of cells A1 and B1 in
sheets 1 through 3. You could set up formulas in a range like this:

Leave cell A1 of a summary sheet blank, put A1 into cell B1 and B1 into
C1. List the sheets in A2 to A4:

A1 B1
Sheet1
Sheet2
Sheet3

select the range B2:C4, with cell B2 as the active cell. Enter this
formula into B2:

=INDIRECT($A2&"!"&B$1)

and hold Ctrl while pressing Enter. This populates the range with values
from the other sheets.

Note: if the sheet names have spaces, you need to enclose them in single
quotes, so the formula should be

=INDIRECT("'"&$A2&"'!"&B$1)

Also, a blank in the source sheet will become a zero in the summary sheet.

- Jon
 
Jon,

Thanks for the suggestion! I'll play around with this a
bit and see if it suits my needs.

Tom
 
Back
Top