------ Register to get rid of these "In Post" ads! ------




"cahduq" wrote:

> I have a formula in one worksheet and want to copy that result to a cell in
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/excel-new-users/85497-re-how-do-i-copy-formula-one-worksheet-another.html#post305792
> another worksheet. When I try to do that, I get "#REF#" in the destination
> cell. How can I make this work?

------ Register to get rid of these "In Post" ads! ------


Just put ' before existing formula now copy the while cell and paste in new sheet (don't ignore to copy whole cell including ' ).

After pasting formula just move ' and your formula will be in shape but make sure that the formula will work on relevant rows and columns only .....

------ Register to get rid of these "In Post" ads! ------


Here's my scenario.
Microsoft Excel 2003 (11.8033.8028) SP2

I have 'Reporting Workbook Days 1-7'
I also have 'Reporting Workbook Days 8-14'.

The master template from which both were created, had a formula error (so
the rows are summing 'off' by one row).
Example:
On Row 69, the formula in cell D69 shows:
=SUM('Mon 10.12'!D68,'Tue 10.13'!D68,'Wed 10.14'!D68,'Thu 10.15'!D68,'Fri
10.16'!D68,Sun!D68,Sat!D68)

All of those !D68 's should be !D69 's. (And this general process repeats
across for *Columns* D through X.

So, I will go through and manually correct each one.
I will do this for all of the affected rows (12 rows, and 21 columns).
That will have manually corrected 'Reporting Workbook Days 1-7'.
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/excel-new-users/85497-re-how-do-i-copy-formula-one-worksheet-another.html#post546157

However, when I try to copy these formulas over to 'Reporting Workbook Days
8-14', the formula (no matter what I choose) insists on referencing the
original workbook.
I don't want another copy of the data from Days 1-7, I want to instead
correct the erroneous formulas for Days 8-14.
(And the other 120 or so days that need to be fixed, one week at a time.)

I'd love to only do the manual work once, instead of many many times.

Anyone have a process for me?

Separately, is there a faster way other than manually updating 12x21 cells
for correcting the initial workbook (which I will then use as a template for
the others) ?


Thank you for any assistance you can provide.

------ Register to get rid of these "In Post" ads! ------


Have you tried an Edit>Replace>Lookin>Formulas

What: D68

With: D69

Secondly...............to copy formulas that reference sheets as yours do,
one method is to do an Edit>Replace in source workbook.
'Original Source: The Code Cage Forums http://www.thecodecage.com/forumz/excel-new-users/85497-re-how-do-i-copy-formula-one-worksheet-another.html#post546206

What: =

Wioth: ^^^

Replace all.

Copy to new workbook then reverse the edit>replace.

Another methos is to copy as is then Edit>Links>Change Links to the current
new workbook.


Gord Dibben MS Excel MVP

On Mon, 2 Nov 2009 15:23:01 -0800, Louis de Pointe du Lac
<LouisdePointeduLac@discussions.microsoft.com> wrote:

>Here's my scenario.
>Microsoft Excel 2003 (11.8033.8028) SP2
>
>I have 'Reporting Workbook Days 1-7'
>I also have 'Reporting Workbook Days 8-14'.
>
>The master template from which both were created, had a formula error (so
>the rows are summing 'off' by one row).
>Example:
>On Row 69, the formula in cell D69 shows:
>=SUM('Mon 10.12'!D68,'Tue 10.13'!D68,'Wed 10.14'!D68,'Thu 10.15'!D68,'Fri
>10.16'!D68,Sun!D68,Sat!D68)
>
>All of those !D68 's should be !D69 's. (And this general process repeats
>across for *Columns* D through X.
>
>So, I will go through and manually correct each one.
>I will do this for all of the affected rows (12 rows, and 21 columns).
>That will have manually corrected 'Reporting Workbook Days 1-7'.
>
>However, when I try to copy these formulas over to 'Reporting Workbook Days
>8-14', the formula (no matter what I choose) insists on referencing the
>original workbook.
>I don't want another copy of the data from Days 1-7, I want to instead
>correct the erroneous formulas for Days 8-14.
>(And the other 120 or so days that need to be fixed, one week at a time.)
>
>I'd love to only do the manual work once, instead of many many times.
>
>Anyone have a process for me?
>
>Separately, is there a faster way other than manually updating 12x21 cells
>for correcting the initial workbook (which I will then use as a template for
>the others) ?
>
>
>Thank you for any assistance you can provide.