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

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

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

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

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