Moving cells in excel but retaining formula
Posted by April M on April 12, 2001 9:13 AM
I often insert columns into my sheets and then want to move or copy one of the columns that has been displaced, but retaining the formulas as they are.
However, the formulas are always adjusted and refer to nonsence cells.
I cant use the anchor $ because there are sometimes when I require them to be changed.
I remember supercalc used to cope with this matter admirably.
Does anyone know if this can be done in excel.
Cheers - April M.
Posted by Mark W. on April 12, 2001 9:19 AM
April, you can move, =INDIRECT("A1"), to your
heart's delight and it will always refer to cell
A1. Of course, you'll have to edit the text
argument if you want it to refer to another cell.
Posted by Dave Hawley on April 12, 2001 4:24 PM
Rather than fill your sheet up with unnecessary formulas, you can use this simple macro.
Sub SpecialCopy()
'Written by OzGrid Business Applications
'www.ozgrid.com
Selection.Areas(2) = Selection.Areas(1).Formula
End Sub
To use it push Alt+F11 and go to Insert>Module. Paste in the code. Push Alt+Q, then push Alt+F8 and click "SpecialCopy". Click Options and assign a Shortcut key.
Now select your cells you want to copy, then holding down the Ctrl key select where you want them copied to. Just be sure the range has the same amount of rows. Now push you shortcut key.
Dave
OzGrid Business Applications
Posted by April M on April 13, 2001 12:42 AM
Great! Brilliant!! Works a treat
I've put it on a button
Thanks Dave,
April.
Posted by Dave Hawley on April 13, 2001 4:30 AM
Pleasure :o)
OzGrid Business Applications
