Validation based on three conditions

[gojakie]

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?

 

[John C]

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.

 

[vezerid]

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

 

[John C]

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.

 

[ShaneDevenshire]

Hi,

And in fact pressing Shift+Ctrl+Enter has no effect whatsoever.

 

[vezerid]

Thanks for the tip John. You learn a new thing every day...

 

[John C]

you do? Heck, I don't, I think my brain is losing something every day