Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
Hi
My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
I hate merged ranges. I have a solution that I was trying to make easier, but
excel was having none of it. First, choose a cell that is not in use (perhaps even hidden or on a separate tab with other tables, etc). For my example, I chose G1 G1: =$B$7&$C$7&$D$7&$E$7&$F$7 Then, in the validation, choose CUSTOM Formula is: =$G$1="x" I tried making a named range and then using that, but it was allowing entry into the first 4 cells, but not the fifth. hope this helps. -- John C " wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
In DV Custom enter the following *array* formula:
=(SUM(LEN($B$7:$F$7))=1)*ISNUMBER(MATCH("X",$B$7:$ F$7,0)) Since it is an array formula press Shift+Ctrl+Enter rather than just enter or clicking the OK button. HTH Kostis Vezerides On Oct 15, 4:56*pm, wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
Nice 1. But since it is DV, even though *array*, you don't need to hit the
CTRL+SHIFT to enter, works just fine without it. -- John C "vezerid" wrote: In DV Custom enter the following *array* formula: =(SUM(LEN($B$7:$F$7))=1)*ISNUMBER(MATCH("X",$B$7:$ F$7,0)) Since it is an array formula press Shift+Ctrl+Enter rather than just enter or clicking the OK button. HTH Kostis Vezerides On Oct 15, 4:56 pm, wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
Hi,
And in fact pressing Shift+Ctrl+Enter has no effect whatsoever. -- Thanks, Shane Devenshire "John C" wrote: Nice 1. But since it is DV, even though *array*, you don't need to hit the CTRL+SHIFT to enter, works just fine without it. -- John C "vezerid" wrote: In DV Custom enter the following *array* formula: =(SUM(LEN($B$7:$F$7))=1)*ISNUMBER(MATCH("X",$B$7:$ F$7,0)) Since it is an array formula press Shift+Ctrl+Enter rather than just enter or clicking the OK button. HTH Kostis Vezerides On Oct 15, 4:56 pm, wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
Thanks for the tip John. You learn a new thing every day...
On Oct 15, 6:14*pm, John C <johnc@stateofdenial wrote: Nice 1. But since it is DV, even though *array*, you don't need to hit the CTRL+SHIFT to enter, works just fine without it. -- John C "vezerid" wrote: In DV Custom enter the following *array* formula: =(SUM(LEN($B$7:$F$7))=1)*ISNUMBER(MATCH("X",$B$7:$ F$7,0)) Since it is an array formula press Shift+Ctrl+Enter rather than just enter or clicking the OK button. HTH Kostis Vezerides On Oct 15, 4:56 pm, wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Validation based on three conditions
you do? Heck, I don't, I think my brain is losing something every day :)
-- John C "vezerid" wrote: Thanks for the tip John. You learn a new thing every day... On Oct 15, 6:14 pm, John C <johnc@stateofdenial wrote: Nice 1. But since it is DV, even though *array*, you don't need to hit the CTRL+SHIFT to enter, works just fine without it. -- John C "vezerid" wrote: In DV Custom enter the following *array* formula: =(SUM(LEN($B$7:$F$7))=1)*ISNUMBER(MATCH("X",$B$7:$ F$7,0)) Since it is an array formula press Shift+Ctrl+Enter rather than just enter or clicking the OK button. HTH Kostis Vezerides On Oct 15, 4:56 pm, wrote: Hi My data is in range B7:F8. Row 7 and 8 is merged (B7/B8, C7/C8 ... E7/ E8) is merged. Therefore in all I have five cells. I want an input "X" or "x" in one of those five cells. (Only one input, more than one is not allowed) If anyone gives two inputs OR if anyone types anything besides "X" or "x" OR if anyone types anything of length more than one character It should give a validation error box with a message. Can someone give a formula to enter in the validation box? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
shade cells based on conditions - i have more than 3 conditions | Excel Worksheet Functions | |||
Counting based upon 2 conditions that are text based | Excel Discussion (Misc queries) | |||
Sum based on two conditions | Excel Worksheet Functions | |||
Sum based on conditions | Excel Discussion (Misc queries) | |||
Combining conditions for data entry validation | Excel Worksheet Functions |