# IF + Manual

Occasional Visitor

# IF + Manual

Hello! I would need a function to do the following (for example):

=IF(A1=1;"X";"_allow manual text_")

In words: I would like a cell to be filled automatically if some conditions are met, but manually if they are not, but without losing the formula in the cell to be filled.

Is it possible?

2 Replies

# Re: IF + Manual

Not literally possible. Once you enter something else "manually" you've obliterated what was there before.

However, you could do your manual entry in a designated cell in the same row, off to the side.

Assume your "X" --predetermined "normal" condition text--is in cell X1

Set aside column "Y" (but every row running down the sheet) for your manual entry

=IF(A1=1;\$X\$1;Y1)

Another solution, depending on how many "manual" entries are expected, how fluid you want those to be...if, in other words, your normal expectation is answer A, but you also will allow B, C, D, E, F, G, H (etc., but finite) is to use Data Validation with A as the default value.

In short, I can envision several ways to approach this, but would need to find out from you a bit more the "big picture" of what you're trying to do.

# Re: IF + Manual

You cannot do that with a formula; it would require VBA. If that is OK: let's say you want to apply this to cells B1:B10 - if the corresponding cell in A1:A10 equals 1, enter "X", otherwise let the user enter data.

Right-click the sheet tab.

Select 'View Code' from the context menu.

Enter the following code into the worksheet module:

``````Private Sub Worksheet_Change(ByVal Target As Range)
Dim rng As Range
If Not Intersect(Range("A1:A10"), Target) Is Nothing Then
Application.EnableEvents = False
For Each rng In Intersect(Range("A1:A10"), Target)
If rng.Value = 1 Then
rng.Offset(0, 1).Value = "X"
End If
Next rng
Application.EnableEvents = True
End If
End Sub``````

Switch back to Excel.

Select B1:B10.

On the Data tab of the ribbon, click Data Validation.

Select Custom from the Allow drop-down.

Enter the formula

``=OR(A1<>1,B1="X")``