Dependent Drop Down List

[P_Bennett]

In the attached worksheet, on the "Data Entry" tab, when the user selects the Project (Column A), I need a drop down list to appear that only selects the subcontractors that are relative to that project in Column D (based on the "Subcontracts" tab Column B.)

Then I need for the Subcontractor name from the "Subcontracts" tab to populate column E automatically on the "Data Entry" tab.

For example, if project 660000227 is chosen, column D should have a list of 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15. If I choose number 1 - then R.D. Lambert would be popuulated in Column E.


Thank you!

[tigeravatar]

P_Bennett,

Attached is a modified version of your posted workbook. It contains the following changes:

1. Sorted the Projects table by project.
2. Created a named range "ProjectList" using formula:

   =Table_Query_from_COMPASS_APP[project]

3. Sorted the Subcontracts table by project and then by subcontract.
4. Created a named range 'SubContractList' using formula:

   =Table_Query_from_COMPASS_APP3[project]

5. Used the ProjectList named range as the data validation list for sheet 'Data Entry' cells A6:A13
6. Created a data validation list in sheet 'Data Entry' cells D6:D13 by using this formula:

   =IF($A6="","",OFFSET(SubContractList,MATCH($A6,SubContractList,0)-1,1,COUNTIF(SubContractList,$A6)))

7. Retrieved the Subcontract Name in sheet 'Data Entry' cells E6:E13 by using this formula:

   =IF(OR(A6="",D6=""),"",INDEX(Table_Query_from_COMPASS_APP3[Name],MATCH(1,INDEX((Table_Query_from_COMPASS_APP3[project]=A6)*(Table_Query_from_COMPASS_APP3[subcontract]=D6),),0)))

[rakesh2806]

Not able to open the attachment.

[tigeravatar]

rakesh, the Attachment is a .xlsx file and therefore requires Excel 2007 or higher, or the MS Office Compatibility Pack. You have Excel 2003 listed as your version of MS Office, which is why you cannot open the attachment.

[P_Bennett]

tigeravatar - Thank you so much, it would have taken me a month to figure that out.

[tigeravatar]

You're very welcome