showing formula and its value simultaneously

[raj kd]

hi
how can 1 show a formula and its resultant value in same cell or 2
different cells?
e.g. If a1=1 & a2= 2 and my formula a3=SUM(a1:a2), i would like to
see both formula SUM(a1:a2) & its resultant value which is 3 in this
case, either in 1 cell or 2 different cells.

 

[T. Valko]

You can always write the formula as a TEXT string like this:

'=SUM(A1:A2)

To combine both in the same cell:

="=SUM(A1:A2) "&SUM(A1:A2)

Or, for separate cells you could have the formula in cell A3:

=SUM(A1:A2)

And in another cell use this UDF to return the formula as a TEXT string:

Function GetFormula(cell_ref As Range) As String
If cell_ref.HasFormula Then
GetFormula = cell_ref.Formula
Else
GetFormula = ""
End If
End Function

Copy the code above and paste into a general module. Then to use it:

=GetFormula(A3)

 

[Rick Rothstein \(MVP - VB\)]

If you don't mind toggling back and forth between them, you can press Ctrl+`
(that symbol with the Ctrl key is the back-apostrophe found on the same key
as the tilde {~}). The column widths may vary as you toggle back and
forth... the best strategy I have found is to select a cell you are
interested in following so you can more easily spot it as it moves with the
column width changes.

Rick