Copying and adjusting formulas

I have a workbook made of a lot of consecutive sheets( each one a date).
I use a formula [f4 of the present sheet= h4 of the previous sheet]
meaning my actual stock is the remaining o the previous. I am looking
for a way to copy the formula [and update] to consecutive sheets. At
presen I have to go one by one. Any hints? Thanks beforehand.

Dimitry

If you're willing to use a User Defined Function.......

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(H4) entered in F4

Note: you can group the worksheets from 2 onwards and enter the formula on
the activesheet.

Will be entered in all sheets except first sheet.


Gord Dibben MS Excel MVP

On Sat, 02 Aug 2008 15:26:14 -0300, dimitry wrote:

I have a workbook made of a lot of consecutive sheets( each one a date).
I use a formula [f4 of the present sheet= h4 of the previous sheet]
meaning my actual stock is the remaining o the previous. I am looking
for a way to copy the formula [and update] to consecutive sheets. At
presen I have to go one by one. Any hints? Thanks beforehand.

Dimitry