Dynamically change validation list formula reference based on cell click VBA

kriscraft

New Member
Joined
May 6, 2021
Messages
1
Office Version
  1. 365
Platform
  1. MacOS
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.

Screenshot 2021-05-06 at 15.53.08.png
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

Forum statistics

Threads
1,141,068
Messages
5,704,088
Members
421,327
Latest member
Msh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top