Dynamically change validation list formula reference based on cell click VBA

[kriscraft]

Hi everyone,

First time poster here, hope you can help!

Im creating a FTE plan which helps people plan their FTE costs based on a rate card on another sheet. On this plan there are 3 drop downs to signify department, role and name of employee if there is one. I have created a formula that successfully updates the list based on the department and the role
=FILTER(ROLE_TABLE[Role Name],ROLE_TABLE[Area]='FTE planning & costs '!B5,"")
=IF(ISBLANK(FILTER(ROLE_TABLE[Resource Name (if filled)],ROLE_TABLE[Role Name]='FTE planning & costs '!C5," "))=TRUE,"",FILTER(ROLE_TABLE[Resource Name (if filled)],ROLE_TABLE[Role Name]='FTE planning & costs '!C5," "))

however as this is an input table i need it to change the reference for the list formula based on the row I have clicked on within the table.

For example on the below, the drop down in column 2 row one points to Column 1 row 1 however if I click on the next cell down in row two then the drop down formula should move to column 1 row 2. Same should happen for column 3 which is dependent on column 2.

Wanted to try and do this on VBA using a click event.