Worksheet relative reference

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="";...

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

"Ciprian" wrote:

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="";...

I find it easiest to do some editreplace first before copying.

EditReplace

What: =

With: ^^^

Replace all.

Copy the results to other workbook then reverse the editreplace.

Close source workbook without saving.


Gord Dibben MS Excel MVP

On Tue, 27 Apr 2010 08:26:01 -0700, Ciprian
wrote:

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="";...

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



--
Regards
Dave Hawley
www.ozgrid.com
"Ciprian" wrote in message
...
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="";...