How to: free text cell, that can also assume a value depending on other cell?

Short story: no, you can't do that. A cell can have a value or a formula, but not both.

If you overwrite a formula with text, you lose the formula . . . for good, unless you replace it.

You would need VBA.

VBA is not a five minute learn. But what you need is not difficult or complicated.

You would need a Worksheet Change Event handler monitoring the cell(s) withe Data Validation drop down. When you select a value, you execute the formula (in the code) and put the returned value in the target cell.

Post a sample workbook and we can give you the code.

In the workbook, you mention cell C9. However, that is not affected by the drop down selection. I think you mean cell G9, and that is what the code updates.

Right click on the Calculations tab
Select "View Code"
In the Main code pane, paste the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' Define and set the "range of interest" = cell E9
Dim rInterest As Range
Set rInterest = Me.Range("E9")

' Check if the Target cell is the range of interest
If Intersect(Target, rInterest) Is Nothing Then Exit Sub
' Check if multiple cells have changed (say, if clearing several cells)
If Target.Cells.CountLarge > 1 Then Exit Sub

Application.EnableEvents = False    ' Stop Event handling
On Error Resume Next                ' Continue if any error(s)

If Target.Value = "" Then
    ' Clear the cell if the Target is empty
    Target.Offset(, 2).Value = ""
Else
    ' Drop the formula into the cell
    With Target.Offset(, 2)
        .Formula = _
            "=IFERROR(VLOOKUP(" & Target.Address & ",Data!B77:D87,3,FALSE),"""")"
        .Value = .Value
    End With ' Target.Offset(, 2)
End If ' Target.Value = ""

On Error GoTo 0                 ' Clear Error handling
Application.EnableEvents = True ' Resume Event handling

End Sub

Please note that you will need to save your workbook as macro enabled (.xlsm or .xlsb). And you will need to enable macros when you open the workbook.

Please see the attached updated sample workbook.

Note: I would suggest that you use Nested IF statements rather than IFS. The IFS function is very inefficient as it has to evaluate every criterion and every result before selecting the first matching result.

Thank you so so much! It works perfectly!
Actually I meant C9, I just moved my old code out of the way since I figured it's probably useless xD But it was easy to change, I just exchanged (,2) to (,-2) in a few places

I thought I would be able to do several of these - I'm making a form that repeats itself 5 times down the sheet. The goal is to have C9 affected by E9, but also C40 by E40, C71 by E71, C102 by E102 and C133 by E133. I tried a bunch of things but I can't make it work. Any advice?

Also, thank you for the advice regarding using VBA and regarding IFS! I felt like I could keep organized easier with an IFS formula than with an IF, and didn't realize the disadvantages.

Try:

Private Sub Worksheet_Change(ByVal Target As Range)

' The goal is to have C9 affected by E9,
' but also C40 by E40, C71 by E71,
' C102 by E102 and C133 by E133.

' Define and set the "range of interest" = cell E9
Dim rInterest As Range
Set rInterest = Me.Range("E9, E40, E71, E102, E133")

You shouldn't need to change anything else.

You're welcome.



If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon (Next to Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

Thanks for the rep.