data validation formula

[max_max]

Hello,
I tried the formula for data validation:

= O (ISNUMBER (FIND ($ B $ 1: $ B $ 11; A1))) = FALSE

with * .xls works well.
In excel *.xlsx or .*xlsm does not work perfectly, in each cell is inserted where the data validation functions are inserted to be done:
select cell> validated> o.k. and then it works.
When you restart the workbook does not work and must repeat the operation select> o.k.
Where am I wrong?
I hope my English is understandable.
max_max

[FDibbins]

Try this instead...
=COUNTIFS($J$13:$J$20,C4)>0

DV (and CF_) work only on TRUE/FALSE (1/0) so you just need a formula that returns that

[sandy666]

or

=IFERROR(NOT(SEARCH(C4,J13:J20)),1)

bad idea

[FDibbins]

hahaha Sandy, love that last comment

[max_max]

The fdiddins formula does not work well
The sandy666 formula works well

A change for sandy666:
if it so works (1 character)

*
/
?

if it does not work more than one character:

**
/ PPP
??? YYY

max_max

[sandy666]

@Ford,
Sometimes I am too fast. Shame on me

[sandy666]

These two characters: ? and * are problematic because both represent every character so if I will lock these two I will lock all characters from the keyboard.
But if you don't care about the two above you can use:

=ISNUMBER(SUMPRODUCT(SEARCH(MID(C4,ROW(INDIRECT("1:"&LEN(C4))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

You can add a few characters here "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" like comma or dot... etc.
Check by yourself

[max_max]

O.k. sandy
but why this formula:
=OR(ISNUMBER(FIND($J$13:$J$20,F15)))=FALSE
It works well with *.xls
but not with * .xlsx / *. xlsm
max_max

[FDibbins]

O.k. sandy
but why this formula:
=OR(ISNUMBER(FIND($J$13:$J$20,F15)))=FALSE
It works well with *.xls
but not with * .xlsx / *. xlsm
max_max

I see no reason what that would work in earlier versions of excel but not in later versions.

Also, perhaps you missed where I said

DV (and CF_) work only on TRUE/FALSE (1/0) so you just need a formula that returns that

so you dont need to specify = FALSE, just structure your formula to return that as an answer

[sandy666]

O.k. sandy
but why this formula:
=OR(ISNUMBER(FIND($J$13:$J$20,F15)))=FALSE
It works well with *.xls
but not with * .xlsx / *. xlsm
max_max

Sorry, I don't know because I don't have Ex2003 or earlier (this is like dinosaur )

[FDibbins]

The fdiddins formula does not work well

What doesnt work? explain please

[sandy666]

my formula doesn't work well becuse you can use <, >
and yes, it search first character and block, the rest is ignored

[sandy666]

@Ford,
Don't you think in your post #2 should be =COUNTIF($J$13:$J$20,K13)<1 ?

[FDibbins]

Sandy, no, it is testing for the presence of the character, so it needs to be >0. However, with that last file, I see that the character may be part of a string, so that wont work anyway

[sandy666]

Ok, I tested a few different method but no luck. If I lock ? and * I can't lock < and > ( tilde before <,> doesn't work also)
Thanks for answer

[FDibbins]

Try this instead...
=COUNTIFS($J$13:$J$20,"*"&C4&"*")>0

[max_max]

The new FDibbins formula works with more than one character in the cell
with one character does not work.
max_max

[FDibbins]

hmm can you upload a sample file showing that?

I just tested it on all the characters in your list, and it tested TRUE for each 1.

[max_max]

attached
max_max

[FDibbins]

ooops you wanted to NOT allow those characters, correct?

=COUNTIFS($J$13:$J$20,"*"&C4&"*")=0

[max_max]

Hello fdibbins,
your formula works only with one character
with more than 1 character does not work.

=CONTA.PIŁ.SE($J$13:$J$20;"*"&C4&"*")=0
=COUNTIFS($J$13:$J$20,"*"&C4&"*")=0

max_max

[sandy666]

Ford,
Try with abc< or ab??cd

[JohnTopley]

Try this

=ISNUMBER(SUMPRODUCT(SEARCH(MID(SUBSTITUTE(SUBSTITUTE(C14,"*","|"),"?","|"),ROW(INDIRECT("1:"&LEN(C14))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))

[dmg2016]

Try:

=SUMPRODUCT(LEN(C13)-LEN(SUBSTITUTE(C13,$J$13:$J$20,"")))=0

C13: Data validation cell

DMG

[max_max]

Hello dmg2016,
I think it's right.
Here at my house works well, monday in my office try the new data validation.
Your formula in italian is:

=MATR.SOMMA.PRODOTTO(LUNGHEZZA(C13)-LUNGHEZZA(SOSTITUISCI(C13;$J$13:$J$20;"")))=0

Thanks so much.
Hello everybody.
max_max