Custom Data Validation

[bandit_1981]

Hello all I am currently using this custom validation.

=AND(LEN(D6)<=8,OR(RIGHT((D6),1)="%",MOD(D6,1)=0))

Now i need to update it to also allow for "." so they can have decimals in their percentage. Any Ideas. I tried putting

=AND(LEN(D6)<=8,OR(RIGHT((D6),1)="%",OR val(FIND(D6,".")) > 0 ,MOD(D6,1)=0))

But this doesnt work

 

[NBVC]

Try this:

=AND(LEN(D6)<=8,OR(RIGHT((D6),1)="%",MOD(D6,1)>=0))

 

[bandit_1981]

It works but if i type say 6.5% in the cell it whipes it out for some reason. It doesnt give an error it just whipes the cell.

 

[bandit_1981]

It appears as if you use text for the format it wont supoort both the "." and the "%" sign in the same cell. General will but it auto converts it to percentage format which wont work for what i need. hmm....

 

[NBVC]

Hmmm.

I tried the exact same validation and used your example and it works just fine.

If I remember correctly, I helped you on the initial validation a while back and I think your cell format must be Text to work properly. Is that what you have?

Even if it's not in text, which it should be, I can't see any reason for it to wipe out your entry and not give any error if your data validation is set. Do you have some sort of macro in the sheet that may be invoked and is clearing certain entries?

Perhaps try in the validation in another cell or other sheet just to see.

Let me know.

 

[bandit_1981]

Yeah you did help me on the orginal one as well. I have my format set to text. I just tried it on a blank sheet and it works it must be messing with one of my formula and it is getting cleared out by it.

Thanks again
Dan