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)
| Tasks | Strategies |
| Task1 | ST1 |
| Task2 | ST3 |
| Task3 | ST1 |
| Task4 | ST2 |
| Task5 | ST4 |
Table 2 Matrix (Strategic Theme ST and Objectives OBJ)
| ST1 | ST2 | ST3 | ST4 |
| OBJ1 | OBJ4 | OBJ6 | OBJ7 |
| OBJ2 | OBJ5 | | OBJ8 |
| OBJ3 | | | OBJ9 |
