Excel formula
 

I have an excel workbook with several sheets. All of the
sheets are a copy of the first one. I have a formula that
contains the sheetname and I need this formula on all
sheets. Is there a way to copy this formula down so that
the formula stays the same, but it changes sheet1 to
sheet2 in the second cell, sheet3 in the third, and so on?

Excel formula
 

I can tell you how I solved that problem, but you have to
write a Visual Basic Macro to do it.
First, you can create a new macro called "SheetName" for
use in the futu

Function SheetName() as String
SheetName = Range("A1").Parent.Name
End Function

then in your processing formula substitute "SheetName()"
(without quotes) wherever you normally have the sheetname.
The macro SheetName will return the value of the currently
active sheet name.




-----Original Message-----
I have an excel workbook with several sheets. All of the
sheets are a copy of the first one. I have a formula that
contains the sheetname and I need this formula on all
sheets. Is there a way to copy this formula down so that
the formula stays the same, but it changes sheet1 to
sheet2 in the second cell, sheet3 in the third, and so on?
.

Excel formula
 

Michael

=INDIRECT("Sheet" & (ROW()) & "!A1")

A1 will be your choice of cellref.

Gord Dibben Excel MVP


On Tue, 6 Jul 2004 12:40:47 -0700, "MICHAEL"
wrote:

I have an excel workbook with several sheets. All of the
sheets are a copy of the first one. I have a formula that
contains the sheetname and I need this formula on all
sheets. Is there a way to copy this formula down so that
the formula stays the same, but it changes sheet1 to
sheet2 in the second cell, sheet3 in the third, and so on?