Copying Formulae - No change to cell referances.

  • Thread starter Thread starter Geoff Forward
  • Start date Start date
G

Geoff Forward

In Lotus and Supercalc it was possible when copying cells containing
fromulae to select a "no change" option. This effectively locked all
cell references.
Can this be done in EXCEL?

MOVE- is no use as that does not leave a copy behind.

COPY is no use unless all cell refernces have been prefixed with $

Any ideas. Icannot believe this feature has not been incorporated into
EXCEL

Geoff

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Geoff,

Simply reference the cell with the formula: if it is in cell A1, just use

=A1

HTH,
Bernie
MS Excel MVP
 
Geoff,

I should have noted that if you need to copy the formula, say to edit part
of it, you can use

F2 Shift-Ctrl-Home Ctrl-C Esc

Move to new cell

F2 Ctrl-V Enter

HTH,
Bernie
MS Excel MVP
 
Hi Geoff

This option is not available it Excel. You can however use this trick

This can be achieved by either pressing F2 and then highlighting the
formula, Copy, Enter then paste to destination. Or doing the same in the
Formular bar.

However, this is not much good for large amounts of data, so try this:
Select the range of cells with Formulae, use the Ctrl key for
non-contiguous ranges. Now go to Edit>Replace and Replace = with @. Copy
and paste to your location and then simply use Edit>Replace @ with =

In VBA you can use

Range("A1:A10")=Range("B1:B10").Formula


***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****
 
Back
Top