Looking for suggestions on how to re-work this file:

[mdkline]

I currently use excel to track available sick and vacation time balances for employees. The basic information has been kept on a summary sheet in the workbook. I then have 52 weekly "reports" (work sheets) that track labor hours by type for individual employees per week. When an employee uses sick/vacation time in a given week this is automatically deducted from the time they available to them in the summary sheet. The following week then references this new balance for the new report. Conditional formatting shows when an employee has used up or is out of sick time.

Here is where I am stuck. With this file I am unable to sort employees' names. When I get a new hire I just have to add them at the bottom of the list. I would like to sort alphabetically, but because each weekly report's employee list is just a reference to the master I can't. If I sort the master worksheet, then the balances will be associated with the incorrect employee name.

I tried using a pivot table to create alphabetized lists of employees, but I was unable to use vlookup where the Lookup value is in a pivot table. If this had worked I could have had the names in any order on the summary page and alphabetized in weekly reports (one messy page is better than every work sheet). a series of vlookup formulas would have matched the weekly entries to the name on the master sheet.

Any suggestions would be appreciated. I am trying to keep the formatting and necessary data entry consistent (others do the data entry and making it more complicated defeats the point).

I'm not highly knowledgeable with Macros & VBA, but I want to practice and learn. If there is an option that would be helpful please clue me in.

Thank you for looking and any input.


Edit: A two month sample of the file can be found here.