Excel formula for if x then $

vbimport

#1

I want to create a formula that will give me this:

1 A B C D
2 X $5.00
3
4

If I put an X in A2 it will actually put $5.00 in A2 instead of the X.

Please HELP!


#2

Don’t think it is possible to type in the same cell that contains a formula that should produce a result in the same cell… it is either the formula or the cell

What you could do is duplicate the table in a second worksheet. You enter the data in worksheet 1 and the results from the formulae show in worksheet 2.


#3

You can do it with a Macro :slight_smile:

Right-click on the sheet tab and select “View Code”

Copy & paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)
    If UCase(Target.FormulaR1C1) = "X" Then
        Target.FormulaR1C1 = "$5.00"
    End If
End Sub

Close out of the code window.

Now, if you type in ‘x’ in any cell, it will turn into “$5.00” :wink: