Excel formula

M

MICHAEL

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?
 
S

SidBord

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 future:

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.
 
G

Gord Dibben

Michael

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

A1 will be your choice of cellref.

Gord Dibben Excel MVP
 
Top