I want to restrict data entry in a column to only accept data in the cell if it contains ****XY*** in this position. For example, this is expected to be a fixed length cell entry of 9 charters but the letters XY must be in the position shown i.e. 5th & 6th characters of the 9 character entry. A warning would be good to tell users when this requirement is not satisfied; and or have these required characters auto filled.
Thanks JET
hi JET2011. select the cells you want the dropdown applied to (say A1:A5). go to Data -> Data Validation -> Allow: Custom -> Formula:
=MID(A1,5,2)="XY"
or if you want a fixed 9 characters too, then:
=AND(MID(A1,5,2)="XY",LEN(A1)=9)
at the same Validation window, you can go to the "Error Alert" tab & show a warning. the "Input Message" tab can be used for instructions to users
Thanks, if you have clicked on the * and added our rep.
If you're satisfied with the answer, click Thread Tools above your first post, select "Mark your thread as Solved".
"Contentment is not the fulfillment of what you want, but the realization of what you already have."
Tips & Tutorials I Compiled | How to Get Quick & Good Answers
Worked perfect! Solved. Thanks
Opps UNSOLVED. Got the formula to work nicely on a single cell but can't get it to work across a range of cells in a column. I even tried enter the formula directly in a cell and drag it down to copy the formatting but no luck.
Here is my formulas I put into the custion validation: It only worked for cell B12
=AND(MID(B12-B20,5,2)="AA",LEN(B12-B20)=9)
=AND(MID(B12:B20,5,2)="AA",LEN(B12:B20)=9)
Solved (again)
I really have it this time, thanks again to Benishiryo. (can't figure out how to PM)
The key for me was to select the area you desired to have validated then enter the custom validation for the first cell only; no need to put the range like I was attempting to do.HTML Code:
This site is the best.
J
Last edited by JET2011; 02-02-2013 at 04:06 PM.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks