+ Reply to Thread
Results 1 to 7 of 7

Dynamic data validation

  1. #1
    Registered User
    Join Date
    09-13-2012
    Location
    Utah, USA
    MS-Off Ver
    Excel 2007
    Posts
    35

    Dynamic data validation

    Im looking for a way to setup a data validation where the user has to enter a number within a dynamic range based on a value set by a number in a different cell
    So if A1 = 314 I need to make sure the user can only enter values between 4001 and 4365
    If A1 is changed to 317 the user has to enter a number between 7001 and 7365

    I can easily capture the 3rd digit (4 or 7 in my example) with a RIGHT statement but I need to apply that to the max and min values allowed in the data entry cell.

    Any help would be REALLY appreciated

  2. #2
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,177

    Re: Dynamic data validation

    Where did 7001 and 7365 come from to test the range?

    Workbook_SheetChange event

  3. #3
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,033

    Re: Dynamic data validation

    Not too difficult.
    Data validation: whole number, between, minimum:

    =--(RIGHT(A2,1)&"001")

    and for the maximum
    =--(RIGHT(A2,1)&"365")
    Glenn




    None of us get paid for helping you... we do this for fun. So DON'T FORGET to say "Thank You" to all who have freely given some of their time to help YOU.

    Temporary addition of accented to illustrate ongoing problem to the TT: L? fh?ile P?draig sona dhaoibh

  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,033

    Re: Dynamic data validation

    Here's the spreadsheet to show you...
    Attached Files Attached Files

  5. #5
    Valued Forum Contributor ranman256's Avatar
    Join Date
    07-29-2012
    Location
    Kentucky
    MS-Off Ver
    Excel 2003
    Posts
    1,177

    Re: Dynamic data validation

    and you want to see if 317 is between 7001 and 7365?

  6. #6
    Registered User
    Join Date
    09-13-2012
    Location
    Utah, USA
    MS-Off Ver
    Excel 2007
    Posts
    35

    Re: Dynamic data validation

    Thank you Glen Kennedy your solution worked perfectly! I had something similar in my original attempt but yours gives me exactly what I was looking for

  7. #7
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    Digital Nomad... occasionally based in Ireland.
    MS-Off Ver
    O365 (PC) V 2403
    Posts
    44,033

    Re: Dynamic data validation

    Hi, you're welcome. If that's everything done, can tyou now mark the thread as solved?

+ 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. Dynamic Data Validation in VBA
    By dchanson in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-18-2012, 04:45 PM
  2. Dynamic Data Validation
    By Badvgood in forum Excel General
    Replies: 3
    Last Post: 12-19-2011, 07:44 PM
  3. Dynamic Data Validation
    By SystemsAccountant in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 11-20-2008, 08:11 AM
  4. Dynamic Data Validation
    By taleb in forum Excel General
    Replies: 1
    Last Post: 09-13-2008, 08:08 AM
  5. [SOLVED] Dynamic Data Validation in VBA. I s it possible?
    By Ayo in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 04-18-2006, 04:55 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