You can EditReplace 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 sheet12
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 InsertModule. Paste the above code in there. Save the
workbook and hit ALT + Q to return to your workbook.
Gord Dibben Excel MVP
On Fri, 29 Dec 2006 06:58:01 -0800, R Vaughn
wrote:
In a workbook with multiple worksheets, I am trying to copy a worksheet
(e.g., Sheet4) that contains cell formulas referring to cells in the adjacent
worksheet (e.g., Sheet3). However, the formulas in the new sheet (Sheet5)
reference the cells of the original sheet (Sheet3), as what would be expected
if the formulas contained absolute row/column references.
I would like to quickly make multiple copies of the spreadsheet whose
formulas refer to its "relative" adjacent sheet. Rather than manually revise
the cells of every new copy, is there an easier way to create formulas with
"relative" worksheet references?
"Gord Dibben" wrote:
Andy
With source workbook/worksheet open.
Select all cells using CRTL + A
EditReplace
what: =
with: xxx
Replace all
Copy the sheet to the new workbook.
Reverse the editreplace on both workbooks.
Gord Dibben Excel MVP
On Tue, 10 Aug 2004 08:19:02 -0700, "andy"
wrote:
i have a sheet that im copying between workbooks, this sheet has formulas
that are supposed to look up certain sheets within the workbook. The problem
is that everytime i copy the sheet, the formulas refer back to the original
workbook so im having to spend time deleting the beginning of the formula so
it looks at the book its pasted to. Is there anyway to stop excell
automatically thinking i want to link back to the workbook its pasted from?
Gord Dibben MS Excel MVP