I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the destination
cell. How can I make this work?

"cahduq" wrote in message
...
I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the
destination
cell. How can I make this work?



Copy-Paste Special-Values

Hi

If you only want the result in another tab and don't care if it is next to
the sourse data, just cut and paste it to the tab you want it in. That will
preserve the relationship to the data.

Norm


"cahduq" wrote:

I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the destination
cell. How can I make this work?

Would it not be better to reference the cell where the formula is in case
the results change?


"cahduq" wrote in message
...
I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the
destination
cell. How can I make this work?

How do you reference the cell? I have tried numerous times and cannot get it
right.

Thanks

"Gail Richner" wrote:

Would it not be better to reference the cell where the formula is in case
the results change?


"cahduq" wrote in message
...
I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the
destination
cell. How can I make this work?

If all you want is the result, just copy then paste specialvalue.

You could on target sheet use =sourcesheet!A1

Or select the cell to copy and editreplace

What: =

With: ^^^

Copy the cell to next sheet then reverse the process.


Gord Dibben MS Excel MVP

On Fri, 14 Sep 2007 13:40:01 -0700, Que wrote:

How do you reference the cell? I have tried numerous times and cannot get it
right.

Thanks

"Gail Richner" wrote:

Would it not be better to reference the cell where the formula is in case
the results change?


"cahduq" wrote in message
...
I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the
destination
cell. How can I make this work?

One way

In the destination cell, type "=" (without quotes),
click on the sheet tab of the cell that you wish to reference,
click on the cell that you wish to reference,
press 'Enter'.

George Gee


"Que" wrote in message
...
How do you reference the cell? I have tried numerous times and cannot get
it
right.

Thanks

"Gail Richner" wrote:

Would it not be better to reference the cell where the formula is in case
the results change?


"cahduq" wrote in message
...
I have a formula in one worksheet and want to copy that result to a cell
in
another worksheet. When I try to do that, I get "#REF#" in the
destination
cell. How can I make this work?

I have a similar problem, I have a large number of formulas that i need to
copy from one worksheet to another. Under the paste tab the Formula option is
greyed out. (i have checked in excel options\advanced\ cut, copy and paste
and all three options are ticked.)
I have saved the source workbook as a 2007 file too so that I am copying and
pasting to the same version of excel.
Any ideas


"cahduq" wrote:

I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the destination
cell. How can I make this work?

"cahduq" wrote:

I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the destination
cell. How can I make this work?

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
.....


--
mubashir aziz
------------------------------------------------------------------------
mubashir aziz's Profile: http://www.thecodecage.com/forumz/member.php?userid=237
View this thread: http://www.thecodecage.com/forumz/sh...ad.php?t=85497

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.

Have you tried an EditReplaceLookinFormulas

What: D68

With: D69

Secondly...............to copy formulas that reference sheets as yours do,
one method is to do an EditReplace in source workbook.

What: =

Wioth: ^^^

Replace all.

Copy to new workbook then reverse the editreplace.

Another methos is to copy as is then EditLinksChange 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
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.

"cahduq" wrote:

I have a formula in one worksheet and want to copy that result to a cell in
another worksheet. When I try to do that, I get "#REF#" in the destination
cell. How can I make this work?