Copy without relative formulas changing

[murphyobrien]

I am looking for a way to copy a range of cells somewhere else without
the formula references changing. For instance I want to select B1:B3
and copy to C1:C3. Say they contain the formulas =A1+$A$1, =A2+$A$1,
A3+$A$1 but I want C1:C3 to still contain =A1+$A$1, =A2+$A$1,
=A3+$A$1.

I don't want to change from relative to absolute.

The only ways I have found is

1) Create a new sheet, Copy B1:B3 into the new sheet at the same
position (B1:B3). Then select the cells in the new sheet and move them
(with drag and drop) to C1:C3, then copy them to the original sheet
into C1:C3.

2) There is another trick where you replace the = in the formula so
Excel is fooled into not changing it, but that is even more labor
intensive.

Anyone know a better way to do this?

Murphy.

 

[Niek Otten]

Hi Murphy,

Change your formula to =$A1+$A$1.
That will change the row number when copied down, but will retain the column letter when copied to the right

--
Kind regards,

Niek Otten

|I am looking for a way to copy a range of cells somewhere else without
| the formula references changing. For instance I want to select B1:B3
| and copy to C1:C3. Say they contain the formulas =A1+$A$1, =A2+$A$1,
| A3+$A$1 but I want C1:C3 to still contain =A1+$A$1, =A2+$A$1,
| =A3+$A$1.
|
| I don't want to change from relative to absolute.
|
| The only ways I have found is
|
| 1) Create a new sheet, Copy B1:B3 into the new sheet at the same
| position (B1:B3). Then select the cells in the new sheet and move them
| (with drag and drop) to C1:C3, then copy them to the original sheet
| into C1:C3.
|
| 2) There is another trick where you replace the = in the formula so
| Excel is fooled into not changing it, but that is even more labor
| intensive.
|
| Anyone know a better way to do this?
|
| Murphy.
|

 

[murphyobrien]

That works in my simplified example, but in reality I don't want
anyabsolute references. Anyone else?

Murphy

 

[MartinW]

Are you duplicating them or just moving them?
If the latter just use cut instead of copy.

 

[murphyobrien]

Duplicating, so I want the equivalent of CUT but leaving the originals.

Murphy

 

[Ardus Petus]

Change your formulae to:
=INDIRECT("A1")+$A$1

Not optimized, but answers your needs.

HTH

 

[Bernie Deitrick]

Copy these to a codemodule. Then, select the cells, run FormulaToText, copy the cells, paste them,
and then select all the cells and run TransformToFormula.

HTH,
Bernie
MS Excel MVP

Sub FormulaToText()
TurnOff
Dim myCell As Range
For Each myCell In Selection
myCell.Formula = "'" & myCell.Formula
Next myCell
TurnOn
End Sub

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub


Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With

End Sub
Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With

End Sub

 

[MartinW]

OK to move the actual formula I think you're pretty much
stuck with absolutes as Niek said, but as a workaround
you could go back to basics and just have C1=B1,C2=B2,
C3=B3 etc

HTH
Martin

 

[vandenberg p]

Hello:

Twenty years or so ago I used a spreadsheet under DOS called Supercalc.
This product had the option that when you copied and pasted you could choose to
paste in one of three ways: relative, absolute or ask. The ask option
allowed you to choose how each individual cell address would be pasted.

I have never understood why at least the option to paste absolute, (with
out adding $'s) is not part of Paste Special in Excel.

Pieter Vandenberg

(e-mail address removed) wrote:
: I am looking for a way to copy a range of cells somewhere else without
: the formula references changing. For instance I want to select B1:B3
: and copy to C1:C3. Say they contain the formulas =A1+$A$1, =A2+$A$1,
: A3+$A$1 but I want C1:C3 to still contain =A1+$A$1, =A2+$A$1,
: =A3+$A$1.

: I don't want to change from relative to absolute.

: The only ways I have found is

: 1) Create a new sheet, Copy B1:B3 into the new sheet at the same
: position (B1:B3). Then select the cells in the new sheet and move them
: (with drag and drop) to C1:C3, then copy them to the original sheet
: into C1:C3.

: 2) There is another trick where you replace the = in the formula so
: Excel is fooled into not changing it, but that is even more labor
: intensive.

: Anyone know a better way to do this?

: Murphy.

 

[Ron Coderre]

Since you are inclined to replace = in the formula with a character, perhaps
this:

Using your posted example:

Select A1:A3
<edit><insert><row>
(that pushes everything down 3 rows)

Copy B4:B6 to B1:B3
Move B1:B3 to C1:C3

Copy A4:B6 up to A1:B3
Clear the contents of A4:B6

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[murphyobrien]

Since you are inclined to replace = in the formula with a character, perhaps
this:

Using your posted example:

Select A1:A3
<edit><insert><row>
(that pushes everything down 3 rows)

Copy B4:B6 to B1:B3
Move B1:B3 to C1:C3

Copy A4:B6 up to A1:B3
Clear the contents of A4:B6

Is that something you can work with?

***********
Regards,
Ron

Yes, that works. I'l try to remember it next time.

Murphy