Excel should have a "Copy absolute formulas " in Paste Special.

[PaleRider]

For those of us that sometimes don't want to add $ signs after our formulas
are written, how about a feature in Paste Special that let's you copy the
formula exactly how you wrote it. This would especially help if you want to
test formula changes. I don't understand why you can move the contents of a
cell without change, but you can't copy without change. It would be easy to
add a "Copy absolute formula" to the Paste Special commands so the relative
references aren't changed.

----------------
This post is a suggestion for Microsoft, and Microsoft responds to the
suggestions with the most votes. To vote for this suggestion, click the "I
Agree" button in the message pane. If you do not see the button, follow this
link to open the suggestion in the Microsoft Web-based Newsreader and then
click "I Agree" in the message pane.

http://www.microsoft.com/office/com...8-4892a38bc6f7&dg=microsoft.public.excel.misc

 

[Mike H]

I don't understand your 'Suggestion to Microsoft'

It would be easy to
add a "Copy absolute formula" to the Paste Special commands so the relative
references aren't changed.

When copying and pasting a formula the relative reference remains the same
i.e. the formula =A1 in B1 is copy/pasted to D1 and the relative reference
remains the same, it becomes =C1 or 1 cell to the left or relatively the same.

For those of us that sometimes don't want to add $ signs after our formulas
are written.

You don't add $ signs after the formula is written. Once a cell address is
entered in a formula tap F4 and you get the $ signs. repeat taps scrolls
through all the relative/absolute options.

Mike

 

[Dave Peterson]

You could
select the cell with the formula to copy
copy the formula from the formula bar
select the recipient cell
paste into the formula bar

 

[David Biddulph]

I don't understand your 'Suggestion to Microsoft'


When copying and pasting a formula the relative reference remains the
same i.e. the formula =A1 in B1 is copy/pasted to D1 and the relative
reference remains the same, it becomes =C1 or 1 cell to the left or
relatively the same.

.... and if you want the formula to stay saying the same in absolute terms,
copy from the formula bar.

 

[RagDyer]

It's already there ... if you do it this way:

Click in the cell with the formula,
Select the entire formula *in the formula bar*,
Right click and choose "Copy",
Hit *either* <Esc> or <Enter>,
Right click in the target cell and choose "Paste",
And you're done!

 

[Sean Timmons]

To copy a formula without absolute reference and retain the cell references,
merely copy the formula from the address bar...

 

[Gord Dibben]

Others have posted a solution for one cell at a time.

If you have a whole herd of formula cells to copy, those of us who are
waiting for MS to provide the "Copy absolute formula" employ VBA code.

I snagged this bit from these news groups long time past.

Sub CopyFormulasExact()
Dim rngCopyFrom As Range
Dim rngCopyTo As Range
Dim intColCount As Integer
Dim intRowCount As Integer

' Check that a range is selected
If Not TypeName(Selection) = "Range" Then End
' check that the range has only one area
If Not Selection.Areas.Count = 1 Then
MsgBox "Multiple Selections Not Allowed", vbExclamation
End
End If

' Assign selection to object variable
Set rngCopyFrom = Selection
If Not Selection.HasFormula Then
MsgBox "Cells do not contain formulas"
End
End If

' This is required in case cancel is clicked.
' Type 8 input box returns a range object if OK is
' clicked or False if cancel is clicked. I do not
' know of a way to test for both cases without
' using error trapping
On Error GoTo UserCancelled

' Assign object variable to user-selected cell
Set rngCopyTo = Application.InputBox( _
prompt:="Select the UPPER LEFT CELL of the " _
& "range to which you wish to paste", _
Title:="Copy Range Formulae", Type:=8).Cells(1, 1)

On Error GoTo 0

' Loop through source range assigning any formulae found
' to the equivalent cell of the destination range.
For intColCount = 1 To rngCopyFrom.Columns.Count
For intRowCount = 1 To rngCopyFrom.Rows.Count
If rngCopyFrom.Cells(intRowCount, _
intColCount).HasFormula Then
rngCopyTffset(intRowCount - 1, _
intColCount - 1).Formula = _
rngCopyFrom.Cells(intRowCount, _
intColCount).Formula
End If
Next intRowCount
Next intColCount
Exit Sub
UserCancelled:
MsgBox "You cancelled. Try again"
End Sub

An alternative to this routine is to run a macro to change relatives to
absolutes in one go.

Sub Absolute()
Dim Cell As Range
For Each Cell In Selection
If Cell.HasFormula Then
Cell.Formula = Application.ConvertFormula _
(Cell.Formula, xlA1, xlA1, xlAbsolute)
End If
Next
End Sub


Gord Dibben MS Excel MVP