If Statement: want user to be able to enter text in cell if FALSE

Hi

I have the following formula in cell F16

=IF($M$1 =3, "149028","")

However, what I would really like is for the user to be able to input a
value in F16 is M1 does NOT equal 3.

Is there a way to do this. The current formula leaves F16 blank if M1
does not equal 3, but when the user types another value, the formula is
erased.

what am i missing? Many thanks

You can partly do it with cell validation.

Allow custom > =$M$1<>3

This will allow data to be entered only if M1 does not equal 3

However... anything that is already in this cell (including your formula) will be removed and it doesnt stop anyone using delete or backspace over your cell to removed the contents that way either.

Could you use another cell for the user input?

=IF($M$1 =3, "149028",$M$2)

And in M2 you might insert a "Comment", or a data validation "input
Message", giving directions on what the user should do.

--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================

<[email protected]> wrote in message
news:[email protected]...
Hi

I have the following formula in cell F16

=IF($M$1 =3, "149028","")

However, what I would really like is for the user to be able to input a
value in F16 is M1 does NOT equal 3.

Is there a way to do this. The current formula leaves F16 blank if M1
does not equal 3, but when the user types another value, the formula is
erased.

what am i missing? Many thanks

You're missing nothing. A cell can contain either a constant (e.g., user
entered value) or a formula, but not both.

You could use a worksheet event macro. Perhaps something like:

Private Sub Worksheet_Calculate()
If Me.Range("M1").Value = 3 Then _
Me.Range("F16").Value = 149028
End Sub

Put this in the worksheet code module (right-click on the worksheet tab
and choose View Code). This will then update every time the sheet is
calculated.

Note that this will enter 149028 as a number, not a text string as your
current formula does.



In article <[email protected]>,
"[email protected]" <[email protected]> wrote:

> I have the following formula in cell F16
>
> =IF($M$1 =3, "149028","")
>
> However, what I would really like is for the user to be able to input a
> value in F16 is M1 does NOT equal 3.
>
> Is there a way to do this. The current formula leaves F16 blank if M1
> does not equal 3, but when the user types another value, the formula is
> erased.
>
> what am i missing?