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
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
1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
2. If your question is resolved, mark it SOLVED using the thread tools
3. Click on the star if you think someone helped you
Regards
Ford
orFormula:
Please Login or Register to view this content.
bad idea
Last edited by sandy666; 02-15-2017 at 03:08 PM.
hahaha Sandy, love that last comment
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
my formula doesn't work well becuse you can use <, >
and yes, it search first character and block, the rest is ignored
@Ford,
Sometimes I am too fast. Shame on me
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:You can add a few characters here "0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ" like comma or dot... etc.Formula:Please Login or Register to view this content.
Check by yourself
Last edited by sandy666; 02-15-2017 at 03:58 PM.
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
What doesnt work? explain pleaseOriginally Posted by 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
so you dont need to specify = FALSE, just structure your formula to return that as an answerDV (and CF_) work only on TRUE/FALSE (1/0) so you just need a formula that returns that
Sorry, I don't know because I don't have Ex2003 or earlier (this is like dinosaur
the fdibbins formula does not work
the sandy66 formula works only with one character
my formula works only with * .xls
Hard to fix something when you are not told what it is doing wrong.
"doesnt work" tells me nothing. Is it giving wring result? no result? something else?
explain
the formula does not read all the characters in J13:J20
Use formula from #8 It will work with first or any next places, like abc\ or qazx< except like I said before: * and ?
Did you read #8 post?
I have no idea for more. You can look for VBA solution.
Last edited by sandy666; 02-15-2017 at 04:41 PM.
@Ford,
Don't you think in your post #2 should be =COUNTIF($J$13:$J$20,K13)<1 ?
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
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
Try this instead...
=COUNTIFS($J$13:$J$20,"*"&C4&"*")>0
The new FDibbins formula works with more than one character in the cell
with one character does not work.
max_max
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.
attached
max_max
ooops you wanted to NOT allow those characters, correct?
=COUNTIFS($J$13:$J$20,"*"&C4&"*")=0
Ford,
Try with abc< or ab??cd
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
Try this
=ISNUMBER(SUMPRODUCT(SEARCH(MID(SUBSTITUTE(SUBSTITUTE(C14,"*","|"),"?","|"),ROW(INDIRECT("1:"&LEN(C14))),1),"0123456789abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ")))
Try:
C13: Data validation cell
Please Login or Register to view this content.
DMG
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks