How to do the exact copy of a formula

[Guest]

Let's say that I have a formula in A1 cell and it refers to A2. I want to
copy it to B1 but I still want it to refer to A2.
So I want to create exact copy of a formula, so it does not change its
refernce cells.

 

[FxM]

Let's say that I have a formula in A1 cell and it refers to A2. I want to
copy it to B1 but I still want it to refer to A2.
So I want to create exact copy of a formula, so it does not change its
refernce cells.

Hi Michal,

Add the $ sign before what shouldn't change !

Copying A1 to B1 with ... will give ...
A2 -> B2
$A2 -> $A2
A$2 -> B$2
$A$2 -> $A$2

Regards
FxM

 

[IlanR]

Another way
Go to A1, highlight the Edit line and press Ctrl + C, then press Esc
Now go to B1 and press Ctrl + V
Your formula in A1 is copyied to B1

 

[Guest]

Thanks, but what if I'd like to do this with more than one cell?
To copy formulas from A1, A2, A3 to B1, B2, B3.

Michal

 

[Arvi Laanemets]

Hi

Select A1:A3
Find and Replace> "=" with "_=">Replace All
Copy A1:A3 to B1:B3
Select A1:B3
Find and Replace> "_=" with "=">Replace All

 

[Guest]

Thanks, That's what I needed.
Why didn't i think of it? Damn I'm stupid.

Michal

 

[Arvi Laanemets]

Btw., I can't imagine, how do you need 2 identical sets of
(function-returned) values on sheet. And when it is absolutely neccessary
anyway, then why not simply into B1
=A1
, and copy to B1:B3?

When you use this to move formulas to new location, then simply select the
range with original formulas, and drag them to new location. A bonus - all
references remain intact.

When you want to create an identical copy of sheet, then simply right-click
on sheet tab and select 'Move or Copy' from drop-down menu (don't forget to
check 'Create a Copy' there)

 

[IlanR]

If you don't want to do it manualy, cell by cell, I think you will need
to use a macro.
Sorry I can't help you there
Ilan

 

[Guest]

Hi, let me explain what was it for...

I have one table with mentioned formulas. These formulas refer to some cells
in other sheets. On top of it I have a button, which is used to "control"
what does this table display. First position shows data forecast for Q1'06
that was placed in May, second position shows data forecast for Q1'06 that
was placed in June, third position shows data forecast for Q1'06 that was
placed in July etc...

I needed the second table with exactly the same formulas so I could do a
simple comparison, how did these forecast change.
In the first table I choose May, in second July and this way I can see how
is the DELTA between these forecasts.

The solution you showed me is perfect to me. THANKS