I'm creating a Level of Effort Calculator. I already have some complex VBA macros that help add or delete specific data in the various tables across the worksheets in my workbook, so if VBA is required, that's no big deal.
The summary of it all is that in table1, I have a dynamic Data Validation Dropdown that allows the user to select a labor category name that is defined in table2. Table2 also has a column with each Labor Category's rate. I then have a second column in table1 that uses a VLookup to grab the selected Labor Category's rate from table2. What I would like column 3 in table1 to do is lookup in table3 the names of all engineers whose rate is less then or equal to the selected labor Category's rate.
Table3 has 3 columns. The first column has the names of our engineers. Column 2 has each engineer's hourly cost to the company and the last column has their fully burdened rate. The goal with column 3 in Table1 is to limit the user's ability to select an engineer for the job whose fully burdened rate is greater than the selected Labor Category's rate. Basically, I'd like column 3 to be a filtered data validation Dropdown.
Not sure if this is possible to do with a custom validation formula and some complex combination of MATCH and IF statements or if I will need to use some VBA code to filter the validation list. If the latter is required, is it possible to run the macro upon changing the labor category or would I have to attach the macro to a button to update the available engineers?
The summary of it all is that in table1, I have a dynamic Data Validation Dropdown that allows the user to select a labor category name that is defined in table2. Table2 also has a column with each Labor Category's rate. I then have a second column in table1 that uses a VLookup to grab the selected Labor Category's rate from table2. What I would like column 3 in table1 to do is lookup in table3 the names of all engineers whose rate is less then or equal to the selected labor Category's rate.
Table3 has 3 columns. The first column has the names of our engineers. Column 2 has each engineer's hourly cost to the company and the last column has their fully burdened rate. The goal with column 3 in Table1 is to limit the user's ability to select an engineer for the job whose fully burdened rate is greater than the selected Labor Category's rate. Basically, I'd like column 3 to be a filtered data validation Dropdown.
Not sure if this is possible to do with a custom validation formula and some complex combination of MATCH and IF statements or if I will need to use some VBA code to filter the validation list. If the latter is required, is it possible to run the macro upon changing the labor category or would I have to attach the macro to a button to update the available engineers?
