Copy without relative formulas changing

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.

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

<murphyobrien@gmail.com> wrote in message news:1147427263.506734.70200@v46g2000cwv.googlegroups.com...
|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.
|

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

Murphy

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

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

Murphy

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

Not optimized, but answers your needs.

HTH
--
AP

<murphyobrien@gmail.com> a écrit dans le message de news:
1147427263.506734.70200@v46g2000cwv.googlegroups.com...
>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.
>

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

<murphyobrien@gmail.com> wrote in message
news:1147454194.910024.268600@j33g2000cwa.googlegroups.com...
> Duplicating, so I want the equivalent of CUT but leaving the originals.
>
> Murphy
>

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

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

murphyobrien@gmail.com 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.

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@gmail.com" 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.
>
>