+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    jtfreed@email.uncc.edu
    Guest

    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. #2
    Registered User
    Join Date
    02-25-2005
    Posts
    83
    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. #3
    RagDyeR
    Guest

    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
    news:1113490584.971319.224370@f14g2000cwb.googlegroups.com...
    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. #4
    JE McGimpsey
    Guest

    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.



    In article <1113490584.971319.224370@f14g2000cwb.googlegroups.com>,
    "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?


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Bookmarks

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