Dynamic linking of Worksheets

[Thatguy99]

I know this might be asking a bit too much on a forum (I'm not sure) but I am trying to figure a way of saving my time on a daily basis.

I have 10 sales employees in a company. Each employee has their own worksheet which shows data about their personal sales. It shows client name, finishing date of the contract, profit due for the contract, profit margin and profit per hour etc. Each spreadsheet is unique in that the first column has the initials of the employee in each row that has sales data. At the bottom of the rows there is a summary formula. For example at the bottom of the pounds per hour row, it will show the total pounds per hour using autosum. At the bottom of profit margin (which is a percentage) field it will show the average profit margin for the total number of current sales.

In addition to each employees spreadsheet there is a "combined" spreadsheet that shows the same data but for all 10 employees in the one sheet. So here, column 1 has all 10 employees initials throughout the whole column and they are jumbled as the overall sheet is sorted in finish date order, regardless of the employee.

The combined spreadsheet is maintained completely independently from the 10 employee spreadsheets so each time a row of sales data is amended, added or removed in the employees sheet, the same is then done in the combined sheet, or vice versa.

Ideally I would like to maintain just the combined sheet and when a row is added, amended or removed it is reflected in the employees sheet also. Almost a subset of the main sheet which is dynamically linked to the combined sheet. Or the other way around, if I amend an employee's sheet then the combined sheet is updated.

The employees sheets need to be separate sheets as they are accessed over a network by each employee and each employee only has access to their sheet. I have tried filtering but then the formulas do not apply to the filtered results and this wouldn't update a separate worksheet anyway.

Is there a straightforward way to achieve my aim?

Thank you.

[ufopilot]

You can use the "Subtotal" formula with filtered data. If you try VLOOKUP to link to your sheets, them at the top of each criteria with numbers to calculate when filtered, SUBTOTAL would only use the filtered data in the calculations specified.