Cell based on formula and allow user input?

Cell A5 has a Yes_No pull-down list for data validation.

In cell B5, I want to display the value 0.002 only if A5 is "Yes"

If A5 is "No", I want the user to be able to enter a number into the B5 cell without destroying the formula.

Is this possible? Thanks.

Excel 2000

Put this in the relevant sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    With Target
        If .Address = "$A$5" Then
            If LCase(.Value) = "yes" Then
                Range("B5").Value = 0.002
            Else
                Range("B5").Formula = "=A1" ' replace with the correct formula
            End If
        End If
    End With
End Sub

Adding Code to a Sheet module
1. Copy the code from the post
2. Right-click on the tab for the relevant sheet and select View Code. This opens the Visual Basic Editor (VBE) and shows the object module for the selected worksheet.
3. Paste the code in the window
4. Press Alt+Q to close the VBE and return to Excel

Hi brickwall015 and welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.

click on FORUM at the top of the screen, select the appropriate forum (maybe General?) and then start a new thread there

No, it's not possible (without VBA).

But you can use a third cell: =if(a5 = "yes", 0.002, b5)