+ Reply to Thread
Results 1 to 11 of 11

Custom data validation

  1. #1
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Custom data validation

    Hi,

    We've got a spreadsheet in Excel that we use as a time sheet. It gets imported into our Access database monthly so it's really important the data is in the correct format.

    We've got what's known as a "Client Code" which consists of a "Client Number" (a number assigned to the client) and an "Episode Number" (a number assigned to how many times they've referred into the service). The format that needs to be kept on the time sheet is ####:# e.g. 1234:1 is the 1234th client and the first time they've used the service.

    I've currently got the formula "=AND(LEN(E5)=6,ISNUMBER(--MID(E5,1,1)),ISNUMBER(--MID(E5,2,1)),ISNUMBER(--MID(E5,3,1)),ISNUMBER(--MID(E5,4,1)),MID(E5,5,1)=":",ISNUMBER(--MID(E5,6,1)))" which seems like a bit of a complicated way of achieving what I want - but it has done the job.

    The one unfortunate issue with this formula is that a 3-digit client number requires a preceding '0' so client 123 must be entered as '0123'. This hasn't been a huge issue as we rarely see clients from that long ago still, HOWEVER - we're about to hit 10000 clients which means that my current formula won't work and if I just extend it to effective accept #####:# it will mean that all clients with four digits will require the preceding '0'. I'm sure that'll get annoying quite quickly and it's not particularly elegant.


    TL:DR - I'd like to find a solution that allows me to force our staff to input the client code in the format #####:# without forcing a certain number of characters before (or, preferably, after) the colon.

    Thanks in advance.
    Attached Files Attached Files
    Last edited by ticplusdan; 01-23-2020 at 11:27 AM.

  2. #2
    Forum Contributor
    Join Date
    11-10-2017
    Location
    INDIA
    MS-Off Ver
    365
    Posts
    184

    Re: Custom data validation

    Congratulations for your growing number, can you enclose a sample file to work on it.

    Check the yellow banner above.
    HOW TO ATTACH YOUR SAMPLE WORKBOOK:
    *If you wish you click on *,a way to say ThankYou

  3. #3
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Re: Custom data validation

    Have attached now.

  4. #4
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Custom data validation

    May be so

    =IFERROR(1/(TEXT((--LEFT(E5;FIND(":",E5)-1)),"00000")=LEFT(E5;FIND(":",E5)-1))/(LEN(E5)=7),)
    also

    =SUBSTITUTE(TEXT(--SUBSTITUTE(E5,":",MID(1/2,2,1)),"00000"&MID(1/2,2,1)&"0");MID(1/2;2;1),":")=E5 , MID(1/2,2,1) - it's decimal separator dependent on regional settings and could be , or .

    =SUBSTITUTE(TEXT(--SUBSTITUTE(E5,":","."),"00000.0");".",":")=E5 or =SUBSTITUTE(TEXT(--SUBSTITUTE(E5,":",","),"00000,0");",",":")=E5
    Last edited by BMV; 01-24-2020 at 05:22 AM.

  5. #5
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Re: Custom data validation

    Thanks for the reply, but all of the above throw up errors when used in the formula box for data validation

  6. #6
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Custom data validation

    M-N column only for example
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Re: Custom data validation

    Sorry for taking so long to look at this, replies much appreciated.

    The issue with this solution seems to be that it will allow 5 digit client numbers, but not anything less e.g. 1234:5 or 123:1 etc. I was hoping it would be possible to allow for [5 or less digits:2 or less digits] not just [5 digits:1 digit].

    Hope that makes sense.

  8. #8
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Custom data validation

    Please Login or Register  to view this content.
    the examples are entered before datavalidation setup.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Re: Custom data validation

    Thanks so much - this looks like it should do the job. I'll try to get it into a live version.

  10. #10
    Forum Expert BMV's Avatar
    Join Date
    01-13-2020
    Location
    St-Petersburg, Russia
    MS-Off Ver
    2013-2016
    Posts
    1,329

    Re: Custom data validation

    I found combination 012345:12 that could be accepted
    add extra condition *(LEFT(E5)<>"0")
    Attached Files Attached Files

  11. #11
    Registered User
    Join Date
    01-23-2020
    Location
    Gloucestershire, UK
    MS-Off Ver
    2016 Professional Plus
    Posts
    6

    Re: Custom data validation

    The database will effectively "lose" any preceding 0's on the import (as it does currently for 3 digit client no's) - so I wouldn't expect that to be an issue.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Custom data validation
    By Travman1986 in forum Excel General
    Replies: 3
    Last Post: 01-27-2017, 09:59 AM
  2. Replies: 0
    Last Post: 11-20-2015, 12:34 PM
  3. Custom data validation
    By EdwardStephenson in forum Excel General
    Replies: 7
    Last Post: 04-10-2014, 10:04 AM
  4. Custom data validation
    By superboy in forum Excel General
    Replies: 0
    Last Post: 01-20-2014, 12:05 PM
  5. Custom Data Validation
    By penfold1992 in forum Excel General
    Replies: 0
    Last Post: 05-15-2013, 05:45 AM
  6. Custom Data Validation
    By Grilleman in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 08-25-2012, 11:52 AM
  7. Custom Data Validation
    By maacmaac in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-11-2009, 03:40 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1