If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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? |
#2
|
|||
|
|||
Do not allow an entry in a cell if another cell has 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? |
#3
|
|||
|
|||
Do not allow an entry in a cell if another cell has 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? |
#4
|
|||
|
|||
Do not allow an entry in a cell if another cell has 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? |
#5
|
|||
|
|||
Do not allow an entry in a cell if another cell has 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? |
#6
|
|||
|
|||
Do not allow an entry in a cell if another cell has 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? |
Thread Tools | |
Display Modes | |
|
|