I am trying to optimize the spreadsheet files we use for our timesheets and for tracking hours booked to a project. Currently we have a timesheet file that has the following functions:

1. Entering a project number in a specific cell will infill the sheet header with project information from a separate file which is a job list containing the information.
2. Once the project number is entered, the body of the file contains two years of dates and formulas that use the project number to search each employees separate timesheet files and inputs the hours booked to the project in the appropriate date range for that employee.
3. The file uses billing rate values in the sheet and more formulas to multiply the number of hours booked by each employee against their billing rate and come up with a total dollar value billed to the project for each two week period.
4. The sheet tracks the ongoing cumulative total billed to the project as well as expenses and invoices that are manually entered.
5. In order for the ledger file to work, the timesheet files need to be created ahead of time for the coming year.

We have had a few issues with the files as follows:

1. The file contains merged cells that are used to display the hours booked to a project and for some reason the formulas work for the first 10 or so rows, but then they display a value error.
2. They do not offer much in the way of flexibility in terms of being able to add or remove employees.
3. They require a significant amount of work to update each year to link to new timesheet files for each year.

This is what I am hoping to accomplish.
1. Come up with a better way of importing data from timesheet files that would eliminate the hundreds of formulas in the cells that search the separate timesheet files. Ideally this would be done using a macro.
2. Come up with a way to have the date range start on the date the project is started (this information is imported from the job list file using a formula) and end two years later, regardless of when the project is started.
3. Improve the flexibility of adding and deleting staff members and automatically linking their associated timesheets.

What I am thinking is there must be a way to create a macro that would search the timesheet associated with the name the column and the date range in the row and imput the hours in the corresponding cell based on the project number that is entered in the sheet.

HELP!!!