absolute?

[wally]

I have 48 worksheets with a unique name (text) in cell a1
in each worksheet. On the 49th worksheet I would like to
list the contents of each a1 cell from the other 48
worksheets but I do not want to manually type the formula
48 times. Is there a function that I can enter once in
worksheet 49 in cell a1 and then scroll down or use some
other method to enter the formula to accomplish this?
Thanks

 

[Bernie Deitrick]

Wally,

No, there is no function that you can enter once and copy to get all that
data. You would need to use a macro.

If you want links:

Sub WallysWorld()
Dim i As Integer
For i = 1 To Worksheets.Count - 1
Worksheets(Worksheets.Count).Cells(i, 1).Formula = _
"='" & Worksheets(i).Name & "'!A1"
Next i
End Sub

Note: this macro puts the links onto the sheet furthest to the right, not
the last sheet added.

HTH,
Bernie
MS Excel MVP

 

[Paul Lautman]

Yes you can do this, I posted an answer to a similar question earlier today,
here it is again:

How's about John Walkenbach's sheet offset UDF at
http://www.j-walk.com/ss/excel/tips/tip63.htm
This can easily be changed to refer to absolute sheet numbers.

But also see Jim Rech's non-UDF method at
http://groups.google.co.uk/[email protected]
(or http://tinyurl.com/4lyoz)

 

[Ron Rosenfeld]

I have 48 worksheets with a unique name (text) in cell a1
in each worksheet. On the 49th worksheet I would like to
list the contents of each a1 cell from the other 48
worksheets but I do not want to manually type the formula
48 times. Is there a function that I can enter once in
worksheet 49 in cell a1 and then scroll down or use some
other method to enter the formula to accomplish this?
Thanks

You may be able to use the INDIRECT function.

How are your sheets named? If they have a string followed by a sequential
number, such as Sheet1; Sheet2; ... Sheet48, you could enter the following
formula into Sheet49!A1 and copy/drag it down to A48:

=INDIRECT("Sheet"&ROW()&"!$A$1")


--ron

 

[hgrove]

Paul Lautman wrote...

Yes you can do this, I posted an answer to a similar question
earlier today, here it is again: ...
But also see Jim Rech's non-UDF method at ...

...

Potentially dangerous. Jim Rech's approach involves using XLM function
in defined names. Copying cells referring to such names and pasting
them into other worksheets will crash Excel 97 and 2000 with *complete*
*data* *loss* since last save.

If you suggest using XLM functions, you should always include this
warning.