copy a formula

  • Thread starter Thread starter Mark Kennedy
  • Start date Start date
M

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
 
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.
 
Many thanks Leo


Leo Heuser said:
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.
Click to expand...
 
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.
 
Back
Top