Do not allow an entry in a cell if another cell has an entry.

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?

You can do it with validation but you need to get a bit fancier. Select Cells
B1:E1 with B1 as teh active cell in the selection. Choose Data -
Validation... Custom and add this formula.

=AND(B1="x", COUNTA($B1:$E1)=1)
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?

Ray,

If you want to use check boxes instead of X's, you can put code in that says

If CheckBox1.Value = True Then
CheckBox2.Value = False
CheckBox3.Value = False
End If

This might be a little tedious to set up but it should work for your purposes.

Adam Bush

"Ray IDEX" wrote:

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?

I tried the formula below suggested by Jim Thomlinson. I only allows an x
which is what I was able to do before. But it still allows entries in
multiple columns. The formula below returns a True or False value.

Ray

"Jim Thomlinson" wrote:

You can do it with validation but you need to get a bit fancier. Select Cells
B1:E1 with B1 as teh active cell in the selection. Choose Data -
Validation... Custom and add this formula.

=AND(B1="x", COUNTA($B1:$E1)=1)
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?

Perhaps I misunderstood... What that formula does is it allows only one x in
either cells B1, C1, D1 or E1. Are you saying that if there is an x in column
B anywhere then do not allow and x in C, D or E. Same for column C... No x
anywhere in columns B, D or E...
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I tried the formula below suggested by Jim Thomlinson. I only allows an x
which is what I was able to do before. But it still allows entries in
multiple columns. The formula below returns a True or False value.

Ray

"Jim Thomlinson" wrote:

You can do it with validation but you need to get a bit fancier. Select Cells
B1:E1 with B1 as teh active cell in the selection. Choose Data -
Validation... Custom and add this formula.

=AND(B1="x", COUNTA($B1:$E1)=1)
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?

Jim,

No. You understood correctly.

In cells B1, C1, D1 and E1, I want to be able to enter an x in only one
cell. If B1 is already populated with an x, and I try to enter an x in any of
the other three, I should get an error. Subsequently, if there is an x in C1,
I should not be able to enter an x in B1, D1 or E1.

"Jim Thomlinson" wrote:

Perhaps I misunderstood... What that formula does is it allows only one x in
either cells B1, C1, D1 or E1. Are you saying that if there is an x in column
B anywhere then do not allow and x in C, D or E. Same for column C... No x
anywhere in columns B, D or E...
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I tried the formula below suggested by Jim Thomlinson. I only allows an x
which is what I was able to do before. But it still allows entries in
multiple columns. The formula below returns a True or False value.

Ray

"Jim Thomlinson" wrote:

You can do it with validation but you need to get a bit fancier. Select Cells
B1:E1 with B1 as teh active cell in the selection. Choose Data -
Validation... Custom and add this formula.

=AND(B1="x", COUNTA($B1:$E1)=1)
--
HTH...

Jim Thomlinson


"Ray IDEX" wrote:

I have a spreadsheet that users fill in for an audit. They need to enter an x
in one or four rating columns. I can limit the value entered and force an x
through validaion with no problem.

What I need to do is to allow an x in only one column. There are four rating
columns B, C, D and E. If they put an x in column B, then I do not want them
to be able to put an x in columns C, D or E. Likewise, if they put an x in
column C, then B, D and E must be blank.

How do I allow only one column to have an entry?