copy a formula

[Mark Kennedy]

I want to copy formulas without changing the cell references and not
relative without the $ signs.

Example: In C1 I have =A1+B1 (actually mine is complex). I want to copy it
as =A1+B1 to D1 D2 D3 (and many more) is there a clever way to do this.

Thanks
Mark

 

[Leo Heuser]

Mark

One way:

0. Make a copy of cell C1 by selecting C1. Select the
formula *in the formula bar* and press <Ctrl>c and <Esc>
Put the formula in a unused cell with <Ctrl>v

1. Make all references in C1 absolute, i.e. =$A$1+$B$1
(Use <F4> for a fast way of doing it, i.e. in the formula bar
move the cursor to a reference and press <F4>. You
can press four times to cycle through the four possibilities)
2. Copy C1
3. Select the cells to receive the formula (you may have
to use <Ctrl>, if the cells are not contiguous)
4. Hit <Enter>
5. While the lot is selected choose Edit > Replace
and replace $ by nothing. OK.
6. Get the copy from point 0 and place it in C1.
7. Delete the temporary cell, that held the copy.

 

[Mark Kennedy]

Many thanks Leo

Mark

One way:

0. Make a copy of cell C1 by selecting C1. Select the
formula *in the formula bar* and press <Ctrl>c and <Esc>
Put the formula in a unused cell with <Ctrl>v

1. Make all references in C1 absolute, i.e. =$A$1+$B$1
(Use <F4> for a fast way of doing it, i.e. in the formula bar
move the cursor to a reference and press <F4>. You
can press four times to cycle through the four possibilities)
2. Copy C1
3. Select the cells to receive the formula (you may have
to use <Ctrl>, if the cells are not contiguous)
4. Hit <Enter>
5. While the lot is selected choose Edit > Replace
and replace $ by nothing. OK.
6. Get the copy from point 0 and place it in C1.
7. Delete the temporary cell, that held the copy.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

 

[Leo Heuser]

You're welcome, Mark.

LeoH

 

[R.VENKATARAMAN]

wondreful idea.

using $ for absolute references in my Excel 2000 it is enough if I highlight
the formula in the formulabar and hit F4 the references become absolute.(it
becomes =$A$1+$B$1) and then you hit enter.