How to Past cells with formulas not using "$" without changing ref

[Jean-Luc]

Hello,

I would like to copy / past a large amount of cells in which "$" has not
been used in formules. Problem : referenced are changed (because "$" is not
there).

Example in A1 : "=B1+C1"

if I copy A1 to A50 : the contained formula will be : "=B50+C50" while I
would like it to be "=B1+C1 without having to insert $ in formulas

Please could you telle me if there is a way to do so ?

many thanks and kind regards.

 

[Daryl S]

Jean-Luc -

A few thoughts...
First, it might be easier to add the dollar signs before copy/pasting.

Second, if the cells really all should be exactly the same, you could just
enter a reference to the one cell with the calculation. For example, in A50,
enter =$A$1. This you can then copy/paste wherever you need it.

Third, you can copy/paste the exact formula by clicking on the cell (e.g.
A1), then highlighting the contents of the cell formula in the formula bar,
then clicking on each cell you want to paste it into, and pasting it into the
formula bar.

Again, it might be easier to put the $ in the original, as then you can
paste into many cells at once.

 

[Gord Dibben]

Select the cells.

Edit>Replace

What: =

With: ^^^

Replace all.

Cut or copy and paste.

Reverse the Edit>Replace.


Gord Dibben MS Excel MVP

 

[Nick Vivian]

Hello,

I would like to copy / past a large amount of cells in which "$" has not
been used in formules.  Problem : referenced are changed (because "$" is not
there).

Example in A1 : "=B1+C1"

if I copy A1 to A50 : the contained formula will be : "=B50+C50"  while I
would like it to be "=B1+C1 without having to insert $ in formulas

Please could you telle me if there is a way to do so ?

many thanks and kind regards.

Here's another way:
http://excelexperts.com/Excel-Tips-Copy-And-Paste-The-Same-Formula