Drop down list in a table based on multiple other columns

[Nicole Jones]

I have a data table with multiple columns, I would like the dropdown list in my spreadsheet to return different values depending on what is entered into other columns within the row. So every cell C# dropdown will be different

Eg if cell A5 has "PM" and cell B5 has "REP" then the following values would appear in the cell C5 dropdown

or if cell A6 has "EE" and cell B6 has "CAL" then the following values would appear in the cell C6 dropdown

Any assistance would be appreciated.

Thank you
Nicole

 

[Jeffrey Mahoney]

If you have Office 365 then this will be much easier. Please change your profile. I really like helping when I can copy the data you have above so I don't have to recreate it on my end
https://www.mrexcel.com/board/excel-articles/xl2bb-excel-range-to-bbcode.1/download

Assuming you have a full list with discipline code, document type, and description: Make it a structured table. Now create a formula like in the table below. Then add your Data Validation like I did.

The cells in column G and to the right would have to be put way out of your data set and need to be on the same row as your data validation

Book2
	A	B	C	D	E	F	G	H
1	Disc Code	Doc Type	Description
2	PM	REP					Progress Reports	Design Report
3	EE	CAL					Fault Current Calculation Report	Maximum Demand Calculation
4	EE	CAL					Fault Current Calculation Report	Maximum Demand Calculation
5	EE	CAL					Fault Current Calculation Report	Maximum Demand Calculation
6
Sheet2

Cell Formulas
Range	Formula
G2:H5	G2	=TRANSPOSE(FILTER(DeliverablesTbl[Description of Deliverable],(DeliverablesTbl[Discipline_Code]=A2)*(DeliverablesTbl[Document_Type]=B2),""))
Dynamic array formulas.

Cells with Data Validation
Cell	Allow	Criteria
C2:C5	List	=G2#

Book2
	A	B	C
1	Discipline_Code	Document_Type	Description of Deliverable
2	PM	REP	Progress Reports
3	PM	REP	Design Report
4	EE	CAL	Fault Current Calculation Report
5	EE	CAL	Maximum Demand Calculation
Sheet1