Want use results of Drop down list to look up a value

[Rob Van Pelt]

I am using Excel 2002 and I have a multisheet workbook and on the first sheet
I have created a series of 6 drop down lists pulling from predefined named
ranges. I also have limited subsequent choices based on what is selected in
the other lists. The end result is the user will be selecting parameters that
identify a product and now I am stuck with an easy way to look up the
associated part number based on the parameters they selected. Essentially,
once they have chosen the parameters in the list, I would like to create a
macro that uses those values to locate the associated part number.

I would like to know the best way to set up the database such that the macro
will use parameter A, B, C, D, E, and F to then go look up the part number
that satisfies those 6 parameters. Then, I would also like to know the best
way to write a macro to do so.

Thank you.

 

[Luke M]

Assuming your table is in rows 1 to 1000, a rough macro would look like
this:

Sub FindMe()
A = Parameter1 'Define these properly
B = Parameter2
C = Parameter3
D = Parameter4
E = Parameter5

For i = 1 To 1000
If Cells(i, "A") = A And _
Cells(i, "B") = B And _
Cells(i, "C") = C And _
Cells(i, "D") = D And _
Cells(i, "E") = E Then

'Where do you want output?
Range("G1") = Cells(i, "F").Value
Exit For
End If
Next
End Sub

 

[Rob Van Pelt]

I think I understand some of what you are describing but I let me clarify. If
I understand what you are saying, I would use column F to define the part
number associated with the combination of parameters in columns A thru E. So
if each parameter had 5 possible values, I would need to use rows 1 to 3125
to cover every possible combination, correct? (5 to the 5th power). Rows 1
thru 625 would all have identical A parameters, rows 1 thru 125 would also
all have identical B parameters, rows 1 thru 25 would also all have identical
D parameters, and rows 1 thru 5 would be each unique E parameter and then
that pattern would repeat 5 more times, correct?

I think that helps, just a lot of data entry to get it set up.

Thanks!

 

[Rob Van Pelt]

I have done as Luke M suggested but and have assigned the macro to a Command
Button. I am not getting the value to display in the target cell. Any
suggestion about that?