How to create multiple dependent drop downs that use repeating numbers

[renrevit]

Hello all,

I am trying to create a spreadsheet that has 2 numerical drop downs that provide a response in a third cell. The user selects a value from the first drop down and a value from the second drop down, and the third cell will reflect a value associated with the two selections.

Let me try to explain it with an example..

From the first drop down, the user selects 10, 15, 20, 25, 30, 40, 50, or 100.
From the second drop down, the user selects <1.2, 1.2, 1.5, 2, 2.5, 3, 4, 6, 8, 10 or 12.
The third cell should show a value associated with the combination of selections from the first two. Ex, choosing 10 and 1.2 will show one response, 10 and 1.5 will show another ... etc.

I've written out all of my data and given the values name ranges but realized I can't use data validation on the third cell in relation to the second, because the second list is full of values that are repeated for every number in the first list.

I can't use =IF, AND in the third cell because I would only be able to do that for one combination of numbers.

I'm pretty new to Excel so I'm hoping someone will have an idea for something I can try that I just don't know about yet. Thank you very much in advance!

[maniacb]

One other way to do this is create one table where the first two lines produce all possible combinations, then add a third column with the third value. You can then use the built in Data User Entry Form to select your data.

[ChemistB]

I'm not sure I'm following (okay, I am sure I am not following).
Why do you repeat the same values in your second drop down list? Maybe if you attach a spreadsheet with what you have and what you want to have (short example). See yellow banner for directions

[renrevit]

Hello! Sorry for the confusion. Think of it as a a cross. Values on the x-axis are predetermined, as are values on the y-axis. Depending on the values selected from the x and y axis, there is an associated value. Attachment 682606

The attached photo is the table I am trying to recreate. Ideally, the user would choose one of the areas from column 1 in the table (10, 15, 20, 25, 30, 40, 50, 100 or 100+), then they would select their limiting distance from the next drop down (<1.2, 1.2, 1.5, 2, 2.5, 3, 4, 6, 8, 10, 12, 16, 20 or 25), and the third column would produce the value from the able that corresponds with the two selections.. make sense?

[renrevit]

Hmm.. I opened your example but was unable to figure out how I could make it apply to what I'm doing. I've never toyed with the Data User Entry Form though, so maybe I'll do some research on how that works. Thank you for the response!!

[maniacb]

I updated the workbook to include a second sheet with a pivot of the first table. You can use a table to populate a pivot which lines up data in that up and down and across format you are looking for. Right now the value is in dates but it could be anything.