Restricting data entry to 1 of 3 cells

**Gunnjg** · 08-21-2008, 02:33 PM

I have an excell sheet with 3 columns. Each column has a drop down list. Once a choice is made in a column I need the other two columns to not have data.
It is a rating. Column 1 holds ratings of 1, Column 2 holds ratings of 2, Column 3 holds rating of 3. Only one choice can be made 1,2 or 3. So if a user choses Column 1 and then decides to increase the rating, when he enters the 2 or 3 the original answer in 1 is removed.

I know it could be handled with a single column with data validation restricting the answer to 1,2,or 3. But the person I am doing this for wants three coulmns.

**ChemistB** · 08-21-2008, 03:43 PM

If you're willing to lose your dropdowns, you can set up data validation to do it as follows. Otherwise, you'll need to use VBA.

Assuming the columns are A, B, C starting in row 2. Highlight Column A from 2 to last entry. Go to data Validation, choose Custom and remove the checkmark next to "Ignore Blank".
Enter this formula;

=OR(A2="",AND(A2=1,B2="",C2=""))

Then go to the Error Alert Tab and type something like this

A rating of 1 can only be used here if there is no rating in Columns B and C.  Erase those columns first.

Whenever anyone tries to enter anything other than a 1 or column B or C is not blank in that row, they will get that error message.

Does that work for you?

ChemistB

**modytrane** · 08-21-2008, 04:23 PM

Take a look at the example attached.
I am sure someone can help you write a macro with a loop for large number of entries..
I have created a small exmple with three sets of buttons. When you select any one button for rating, other two buttons will turn off and clear the ratings.
Let me know if this works for you.
modytrane.