Circular formulas

[SueB]

If I create a formula for a cell, in that cell, I can't add any data to make
the formula work as it erases the formula.

Example - I want "1" to change to "Male" and "2" to change to "Female".
I've created the formula no problem but I need the data to be reflected in
the cell in which I have created the formula. Is that even possible?

 

[Marcelo]

http://www.cpearson.com/newsletter/content/2007_07_23.htm

Hello,

Try to look the Chip Pearson's newsletter on this address.

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"SueB" escreveu:

 

[Gord Dibben]

You cannot manually enter data in a cell that contains a formula so forget that
step.

You can use a helper cell to enter the 1 or 2 or event code with no formula.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo endit
Application.EnableEvents = False
Select Case Target.Value
Case "1"
Target.Value = "Male"
Case "2"
Target.Value = "Female"
'add more cases if needed
End Select
endit:
Application.EnableEvents = True
End Sub

This is sheet event code. Right-click on the sheet tab and "View Code"

Copy/paste the code into that sheet module. Edit "A1" to your cell address.

Alt + q to return to the Excel window.

Enter 1 or 2 in A1.......or your edited cell.


Gord Dibben MS Excel MVP