Prev: Counting dates within a range
Next: Formula help
From: Ciprian on 27 Apr 2010 11:26
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="";...
From: Eduardo on 27 Apr 2010 11:41
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="";...
From: Gord Dibben on 27 Apr 2010 12:22
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

On Tue, 27 Apr 2010 08:26:01 -0700, Ciprian
<Ciprian(a)discussions.microsoft.com> 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="";...

From: ozgrid.com on 27 Apr 2010 20:37
Copy the formulas as normal the Edit Replace [EBM_O2C_Scorecard_ES.xls] with
nothing.



--
Regards
Dave Hawley
www.ozgrid.com
"Ciprian" <Ciprian(a)discussions.microsoft.com> wrote in message
news:239094C0-A3BD-492A-B194-04956B7E02BB(a)microsoft.com...
> 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="";...

 | 
Pages: 1
Prev: Counting dates within a range
Next: Formula help