Excellent answer Bernie thankyou, this has saved alot of time.

Thanks
Brian

"Bernie Deitrick" wrote:

Brian,

Select the cell with the formula, and run the macro below. It will change most cell references to
values, with the exception of multi-cell ranges. That would be do-able, but would require a
re-write.

HTH,
Bernie
MS Excel MVP

Sub Convert()
'Converts cell references to values within the
'Activecell's formula
'Written by Bernie Deitrick Dec 15, 2005

Dim strForm As String
Dim strOrig As String
Dim Addr As Variant
Dim i As Integer
Dim myCell As Range
Const Operators As String = "=+-*/^()"

strForm = ActiveCell.Formula
strOrig = ActiveCell.Formula

For i = 1 To Len(Operators)
strForm = Replace(strForm, Mid(Operators, i, 1), "*")
Next i

Addr = Split(strForm, "*")
For i = LBound(Addr) To UBound(Addr)
On Error GoTo NotCell
Set myCell = Range(Addr(i))
strOrig = Replace(strOrig, Addr(i), myCell.Value)
NotCell:
Resume GoOn
GoOn:
Next i

ActiveCell.Formula = strOrig
End Sub






"Co-op Bank" wrote in message
...
Hello, is there a way to change a formula for example '=a1+a2+a3' to show its
constant values instead in the cell so it would show '=10+20+30' assuming
'a1' = 10 etc...

Would this be a VBA solution? Any help suggestions would be much appreciated.

Regards
Brian
Manchester, England