+ Reply to Thread
Results 1 to 5 of 5

How can I limit the max int value of 2nd drop down box based on int value of first?

  1. #1
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    45

    Question How can I limit the max int value of 2nd drop down box based on int value of first?

    I have a small and what feels like it should be a simple issue but so far with various internet searching I simply can't figure out how to solve my problem.

    I need whichever values in both dropdown boxes to equal 100 when summed together. The value of the first should limit what I can use in the second.

    I've got a cell with a dropdown box which refers to a named range that has numbers from 0-100.

    The ideal functionality would work like this:

    If I chose 50 in the first dropdown box, the maximum value I can choose in the second is 50.

    If I chose 10 in the first dropdown box, the maximum value I can choose in the second is 90.

    If I choose 100 in the first one, the max value I can choose is 0 in the second dropdown box.

    I 've seen various ways to make dependent dropdown boxes with text lists but I can't figure out how to make this work as described above based on a sum of both.

  2. #2
    Forum Contributor shank_mis's Avatar
    Join Date
    09-08-2018
    Location
    Delhi
    MS-Off Ver
    2010
    Posts
    128

    Re: How can I limit the max int value of 2nd drop down box based on int value of first?

    Hi,

    Please find the attached sheet here. Hope this is in line with your requirement.
    Next time onwards, try uploading a sheet for your problem.

    Please hit "Add Reputation" Button, if you liked the answer.
    Attached Files Attached Files
    Shashank Mishra
    Please hit "Add Reputation" Button if you liked the answer.

  3. #3
    Forum Expert bebo021999's Avatar
    Join Date
    07-22-2011
    Location
    Vietnam
    MS-Off Ver
    Excel 2016
    Posts
    8,460

    Re: How can I limit the max int value of 2nd drop down box based on int value of first?

    I offer 2 options: one is manual input, other is dropdown box

    Data Validation open:

    OPTION 1
    Input 1: G5
    Allow: Custom
    Formula: =ISNUMBER(MATCH(G5,ROW($1:$100),0))

    Input 2: G8
    Allow: Custom
    Formula: =G8<=100-G5

    OPTION 2
    Creat name1=Sheet1!$A$2:$A$101
    With Sheet1!A2:A101 contains 1 to 100
    name2: =OFFSET(Name1,,,100-Sheet1!$K$5)

    Data Validation open:

    Input 1: K5
    Allow: List
    Source: =Name1

    Input 2: K8
    Allow: List
    Source: =Name2
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    08-04-2010
    Location
    United States
    MS-Off Ver
    Office 365 for windows
    Posts
    45

    Re: How can I limit the max int value of 2nd drop down box based on int value of first?

    Thank you guys! I was not able to understand this reading it on the web but from the examples that work here I can see what is happening and I am sure I can get it to work in my spreadsheet thank you again. :D

  5. #5
    Registered User
    Join Date
    05-26-2021
    Location
    Washington DC
    MS-Off Ver
    Microsoft Excel for Microsoft 365 - 2008
    Posts
    13

    Re: How can I limit the max int value of 2nd drop down box based on int value of first?

    Hi, I really like your drop example and 2 options here. However, I am struggling to understand how you created "name2" with the offset formula - I don't see this in the drop down name box. I am attempting to use what you provided, but I also want to add additional "Inputs" and those drop down quantities will continue to reference the prior selection and limit of 100. In other words, is there a dynamic way to add more inputs that also have a drop down that all adhere to the qty 100 rule? My example is actually 40 total, but I was hoping there's a way to duplicate the formula so I can add more inputs and still have it know 40 is my max amount until all is used.

    Thanks

+ 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. drop down list share with limit selection
    By oriazria in forum Excel General
    Replies: 0
    Last Post: 11-22-2020, 05:14 PM
  2. [SOLVED] Limit the number of uses of the contents of a drop down box
    By mlafrance in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 07-27-2015, 01:59 PM
  3. [SOLVED] How to limit the selection in a drop down
    By JennOlsen in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 12-30-2013, 04:35 PM
  4. [SOLVED] drop down menu data limit
    By torbee in forum Excel General
    Replies: 2
    Last Post: 05-22-2012, 12:11 PM
  5. Using one set of Drop downs to limit another
    By SkyDesign in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 06-01-2010, 04:56 PM
  6. How can I get around the DV drop down list limit
    By concretetsunami in forum Excel General
    Replies: 4
    Last Post: 08-07-2009, 05:43 PM
  7. Limit on filter drop-down list?
    By koltregaskes in forum Excel General
    Replies: 1
    Last Post: 11-21-2005, 07:40 AM

Tags for this Thread

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