Avoiding double entry in multiple sheets.

Spencer Simmonds

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?

Travis

Do you need to see these calculations at all times or are you ok with seeing them on the fly? In reports, if you highlight data, the bottom right of the sheet will show some calculations. For example if you highlight a group of numbers, you will see sum, avg, min, max, and count. 

 

 

Another option is to export the report to Excel, then import that back into Smartsheet and create formulas in that new sheet. The only disadvantage of this, is you would need to export a new report whenever the data changes. 

Spencer Simmonds

Thank you Travis.  I will look at the fist option.  This isn't 100% ideal as within excel I create calcualtions on the calcualtions in new cells but this may work.

 

The second option wouldn't be viable as the data changes on a daily basis and I think this would be more manual than the current process.