relative references

[Rob]

I wish to create a sum formula that references the previous sheet in a
multiple worksheet workbook. I would then like to copy the formula to the
next sheet and maintain the relative refrence to the new previous sheet. Is
it possible to do this without having to change the reference in each sheet?

 

[John Bundy]

Not really well without code, but it you put a formula somewhere in each
sheet that gets the prior sheets name and the location of the sum, you can
reference the cell with an indirect. Something like this
=MID(CELL("filename",Sheet1!A1),FIND("]",CELL("filename",A1))+1,256)&"!B2"
Sheet1 being the prior sheet and B2 containing the value

 

[Gord Dibben]

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

Example of use......................

Say you have 12 sheets, sheet1 through sheet12...........sheet names don't
matter.

In sheet1 you have a formula in A10 =SUM(A1:A9)

Select second sheet and SHIFT + Click last sheet

In active sheet A10 enter =SUM(PrevSheet(A10),A1:A9)

Ungroup the sheets.

Each A10 will have the sum of the previous sheet's A10 plus the sum of the
current sheet's A1:A9


Gord Dibben MS Excel MVP