Hi,
I am trying to create a simple solution to allow the contents of a drop-down data validation list (within a table) be determined by the value of a cell 2 columns to the left of the drop-down cell; a Name -> Job relationship.
Workbook after the link > https://www.dropbox.com/sh/si7t810zjfqm6xl/AADKG0lmZ7kjFrq6pdTohFATa?dl=0
The worksheet Emp-Pay contains a table which has a columns called Name and Job. The Job column uses a data validation list the contents of which are from the named range dddlstJob.
The use case is as follows; user enters name in the Name field and then selects a value from the relevant Jobs in the Job column. The relationship between Name and Job is defined in a second table Emp-Job.
I currently have a VBA worksheet change action causing cell U1 to equal the value of the current cell and drive the ddlstJob formula.
Problem; when the user goes to select the Job data list is moves the active cell from the Name field and messes up the ddlstJob list making the list empty.
I'd really appreciate any help with this, thanks all.
John
I am trying to create a simple solution to allow the contents of a drop-down data validation list (within a table) be determined by the value of a cell 2 columns to the left of the drop-down cell; a Name -> Job relationship.
Workbook after the link > https://www.dropbox.com/sh/si7t810zjfqm6xl/AADKG0lmZ7kjFrq6pdTohFATa?dl=0
The worksheet Emp-Pay contains a table which has a columns called Name and Job. The Job column uses a data validation list the contents of which are from the named range dddlstJob.
The use case is as follows; user enters name in the Name field and then selects a value from the relevant Jobs in the Job column. The relationship between Name and Job is defined in a second table Emp-Job.
I currently have a VBA worksheet change action causing cell U1 to equal the value of the current cell and drive the ddlstJob formula.
Problem; when the user goes to select the Job data list is moves the active cell from the Name field and messes up the ddlstJob list making the list empty.
I'd really appreciate any help with this, thanks all.
John
