Data validation text length based on value in another cell
Hello,
I'm trying to set up one cell to be required to be 12 digits long if the cell to the left is equal to "S". How do I accomplish this using data validation. I tried this formula and thought it was working but it it was restricting the value in E7 no matter what value was in D7: =IF(D7="S", LEN(E7)=12). The point is to disallow incorrect ticket numbers in column E when column D designates "S" as the hauler. Thanks!
Like many will point out, a sample file is best. See the yellow banner above.
But if I am reading this right... you just need a handler for the FALSE as in When it does NOT equal S...
Now, I am just making it be anything at this point... but something like this should step you through to the next option
=IF(D7="S", LEN(E7)=12,LEN(E7)>0)
-If you think you are done, Start over - ELeGault
To take it a step further... I dont know what you are doing with your data... but I would setup a table of constraints as I assume there is more than just an S class ticket?
Setup a table... lets call it Ticket_Constraints but you can call it what you wish.
Column 1 Being TicketType
Column 2 Being Length
From here we can setup a valid/invalid check in a cell next to the entry location. You said you are on E7 so lets say you enter it in on F7...
What we will do now is make it lookup the Length based on the Ticket Type you enter into D7.
F7
You could change the 2 Reference to another Match in which you determine which column has Length, but if there are no other values a 2 will suffice
Please Login or Register to view this content.
Now the Data Validation can look at F7 but validation is applied to E7
Data Validation is
Really hard to give good direction without knowing the file... but if it was me I would have a visual flag for the user along with a Data Validation to hard stop. Doing lookups in validation lists is a no no so embedding it in the sheet is a good move. If this is the ONLY place you enter the ticket number/ID then you can hide that valid/invalid and make it a 1/0, True/False...etc. to validate against... all optional but really comes down to you and your design!Please Login or Register to view this content.
I apologize for the lack of an example sheet but yes, that is exactly what I needed and it works perfectly now. Thank you very much for the help!
I appreciate the time you took to do all that but EleGault solved my issue by just added the last bit onto my formula that I had started. Thanks again
Heh, was me in both posts
Glad that was good enough for what you needed!
Be sure to mark the thread solved under the thread tools up there
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks