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

1. ## 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

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

3. ## Re: If Statement: want user to be able to enter text in cell if FALSE

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

<jtfreed@email.uncc.edu> wrote in message
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

4. ## Re: If Statement: want user to be able to enter text in cell if FALSE

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.

"jtfreed@email.uncc.edu" <jtfreed@email.uncc.edu> 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?

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1