Dynamic reporting across multiple sheets

Nolan Nordlund

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?

Andrée Starå

Hi Nolan,

There are a few different ways to structure a solution.

Can you describe your process in more detail and maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

Nolan Nordlund

Unfortunately my organization is pretty strict about confidentiality so I can't upload or screenshot but I'll try to be more detailed.  We have a team of photographers and editors creating product images.  I am specifically trying to track how many products were photographed in the last week.  When we sign a new client contract to create product images for them, a new grid item is created in Smartsheet. It contains a list of all the products that are included as part of that contract. It also has photographer and a photo date columns. 

At different points in the past, we've had as many as 25 contracts in progress at a time and some of them are finished in just a day or two. Because of that, the ability to dynamically search across all those different contracts to find out how many total parts a photographer was able to process is important.

Andrée Starå

Ok.

I'll give it a shot!

I'd recommend having an Intake Process and then connect that to a master template that would be used for each new contract.

A new copy would be created for each contract.

We'd then use Sheet Summary or a specific section in the sheet to calculate the metrics for each contract. We could also use a Master Metric Sheet to collect all the metrics and be able to calculate further.

Reports could be used to see everything together or maybe filtered to each owns tasks.

What do you think? Would that work?

Nolan Nordlund

Is intake process some kind of template or something? Do you have a link to an example?

Andrée Starå

It can be a sheet or a form. Most of the time, I use a form that submits to a sheet to manage projects, prospects, or something else.

Make sense?

Andrée Starå

Here's an example.

https://www.smartsheet.com/marketplace/template-set/project-intake

Andrée Starå

Hi Nolan,

Did you get it working or do you still need help?

Have a fantastic week!