I have been reading through the forums and I have seen a lot of responses issues similar to the one I'm currently trying to solve, but none have enough information for me to know how to modify the provided solutions to fit my situation. A link from the most similar post would be this...

http://www.excelforum.com/excel-prog...here-used.html



The issue:
I'm developing a set of Worksheets used to help calculate Payroll. The current set up is as follows...

General Note:
Every Table or List of values will have Named Ranges

Payroll Information Entry Sheets:
  • Payroll
  • Group Fitness
  • Private Sessions

Visual Data Confirmation Sheets:
  • Breakout (Takes from all sheets)
  • Summary (Pivot Table from Breakout)

Administrative Sheets:
  • Employee List (Takes from Department List and Paygrade List)
  • Department List
  • Paygrade List

To make User Entry and Error Checking more simplistic I began using Data Validation on the Employee List sheet so Administrators could select the Department and Paygrade for each Employee entry from a drop down list. However, I've found that this method results in the selection's value being placed in the cell without reference to the index of that list it was selected from.

This causes a conflict for me as the Department and Paygrade sheets may be sorted, added to, deleted, or their values modified throughout the use of this program. The main problem is if the values of these lists are changed since the new value is not populated to every one of the cells that previously held the old value.

I found some code from the previously linked post:
Please Login or Register  to view this content.
My issue may not be with the code, but my ignorance when it comes to VBA and how to use the code. I've never done anything with VBA before simply due to never being taught it - though I've been researching it more as of late.

I did the following to attempt using VBA for the first time (which i'm sure is wrong)...
  1. Right Clicked on Employee List
  2. Selected View Code
  3. Pasted the modified code (below) into the sheet
  4. Saved the Workbook as .xlsm

Please Login or Register  to view this content.
Note:
The columns D and E are labled Department and Paygrade respectively. This first attempt at the code was just to correct the Paygrade column (E). I'm not sure what I'd have to do to ensure this same functionality would work for both columns either.

I've probably missed a crucial and basic step because the above steps haven't seemed to have done anything for me - that or I don't know how to invoke the code to become active.

I'd really appreciate someone helping me out in either correcting the code or giving me some guidance and tips on how to use VBA more effectively!

Please let me know if there are any questions regarding what I'm attempting to accomplish!

Thank you!