+ 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,016

    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
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    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



  4. #4
    Forum Moderator Glenn Kennedy's Avatar
    Join Date
    07-08-2012
    Location
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    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,016

    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
    It changes constantly, but based in Ireland.
    MS-Off Ver
    Excel 2013 & 2016
    Posts
    25,319

    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. 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