Sheet range reference

[Guest]

I have a workbook with a number of sheets, let's say named sheet01, sheet02
......sheet10. These sheets are of the same structure and are representing
some development in time. So now I would like to make a chart (or separate
table) the where the X coordinate is the sheet number and the Y some cell on
each of the sheets.

Is this possible with a simple reference like the ranges within the same
chart or do I really have to create a macro or do it by hand?

Jens.

 

[JE McGimpsey]

Each series must reside on a single sheet. You can create a summary
sheet which has something like:

A1: =Sheet1!A1
A2: =Sheet2!A1
A3: =Sheet3!A1

etc.

and plot that series.

 

[David McRitchie]

Hi Jens,

If you want to display the value of a cell on another sheet
='sheet1'!B2
You can use these for a graph

If you want a link to the data and show the data
=HYPERLINK("'sheet1'!B2", 'sheet1'!B2)
more examples in excel/sheets.htm#hyperlinks
Examples that survive insertion/deletion of rows/columns:
=HYPERLINK("#"&CELL("address",C5),C5)
=HYPERLINK("#"&CELL("address",sheetone!C5),sheetone!C5)
=HYPERLINK("#"&CELL("address",'sheet two'!C5),'sheet two'!C5)

If you want a table of your pages see
Build Table of Contents, similar listings, working with Hyperlinks
http://www.mvps.org/dmcritchie/excel/buildtoc.htm

Sheetnames that have spaces will require the single quotes
as shown above. The single quotes will actually be removed
if not needed -- not needed in most of the above examples.

More information on my
Worksheets in VBA Coding and in Worksheet Formulas
http://www.mvps.org/dmcritchie/excel/sheets.htm

 

[Dave Peterson]

One more:

I put SheetName in A1.
And then I put the sheetnames in A2:A11 (sheet01, ..., sheet10)
then I put the address of the cell I wanted to retrieve in B1.
Then in B2, I put this formula:
=INDIRECT("'" & $A2 &"'!"&$B$1)
And dragged down.

Then I used that to for my chart.

I could put additional addresses in C1 and use a similar formula to retrieve
those values, too. (watch out for the $ signs and addresses if you copy to the
right.)

 

[Guest]

Ahh... Yes, of course. I haven't thought of the possiblility of an indirect
reference.

Thanks,

Jens.