Oops!

=INDIRECT(Summary!$A$1&"!"&CELL("address",A1))


--
Regards,

Peo Sjoblom



"Peo Sjoblom" wrote in message
...
Another way which might be easier to understand when copied across/down
since it uses a cell reference



--
Regards,

Peo Sjoblom




"David Hilberg" wrote in message
news:lcVFi.5320$eh3.2743@trndny07...
Eric,

You can modify JE McGimpsey's formula as follows:
=INDIRECT(Summary!$A$1 & "!" & ADDRESS(ROW(),COLUMN()))

To avoid getting "0" whenever there is a blank cell on the original
sheet, use this:
=IF(ISBLANK(INDIRECT(Summary!$A$1 & "!" &
ADDRESS(ROW(),COLUMN()))),"",INDIRECT(Summary!$A$1 & "!" &
ADDRESS(ROW(),COLUMN())))

Either of these might be cumbersome to recalculate over 26,000 cells,
however.

- David

Eric wrote:
Thank you for your suggestions
=INDIRECT(Summary!$A$1 & "!J10")

How to modify "!J10" into a variable?
If I drag and drop this cell, all cell locations will refer to J10 only.
I would like to drag and drop this cell across a table A1:Z1000,
therefore,
In cell D1, the formula will be =INDIRECT(Summary!$A$1 & "!D1")
In cell H12, the formula will be =INDIRECT(Summary!$A$1 & "!H12")

Do you have any suggestions?
Thank you for any suggestions
Eric


"JE McGimpsey" wrote:

One way:

=INDIRECT(Summary!A1 & "!J10")

In article ,
Eric wrote:

Does anyone have any suggestions on how to do following task in excel?

I get a workbook with 10 working sheets, which is named by A, B, C, D,
E, F, G, H, Summary, Temp
and I would like to display all cells' content in a specific sheet
into "Temp" sheet based on the sheet name in "Summary" sheet. For
example, I type "A" in cell A1 under "Summary" sheet, then in "Temp"
sheet, all cell's content will connect to "A" sheet and display what
the cells' content under "A" sheet, if I input "E" in cell A1 under
"Summary" sheet, then in Temp" sheet, all cell's content will connect
to "E" sheet and display what the cells' content under "E" sheet.

Does anyone have any suggestions on how to do it?
Thanks in advance for any suggestions
Eric