relative formulas

  • Thread starter Thread starter *Scott
  • Start date Start date
S

*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
 
--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
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)
 
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
Click to expand...


.
Click to expand...
 
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
 
Back
Top