Dynamically update cells depending on content of adjacent cell

[markmcm2021]

Hi Everyone. Hopefully this makes sense, and hopefully someone can help.

In the attached file Sheet1 contains a list of Employees (who are selected by dropdown lists). Depending on which Employee is selected, a number of assigned tasks relevant to that Employee are then made available in dropdown lists in adjacent cells.

On Sheet2 I have worked out how to dynamically update the Type depending on which Employee is listed. What's the best formula to get the Tasks assigned to each employee on Sheet1 to also dynamically update on Sheet2, when cells B2 and B6 are changed accordingly?

Thanks in advance,
Mark.

[Pete_UK]

Use these formulae in the cells stated below:

F3: =IFERROR(INDEX(Sheet1!B:B,MATCH($B$2,Sheet1!$A:$A,0)+ROWS($1:1)-1)&"","")

G3: =IFERROR(INDEX(Sheet1!D:D,MATCH($B$2,Sheet1!$A:$A,0)+ROWS($1:1)-1)&"","")

Copy down into F4:G5.

Similar formulae for the other employee:

F7: =IFERROR(INDEX(Sheet1!B:B,MATCH($B$6,Sheet1!$A:$A,0)+ROWS($1:1)-1)&"","")

G7: =IFERROR(INDEX(Sheet1!D:D,MATCH($B$6,Sheet1!$A:$A,0)+ROWS($1:1)-1)&"","")

Again, copy down into rows 8 and 9.

Hope this helps.

Pete

[markmcm2021]

You're a legend Pete! Much appreciated. Many, many thanks.

[Pete_UK]

Glad to help.

If that takes care of your original question, please take a moment to select Thread Tools from the menu above and to the right of your first post in this thread, and mark the thread as SOLVED.

Also, since you are relatively new to the forum, you might like to know that you can directly thank those who have helped you by clicking on the small "star" icon located in the lower left corner of a post that you have found to be helpful (not just in this thread - for any post that has helped you). This also adds to the reputation of the poster (the small green bars in the poster's profile).

Pete