Setting Conditions in a Cell

[Salza]

Hello everyone.

I have limited a cell (i.e F51) to these conditions when users key in
data
in it.

a) All letters are Upper Case
b) Maximum number of letter is 3

Bob Philips has helped me with this formula for that :-
=AND(LEN(F51)=3,NOT(ISNUMBER(--LEFT(F51,1))),NOT(ISNUMBER(--MID
(F51,2,1))),NOT(ISNUMBER(--RIGHT(F51,1))),EXACT(F51,UPPER(F51)))

It works perfectly.

And now, I would like to set one more condition in that cell F51.
In cell F51, the data should not be the same with any data (no
duplication/repetition) in cells B110:B241.

It works with this formula =COUNTIF(B110:B241,F51)=0 if it is set
alone, but can I add this to the formula Bob gave?
=AND(LEN(F51)=3,NOT(ISNUMBER(--LEFT(F51,1))),NOT(ISNUMBER(--MID
(F51,2,1))),NOT(ISNUMBER(--RIGHT(F51,1))),EXACT(F51,UPPER(F51)))

In short, cell F51 should meet this condition
a) All letters are Upper Case
b) Maximum number of letter is 3
c) Data should not be the same as any data in cells B110:B241.

Please help. Thank you.

 

[T. Valko]

That formula will allow non-letter entries. Try entering ???.

One way...

Create this defined name
Goto the menu Insert>Name>Define
Name: Letters
Refers to:

="ABCDEFGHIJKLMNOPQRSTUVWXYZ"

Make sure they're all UPPERCASE letters

OK out

As the data validation formula use:

=AND(COUNTIF(B110:B241,F51)=0,(COUNT(FIND(MID(F51,ROW(INDIRECT("1:"&LEN(F51))),1),Letters))=3)*(LEN(F51)=3))

**Uncheck** Ignore blank

If you test that formula on the worksheet it has to be array entered.

Also note, if cell F51 already contains a valid entry like ABC and you then
make that same entry, ABC, in the range B110:B241 you'll *not* get an
invalid entry message. So, you might want to consider setting validation on
the range B110:B241 at the same time. B110:B241 <> F51.

 

[Salza]

Hi Biff,

Thank you so much. It works the way I want it.
You are really brilliant.

I am working on an exam marksheet using Excel 2007.

* Tq too for a quick reply.

Zainuddin

 

[Per Jessen]

Hi

You can add the new condition into the And statement:

=AND(LEN(F51)=3,NOT(ISNUMBER(--LEFT(F51,1))),NOT(ISNUMBER(--MID
(F51,2,1))),NOT(ISNUMBER(--RIGHT(F51,1))),EXACT(F51,UPPER(F51)),COUNTIF(B110:B241,F51)=0)

Regards,
Per

 

[Salza]

Thank you, Per.
It works. I appreciate your kind help.