relative formulas

[*Scott]

Is there a way to copy a formula so it stays relative to a
cell on the preceeding worksheet. I need to have about 75
tabs that carry over totals from the previous tab. Any
ideas? thanks

 

[Bob Phillips]

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Bob Phillips]

Hi Scott,

As long as your sheet tab are all of the format Sheet1, Sheet 2, etc, you
could use

=INDIRECT("Sheet"&SUBSTITUTE(MID(CELL("filename",A1),FIND("]",CELL("filename
",A1))+1,99),"Sheet","")-1&"!B2")

This returns the value in B2 in the previous sheet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Scott]

Thanks Bob - I'll give it a try. I really appreciate your
assist! have a great day.

Scott

-----Original Message-----
Hi Scott,

As long as your sheet tab are all of the format Sheet1, Sheet 2, etc, you
could use

=INDIRECT("Sheet"&SUBSTITUTE(MID(CELL("filename",A1),FIND ("]",CELL("filename
",A1))+1,99),"Sheet","")-1&"!B2")

This returns the value in B2 in the previous sheet

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Is there a way to copy a formula so it stays relative to a
cell on the preceeding worksheet. I need to have about 75
tabs that carry over totals from the previous tab. Any
ideas? thanks

.

 

[Gord Dibben]

Scott

Copy/paste this UDF to your workbook.

Function PrevSheet(rg As Range)
'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.
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

Group all sheets past the first sheet(shift + click)

In a cell, say A1, enter =PrevSheet(B2)

Ungroup the sheets.

A1 on each sheet will refer to B2 on the previous sheet.

Gord Dibben Excel MVP