IF + Manual

[Lucassis]

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]

@Lucassis 

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

The formula could then read

=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.

[#3]

@Lucassis 

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")

Activate the Error Alert tab.

Enter an appropriate error message to be displayed if the user tries to enter something else than "X" if column A contains 1.

Click OK.

Save the workbook as a macro-enabled workbook (.xlsm).

Makes sure that you allow macros when you open it.