Hi,
I'm trying to get a validation on cells A1:A10 where you can only put a 4 digit number + a letter that is determined by the mod(4digitnumberinput;5)+1 where depending on value: F=1;N=2;R=3;T=4;X=5. What is/are the formulas i can use to get this result.
Much appreciated,
do you want to limit the valid entries by putting a number somewhere in the sheet to say what the fifth character should be? If so, name cell B1 MOD.
Enter this formula in cell B2: =VALUE(LEFT(A1,4))&VLOOKUP(MOD,C1:D5,2,FALSE)=A1
In C1:C5 enter 1 2 3 4 5
In D1 through D5 enter F N R T X
In A1, enter this custom validation formula: =B2=TRUE
If your question has been satisfactorily addressed, please consider marking it solved. Click the Thread Tools dropdown and select Mark thread as solved.
Also, you might want to add to the user's reputation by clicking the star icon in the lower left corner of the post with the answer- it's why we do what we do...
Thomas Lafferty
Analyst/Programmer
What i'm trying to do is a data validation where the customer can only put a four digit number and the output comes out with the letter determined by the 4 numbers mod, in A1.
Validation with a formula like =RIGHT(A1,1)=CHOOSE(MOD(LEFT(A1,4),5)+1,"F","N","R","T","X") should do it.
_
...How to Cross-post politely...
..Wrap code by selecting the code and clicking the # or read this. Thank you.
Thanks a million! It was what i was looking for!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks