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?

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

Thank you. Your information was very helpful.



"Gord Dibben" wrote:

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

Thanks for the feedback.

Gord

On Fri, 29 Dec 2006 10:03:00 -0800, R Vaughn
wrote:

Thank you. Your information was very helpful.



"Gord Dibben" wrote:

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


Gord Dibben MS Excel MVP