"='sheet 1'!D4" auto fill sheet to sheet ='sheet 2'!D4

[mistewalker]

how do auto fill formula ='sheet 1'!D4 to sheet 2 ='sheet 2'!D4
with out change the formula maunually
I want to make one sheet with the formula and add sheets and have the new
sheet look at the adjacent sheet etc

 

[Chip Pearson]

You can use the INDIRECT function to do this. Note that this will work only
with sheets that have a name of SheetN, where N is some number. It will
*not* work if the sheet have other names.

Enter the following formula in some cell, and change the value N in the
formula to the row number in which it was first entered.

=INDIRECT("'Sheet"&ROW()-N+1&"'!D4")

Now, fill down for as many cells as you have sheets.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[Per Erik Midtrød]

how do auto fill formula ='sheet 1'!D4 to sheet 2 ='sheet 2'!D4
with out change the formula maunually
I want to make one sheet with the formula and add sheets and have the new
sheet look at the adjacent sheet etc

You could use the indirect function.
Put this in row 1 your formula sheet and copy down:
=INDIRECT("Sheet" &ROW() &"!D4")

Per Erik

 

[mistewalker]

i actually have the sheets named as dates "1-7-08" as an example and i just
want to be able to make a new sheet every month and have the formula change
to look at the previous sheet no matter what the name
make sense?

 

[Pete_UK]

You could use Find & Replace to change the old sheet name to the new
sheet name.

Hope this helps.

Pete

 

[Gord Dibben]

You could use a User Defined Function to refer to the previous sheet.

Function PrevSheet(rg As Range)
n = Application.Caller.Parent.Index
If n = 1 Then
PrevSheet = CVErr(xlErrRef)
ElseIf TypeName(Sheets(n - 1)) = "Chart" Then
PrevSheet = CVErr(xlErrNA)
Else
PrevSheet = Sheets(n - 1).Range(rg.Address).Value
End If
End Function

=PrevSheet(D4) entered in a cell on sheet 1-7-08 will return value from D4 on
sheet 1-6-08


Gord Dibben MS Excel MVP