copy and autoadjust formula

  • Thread starter Thread starter dimitry
  • Start date Start date
D

dimitry

I have a workbook with many consecutive sheets (one for each day)In 3
different cells (on the same location in every sheet) there is a
formula, which comes from the previous sheet. Eg: C3Tuesday='Monday!F3.
How can I copy these formulas so that they autoadjust from sheet to
sheet, and I dont have to do it manually. Thanks beforehand
 
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(F3)

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
 
Back
Top