How to do it in excel?

[Guest]

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

 

[JE McGimpsey]

One way:

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

 

[Guest]

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

 

[Sandy Mann]

If JE's formula is not what you are looking for and your sheet is not too
big then put this code in the Summary sheet module: (right-click on the
sheet tab and select view code)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
Application.ScreenUpdating = False
With Sheets(Range("A1").Value)
.Cells.Copy Destination:=Sheets("Temp").Cells(1, 1)
End With
CutCopyMode = False
Cells(1, 1).Select
MsgBox "Sheet " & Sheets(Range("A1").Value).Name & " Copied"
Application.ScreenUpdating = True
End Sub

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[David Hilberg]

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

 

[Peo Sjoblom]

Another way which might be easier to understand when copied across/down
since it uses a cell reference



--
Regards,

Peo Sjoblom

 

[Peo Sjoblom]

Oops!

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

 

[JE McGimpsey]

One way:

Create a defined name (Insert/Name/Define), say

Name in workbook: MyVariableRange
Refers to: =INDIRECT(Summary!$A$1 & "!A1:Z1000")

Then in your worksheet, enter

A1: =INDEX(MyVariableRange,ROW(),COLUMN())

Drag to A1:Z1000.

 

[Guest]

Thank everyone very much for suggestions

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

Under Summary sheet, if "A" is in cell a1, then it works, what if the
content becomes "A 2007", then there is an error to locate the address, do
you have any suggestions on modify the above formula to accept the string "A
2007" in cell A1 under Summary sheet?

Thank everyone for any suggestions
Eric

 

[Dave Peterson]

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

(wrapped the worksheet name (what ever is in summary!$a$1) inside apostrophes.)

 

[Guest]

Thank everyone very much for suggesitons
Eric

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

(wrapped the worksheet name (what ever is in summary!$a$1) inside apostrophes.)