Generate Table from Drop-Down list

[scurveydog]

Hello,
I have attached an excel file to help explain what I am trying to do. On Sheet1 I have a drop down list that allows the user to select PartA, PartB, PartC, or PartD. On Sheet2 I have listed the Binders that correspond to each Part (in red). I have also listed the locations for each of the binders (in yellow).

Here is what I would like to have happen. When the user selects a Part on Sheet1, I would like the table (in green) to automatically update so that all the binders and the binder locations update each time the user selects a different Part from the drop down list.
Note: I just put the ‘green table’ in the spreadsheet to help explain what I want to have happen.

Any help would be greatly appreciated.

[Roger Govier]

Hi

The attached workbook has some Dynamic Named ranges

Location =Sheet2!$H$3:INDEX(Sheet2!$I:$I,COUNTA(Sheet2!$H:$H)+1)
Programs =Sheet2!$C$2:INDEX(Sheet2!$F:$F,COUNTA(Sheet2!$C:$C)+1)

Then the following formulae on Sheet1 in cells C4 and D4 and copied down

C4 =INDEX(Programs,ROW(A2),MATCH($A$4,INDEX(Programs,1,0),0))

D4 =INDEX(Location,MATCH($C4,INDEX(Location,,1),0),2)

[scurveydog]

Worked Perfectly! Thank-you very much.