+ Reply to Thread
Results 1 to 6 of 6

How do I prevent data entry in one cell if an entry exist in another cell

  1. #1
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    How do I prevent data entry in one cell if an entry exist in another cell

    I have a workbook with 4 adjacent colums. The user is to make an entry in ONLY one of these columns on each row. What I would like to happen is that if the user puts an entry in any of the 4 columns, the other three would be locked (or maybe even filled with a color) or otherwise unusable unless the entry in the other column was deleted. Any suggestions.

  2. #2
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How do I prevent data entry in one cell if an entry exist in another cell

    Look here with Data validation:

    (from A1 to A20)
    Attached Files Attached Files

  3. #3
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How do I prevent data entry in one cell if an entry exist in another cell

    I'm not sure I understand the logic in the Data Validation formula. Can you explain what it's doing with your formula. Your example was good, but I want the user to only be able to enter a number value in just one cell in A1:A20) ex. If they put '2' in A4, and then try to also enter any number in A15, they should get the error.

  4. #4
    Forum Moderator zbor's Avatar
    Join Date
    02-10-2009
    Location
    Croatia
    MS-Off Ver
    365 ProPlus
    Posts
    15,576

    Re: How do I prevent data entry in one cell if an entry exist in another cell

    Isn't that what it's done?

    Try enter same number you'll get error.
    enter different number it's OK.

    Data validation looks with =COUNTIF($A$1:$A$20;$A1)<2 is there less than 2 entries. If yes then OK, if not then error.

  5. #5
    Valued Forum Contributor
    Join Date
    07-29-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2003/Excel 2010
    Posts
    534

    Re: How do I prevent data entry in one cell if an entry exist in another cell

    Based on how i read rkjudy's requirement in post #1:"The user is to make an entry in ONLY one of these columns on each row."
    Both datavalidation and conditional formatting are applied.
    Attached Files Attached Files

  6. #6
    Forum Contributor rkjudy's Avatar
    Join Date
    03-31-2009
    Location
    Longview, TX
    MS-Off Ver
    MS Office 2010
    Posts
    239

    Re: How do I prevent data entry in one cell if an entry exist in another cell

    The data validation presents an error if i put a '1' in any two cell in A1:A20, but it I put a '1' in one cell and any number larger than '1' in one of the other, no error is returned.

+ 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