Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Control Data Entry - push entry to next cell | Excel Discussion (Misc queries) | |||
restricting entry into a cell based on entry to a previous cell | New Users to Excel | |||
Creating a separate cell entry for every individual line in a cell | Excel Discussion (Misc queries) | |||
Cell Entry That Locks Selected Cells From Any Data Entry. | Excel Worksheet Functions | |||
enter a time into a cell, have the cell show two times the entry | Excel Worksheet Functions |