Copying a formula "literally"

[Guest]

Say I have the following relative reference formulas in Col A:
+D1
+D2
+D3

I want exactly the same formulas in Col B:
+D1
+D2
+D3

I can change the formulas in Col A to absolute reference, copy/paste them,
and then change all formulas back to relative. Along similar lines I could
add an apostrophe in front of each formula before copying it, thereby
changing it to text, then removing it after the copy/paste.

But these methods are manual and cell by cell.

Is there a quicker way to do this, short of writing VBA code?

Art

 

[Max]

.. I have the following relative reference formulas in Col A:
+D1
+D2
+D3

I want exactly the same formulas in Col B:
+D1
+D2
+D3

....
Not a direct answer probably,
but on the face of it, why not just point col B to col A ?
eg: in B1: = A1, with B1 copied down

---

 

[Guest]

In column B, enter the first formula

=D1

Then, select the cell w/this formula, put your mouse on the little black
cross in the bottom right corner, click and hold the left mouse button while
you drag the formula down as far as you need.

 

[Max]

Perhaps more directly to your subject line ..

In the source sheet,

Select the range of formula cells
Click Edit > Replace
Find: =
Replace with: zzzzz (say)
Click Replace All

Then select the range > copy

and in the destination sheet,

Right-click > Paste
Click Edit > Replace
Find: zzzzz
Replace with: =
Click Replace All


---

 

[Dana DeLouis]

Copying a formula "literally"

Hi. Any copy operation puts Excel into CutCopyMode, and will adjust the
references.
As one alternative, see if this macro will work for you.

Sub Demo()
Columns(2).Formula = Columns(1).Formula
End Sub

 

[JBarr]

Max,

If you place a $ in front of the column reference (i.e. +$D1), it will
always keep your cell reference on column D. The same thing applies to
rows if you place a $ in front of the row reference (i.e. +D$1). You
can also have a static cell reference by placing a $ in front of both
the column and row reference (i.e. +$D$1). In addition, you can copy
the cells with the formula you need and Paste Special --> Formulas
where you want the new formulas.

I hope that helps!

Jan

 

[Max]

ArthurJ, you mean <g>

---

 

[gjcase]

If you want the exact copy vs relative, simply place an apostrophe (')
ahead of the equals sign in the first formula, similar to adding
comments in some programming languages; this changes it from a formula
to text. Then copy it to the new cell, then remove the apostrophes from
both.

Incidentally, this works pretty well if you are trying to debug a long
formula and Excel won't let you enter the formula with a mistake in it.
You can "comment out" the entire formula, then copy it and work on
pices of the formula until you find the fix.

 

[Dave Peterson]

I wouldn't use an apostrophe. Those are very difficult to clean up with
Edit|replace's.

I'd use:

edit|replace
what: = (equal sign)
with: $$$$$=
replace all.

That unique string $$$$$= should be easy to change back to a single equal sign.