Change Dashboard/Report based on choosing a piece of information

treyrosejohnson

Hello,

I am a relatively new Smartsheet user. I started a new job a couple months ago and my office wants to start using Smartsheet more and I've been tasked with getting up to speed and finding ways to help make things run more efficiently using Smartsheet.

I currently have separate smartsheets for booked jobs, invoices, purchase orders, and equipment for all jobs we are currently working on. Each sheet has a parent row for each job with the children rows being the individual job booking/change order booking information, individual invoices, purchase orders, etc.

Is there a way to build a report or dashboard that would allow me to pull all information from each sheet or report to one centralized place based on the job number, if the job number were in the same format, etc. in each sheet?

If so, is there a way to add a dropdown box with each job number to change from job to job on the same dashboard?

Like I said, I am still fairly new to all of this, so I am still figuring out the capabilities and limits of Smartsheet.

Thanks!

Matt Johnson

Hi @treyrosejohnson

Those are great questions.

Yes to the report question with the job numbers. Add a column to each sheet called Job Name or Job No. or whatever, do a column formula with =PARENT(column with the parent job). Then create a report and for the source, pick all the sheets. Then when you add the column be sure to includ the Job Name column you just created and you Group by that and Summarize the money columns and they'll roll up into each Job aka Group.

As for the dropdown box on the dashboard, that's not currently available in Smartsheet. I think I've seen some developers whip up some API magic to mimic that but its not standard at this time. However, you could create a report for each job (create 1, save as new, rest the filter, repeat) and then put links to each report on a sheet/report and embed that on the dashboard. The good thing about that is that no one could set the filter and then forget the filter is set when reviewing the data. Its a new window with the project name clearly visible at the top.

I hope that helps.

Matt

treyrosejohnson

Thank you Matt for these wonderful suggestions. The Parent row idea led me in a great direction and I used the Smartsheet University self-paced eLearning portal to learn more about this and it was hugely helpful. This absolutely worked and helped me get over a big hurdle and increase my skill level in Smartsheet. I appreciate your detailed and clearly worded suggestions.