| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Multiple Data Validation
I have six columns titled B, M, L, A, S, E. I have a data validation that
prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
I have six columns titled B, M, L, A, S, E.
What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
The actual colums are B4 through G4. Users are supposed to enter an X, but
are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
Users are supposed to enter an X, but are not
prohibited from entering a different value. prohibits X's from being entered under more than 3 of the 6 columns So, I guess that means a user can enter 3 Xs and up to 3 other entries for the other 3 cells? Try this: =AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+( $F4="x")<3) -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... The actual colums are B4 through G4. Users are supposed to enter an X, but are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
Thanks. That worked great for prohibiting entries under B, L, and S
together. I think I was unclear on the other part. Users are entering an x under columns like a check mark, and they may select a maximum of 3 columns to enter their x under (from the choices B, M, L, A, S and E). I was using data validation to prohibit more than 3 column selections using this: =COUNTA($B$4:$G$4)<4. Is there any way to combine both your formula and this one with data validation? Thanks so much, I appreciate your help! "T. Valko" wrote: Users are supposed to enter an X, but are not prohibited from entering a different value. prohibits X's from being entered under more than 3 of the 6 columns So, I guess that means a user can enter 3 Xs and up to 3 other entries for the other 3 cells? Try this: =AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+( $F4="x")<3) -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... The actual colums are B4 through G4. Users are supposed to enter an X, but are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
If I were you I'd restrict the user to entering only the single character
"X". Then you could use this: Select the range B4:G4 DataValidation Allow: Custom Formula: =AND(OR(B4="x",B4=""),COUNTIF($B4:$G4,"X")<=3,$B4& $D4&$F4<"xxx") Uncheck Ignore blank OK out Allows only the single character X Allows only a maximum of 3 Xs in the entire range Does not allow those 3 Xs to be in B4, D4 and F4 -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... Thanks. That worked great for prohibiting entries under B, L, and S together. I think I was unclear on the other part. Users are entering an x under columns like a check mark, and they may select a maximum of 3 columns to enter their x under (from the choices B, M, L, A, S and E). I was using data validation to prohibit more than 3 column selections using this: =COUNTA($B$4:$G$4)<4. Is there any way to combine both your formula and this one with data validation? Thanks so much, I appreciate your help! "T. Valko" wrote: Users are supposed to enter an X, but are not prohibited from entering a different value. prohibits X's from being entered under more than 3 of the 6 columns So, I guess that means a user can enter 3 Xs and up to 3 other entries for the other 3 cells? Try this: =AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+( $F4="x")<3) -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... The actual colums are B4 through G4. Users are supposed to enter an X, but are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
Thanks! It worked perfectly. I appreciate all of your help!
"T. Valko" wrote: If I were you I'd restrict the user to entering only the single character "X". Then you could use this: Select the range B4:G4 DataValidation Allow: Custom Formula: =AND(OR(B4="x",B4=""),COUNTIF($B4:$G4,"X")<=3,$B4& $D4&$F4<"xxx") Uncheck Ignore blank OK out Allows only the single character X Allows only a maximum of 3 Xs in the entire range Does not allow those 3 Xs to be in B4, D4 and F4 -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... Thanks. That worked great for prohibiting entries under B, L, and S together. I think I was unclear on the other part. Users are entering an x under columns like a check mark, and they may select a maximum of 3 columns to enter their x under (from the choices B, M, L, A, S and E). I was using data validation to prohibit more than 3 column selections using this: =COUNTA($B$4:$G$4)<4. Is there any way to combine both your formula and this one with data validation? Thanks so much, I appreciate your help! "T. Valko" wrote: Users are supposed to enter an X, but are not prohibited from entering a different value. prohibits X's from being entered under more than 3 of the 6 columns So, I guess that means a user can enter 3 Xs and up to 3 other entries for the other 3 cells? Try this: =AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+( $F4="x")<3) -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... The actual colums are B4 through G4. Users are supposed to enter an X, but are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Multiple Data Validation
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "JStiehl" wrote in message ... Thanks! It worked perfectly. I appreciate all of your help! "T. Valko" wrote: If I were you I'd restrict the user to entering only the single character "X". Then you could use this: Select the range B4:G4 DataValidation Allow: Custom Formula: =AND(OR(B4="x",B4=""),COUNTIF($B4:$G4,"X")<=3,$B4& $D4&$F4<"xxx") Uncheck Ignore blank OK out Allows only the single character X Allows only a maximum of 3 Xs in the entire range Does not allow those 3 Xs to be in B4, D4 and F4 -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... Thanks. That worked great for prohibiting entries under B, L, and S together. I think I was unclear on the other part. Users are entering an x under columns like a check mark, and they may select a maximum of 3 columns to enter their x under (from the choices B, M, L, A, S and E). I was using data validation to prohibit more than 3 column selections using this: =COUNTA($B$4:$G$4)<4. Is there any way to combine both your formula and this one with data validation? Thanks so much, I appreciate your help! "T. Valko" wrote: Users are supposed to enter an X, but are not prohibited from entering a different value. prohibits X's from being entered under more than 3 of the 6 columns So, I guess that means a user can enter 3 Xs and up to 3 other entries for the other 3 cells? Try this: =AND(COUNTIF($B4:$G4,"x")<=3,($B4="x")+($D4="x")+( $F4="x")<3) -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... The actual colums are B4 through G4. Users are supposed to enter an X, but are not prohibited from entering a different value. Thanks for your help! "T. Valko" wrote: I have six columns titled B, M, L, A, S, E. What are the *actual* columns being used? Are the columns a contiguous range? Is the only allowable entry a X? -- Biff Microsoft Excel MVP "JStiehl" wrote in message ... I have six columns titled B, M, L, A, S, E. I have a data validation that prohibits X's from being entered under more than 3 of the 6 columns per row. I need another data validation that prohibits users from entering their 3 X's under the columns B, L, and S together (all other combinations are fine). Any help is much appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| multiple data validation | Excel Discussion (Misc queries) | |||
| Multiple Data Validation | Excel Worksheet Functions | |||
| multiple choice data validation | Excel Discussion (Misc queries) | |||
| Multiple Data Validation | Excel Discussion (Misc queries) | |||
| Multiple Data Validation Criterias | Excel Worksheet Functions | |||
Linear Mode
