Excel Formula to auto generate a drop down list based on an Xlookup formula from another cell

Question

I am a supervisor, I have 4 employees that I am managing. I have an excel workbook with several sheets with pre-defined tables. Sheet1 contains a list of 10 Tasks with 4 Strategic Themes. Each task is assigned a strategic theme. Sheet2 contains a Matrix Table, Strategic Themes in reference cell A1:E1 for the 4 strategic themes and underneath each strategic theme I have list of Objectives and they are in reference cell A2:E6. My expectation is for all my employees to go into sheet3 and fill it out accordingly. In sheet3, initially at the start will only have the following headers with all rows empty. Column A will be "Task", Column B will be Strategic Theme, and Column C will be Objectives. Employee 1 will go into the worksheet (sheet3) and click in reference cell A2 and a dropdown list of all the tasks will appear. Employee will select a Task and automatically Column B will auto populate because in Table 1 All tasks have been assigned a Strategic theme. So there is nothing expected for the employee to do in Column B. Employee 1 will proceed to Column C which will now have a dropdown list based on the strategic theme that was auto populated in column B and Table 2 where each Strategic Theme is assigned a list of Objectives. The expectation is for the employee to select an objective from that dropdown list. That completes the mission for employee 1. Then whenever employee 2 is available they will go into the same workbook and continue on the next row down and perform the same thing employee 1 did. A macro or excel calculation will work for me.

Note: I have generated the drop down list for column A "Task" and used XLookup to auto populate Column B "Strategic Theme" but my struggle is with Column C "Objective". It seems like the Data Validate doesn't like a reference formula inside another formula i.e., the Xlookup formula in Column B

Table 1 (sample with 5 tasks and Strategic Theme ST)

Task	Strategic Theme
Task1	ST1
Task2	ST2
Task3	ST3
Task4	ST4
Task5	ST1

Table 2 Matrix (Strategic Theme ST and Objectives OBJ)

ST1	ST2	ST3	ST4
OBJ1	OBJ4	OB6	OBJ7
OBJ2	OBJ5		OBJ8
OBJ3

Employees Sheet

Task	Strategic Theme	Objective
Here will be dropdown list of all 10 tasks	Auto Populate based on Table1	Drop down list will be generated based on the auto-populated Strategic Theme and table2

Many thanks, either Macro or Formula but the most efficient and robust solution....... many thanks.

Note: I have generated the drop down list for column A "Task" and used XLookup to auto populate Column B "Strategic Theme" but my struggle is with Column C "Objective".

Answer 1

Hi,

It seems you want to create a dependent dropdown list, I suggest you refer to this support article and try to create the dependent dropdown list:

https://exceljet.net/articles/dependent-dropdown-lists

Note: Microsoft is providing this information as a convenience to you. The sites are not controlled by Microsoft. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link.

Just checking in to see if the information was helpful. Please let us know if you would like further assistance.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

thanks for the reply @Emi Zhang-MSFT but no it is not just a dependent drop down list as described in elaborate details in my message. I can perform and execute a dependent drop down list with ease. The situation is a dependent drop down list that is depending on another calculated column. Please read my message in its entirety for clear understanding.