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

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

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

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

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