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?
Comments
-
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
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
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.
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Is intake process some kind of template or something? Do you have a link to an example?
-
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?
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Here's an example.
https://www.smartsheet.com/marketplace/template-set/project-intake
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
-
Hi Nolan,
Did you get it working or do you still need help?
Have a fantastic week!
SMARTSHEET EXPERT CONSULTANT & PARTNER
Andrée Starå | Workflow Consultant / CEO @ WORK BOLD
W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35
Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 377 Global Discussions
- 207 Industry Talk
- 440 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 289 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!