Dynamic reporting across multiple sheets


My organization recently moved from Google sheets to Smartsheet. We were tracking the number of jobs completed by each team member in the last week. We had multiple groups of jobs that would be uploaded as a new tab on the sheet. The reporting we had set up was able to search the new tabs for data to include in the report.

I'm trying to duplicate this in Smartsheet but I'm having difficulty.  After trying out a few different methods, I am now trying to track it on a sheet. I have set up the columns as the different team members and the rows are the job groups (usually some kind of client contract or order #). In each cell I am using COUNTIFS to which reference the sheet related to the contract or order to find the team member and the date the job was completed.

The problem with this method is that any time a new contract or order is started, we have to manually add a row, copy the formula from another cell, and create new references for the new contract/order. Does anyone know of a better way that wouldn't require us to do all this work manually when we create a new sheet for a contract/order?


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!