You can Edit>Replace after the fact to change the sheet name which is probably
easiest.
If you're willing to use a User Defined Function.......
Function PrevSheet(rg As Range)
'Enter =PrevSheet(B1) on sheet2 and you'll get B1 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
Say you have 12 sheets, sheet1 through sheet12.
Select sheet2 and SHIFT + Click sheet31
In B1 enter =PrevSheet(A1)
Ungroup the sheets.
Each B1 will have the contents of the previous sheet's A1
Copy/paste the UDF above into a General Module in your workbook.
If not familiar with macros and VBA, visit David McRitchie's website on
"getting started".
http://www.mvps.org/dmcritchie/excel/getstarted.htm
In the meantime..........
To create a General Module, hit ALT + F11 to open the Visual Basic Editor.
Hit CRTL + R to open Project Explorer.
Find your workbook/project and select it.
Right-click and Insert>Module. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Gord Dibben Excel MVP
how do i reference a sheet relatively and not as an absolute? Everytime I
copy a sheet the cell always refers back to the original reference.
ie. I make a formula that takes the difference between cells in sheets a and
b. I create sheet c and expect the formula to figure the difference between
b and c. This doesn't happen.
Gord Dibben MS Excel MVP
