Avoiding double entry in multiple sheets.

I currently use Smartsheet for one purpose and still use Excel spreadsheets for another purpose.  I have however hit a problem and wonder if anyone can help and provide a solution.

Currently I have a sales spreadsheet for each employee.  Each sheet contains a range of data.  The range is the same on every spreadsheet and it is just the populated data that changes as it is relevant to each employee.  Each sheet will contain the Employees initials on one column, client in another, then a date column, currency column, percentage column and a couple of others.

At the bottom of some of the columns are basic formulas.  For example at the botton of the percentage column a field will display the average percentage across all entries.  Or the currency column will show a total at the bottom.   The rows are then sorted by the date column.

In addition to each spreadheet for each employee, I also have a combined spreadsheet which includes the data from all the other employee spreadhseets so that I can see totals from a company perspective in one sheet.  Currently the individual sheets and the combined sheet are maintained independently of eachother, so if I make a change in the main sheet, I then manually make the corresponding change in the individual sheet, or vice versa depending on which one I change first.  As I need to change them both it doesn't really matter.

I imported the main sheet into Smartsheet and was then able to set up reports to replicate the individual sheets so that if I made a change in the main sheet with all data, the filtered report would display the individuals data correctly.  The problem I found with this is whilst the data is correct I cannot perform calculations on the report so I cannot display the totals and averages as part of the report.

I then tried it the other way around and created a number of individual sheets.  From here I tried to merge/link the data from all the individual sheets into a new combines sheet.  To a small extent this worked as I can perform calculations on all the sheets but to a larger extent it didn't as I could find no way of creating a tidy combined sheet with all data from the individual sheets in sorted sequential rows.  Even if I did link, if I then added a new row to one of the individual sheets that wasn't automatically linked which made linking a buit pointless as the amount of rows on each sheet is variable and changes frequently.

Does anyone have any ideas on how to achieve this or is this out of the scope of what Smartsheet is capable of?