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?