Worksheet relative reference

[Ciprian]

Hi,

I have created in a workbook a worksheet containing formulas with relative
and absolute references to other worksheets in the same workbook.

As I have a large number of workbooks with exactly the same structure (one
workbook for each operating company), I tried to copy the worksheet with the
formulas to each of the workbooks. However, the formulas in the copied
worksheets have absolute references to the initial workbook.

Is there any way to specify that the formulas should have relative and not
absolute worksheet references?

Thnak you,

E.g. original formula:
=IF(' Scorecard sales orders'!$B62="";...

copied formula:
=IF('[EBM_O2C_Scorecard_ES.xls] Scorecard sales orders'!$B62="";...

 

[Eduardo]

Hi,
No there is not, but you can use the find and replace function to quickly
change the reference, press CTRL + H

 

[Gord Dibben]

I find it easiest to do some edit>replace first before copying.

Edit>Replace

What: =

With: ^^^

Replace all.

Copy the results to other workbook then reverse the edit>replace.

Close source workbook without saving.


Gord Dibben MS Excel MVP

 

[ozgrid.com]

Copy the formulas as normal the Edit Replace [EBM_O2C_Scorecard_ES.xls] with
nothing.