How to show values less than or equal to another value in drop down list, data validation

Hello,

I have a problem where I want to show in a drop down list only the names that refer to values that are less than or equal to a separate value. I would be able to do this using data validation but I'm not sure how to go about it.

I have included a spreadsheet with the number of marbles and four bags with different capacities. In the drop down list, I would like to only show the number of the bags that the marbles will fit in. For example, if there were 200 marbles then the drop down list would only show 2 and 3 as the capacity of bag 2 and 3 are larger than the number of marbles.

Thank you for your help in advance!

emily31

Thank you Ace_XL for your help but I'm going to go with using martindwilson's method.


Is there anyway I can change the bag numbers to a name? I'm trying to avoid having to sort the capacities.

Thank you very much!

emily31

Thank you so much for your help! I've managed to use this example to help me with another sheet but is there any way I can generalise the formulae so that I have the data validation/drop down list for 100 different rows, for example, a 100 different values for the number of marbles?

Thank you,

emily31

Also, if two bags have the same capacity then only the name of the first bag found is shown twice as opposed two different names, for example, in the spreadsheet below, 'small bag' and 'another small bag' have the same capacity so 'small bag' is shown twice.