How do I make the formulas relative when coping a worksheet?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need the formula to refer back to the previous page when inserting a new
worksheet, but cannot get the formulas to update automatically.

I am using Excel 2000 9.0.6926 SP3
 
Copy/paste this User Defined Function to a general module in your workbook.

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

'Enter =PrevSheet(B2) on sheet2 and you'll get B2 from sheet1.

If not familiar with VBA and macros see David McRitchie's site.

http://www.mvps.org/dmcritchie/excel/getstarted.htm

Gord Dibben Excel MVP
 
You must log in or register to reply here.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Automatically update excel worksheet formula 7
Copying formulas with relative references 2
Replace worksheet without affecting the referencing formula 1
Freeze Formulas - Replace Worksheet 5
Automatically Enter Formula in a Cell After Entering Data in Another Cell 0
Inserted Row has no formulas in it 2
relative references 2
Copy worksheet to new doc w/formulas not referencing previous doc 4

Back
Top