How to display formula instead value?

[sensorflo]

Hello

Say cell A1 is "=cos(0)" and cell A2 is "=A1+1". A1 should now display
"cos(0)" or "=cos(0)", and A2 should display "2".
In other words, Id like to change the 'display properties' of A1 such
that A1 doesn't display the formulas result, but the formula itself.
However, A1 should still evaluate the formula.
Any ideas? What makes matters worse is that I only want to change
properties of A1, I don't want to insert cryptic formulas into A2 (and
the many other cells referencing A1) to achieve what I want. That would
be too cumbersome.
Has anybody any ideas?

Thank you for your help

Florian Kaufmann

 

[Niek Otten]

You can use a User Defined Function (UDF).

If you're new to VBA, look here first:

http://www.mvps.org/dmcritchie/excel/getstarted.htm

This is the function:

Function ShowFormula(a As Range)
If Application.ReferenceStyle = xlR1C1 _
Then ShowFormula = a.FormulaR1C1 _
Else: ShowFormula = a.Formula
End Function

 

[Bob Phillips]

You can't, you either display all formulae or not, not a mix. You could add
some comment with the formula using event code

Private Sub Worksheet_change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .HasFormula Then
.AddComment
.Comment.Text Text:=.Formula
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.





--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Bob Phillips]

Slight mod to cater for changed formula

Private Sub Worksheet_change(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
With Target
If .HasFormula Then
If Not .Comment Is Nothing Then
.Comment.Delete
End If
.AddComment
.Comment.Text Text:=.Formula
End If
End With

ws_exit:
Application.EnableEvents = True
End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)