Best Workflow for tracking tasks and reporting

Jeana

I am using Smartsheet to accept requests (using a Form) to upload a video and get captions generated then share the links and other details with the requestor once the tasks are completed. This process involves several steps over a period of time (and a few different people).

I have an input form that populates a sheet where I have added columns for the steps to track the steps of the request. I can use a formula to generate a "status" and report off this sheet and it works fine.

My issue is that the number of columns to do this is pretty long and it's a scroll-a-thon even when I freeze the key columns to see details people need to either track the tasks or see the links and related data. Plus it's just not visually appealing.

My second attempt was to organize the steps vertically using children. I like the way this looks much better although to see ALL the form fields and links it's still a scrol-a-thon. Another issue with this is reporting on the Children Rows - haven't figured that out yet.

What brought me to questioning my whole workflow was when I tried automating the moving of the input fields from the source sheet to the Tracking sheet. Each new form creates a new row but not all the children (Steps) required to track. Then I tried coping rows from the Form sheet to the tracking sheet but then it copied more columns than I wanted - is there a way to selectively copy rows/columns?

It seems either method is workable but before I go digging into reports and linking and summaries, etc. I would like some input from others that have created similar workflows.

What's the best method to organize or separate the Input from the tracking sheets and still reference or pull data? What combination of automation, cell linking, summary fields, etc. works best?

My desired outcome:

An input form for people to request the video be uploaded and captioned (Done)

One place for the people involved in the tasks to track their steps and to and enter the related links/content.

A "User Report" or way for individuals to see the status and related links for their specific requests and for managers to see status and related links for all requests.

Thanks for any insight anyone might be able to provide.

Jeana

Video Request Tracking pic.jpg

Video Request tracking pic 2.jpg

Video Tracking Better look.jpg

Andrée Starå

@Jeana

Hi Jeana,

I have a few alternatives. Here are two. The best one depends on one of the specifics of your process.

1. Intake sheet linked with another sheet(s) through cross-sheet formulas and VLOOKUP or INDEX/MATCH.
2. Premium add-on, Dynamic View.

Would any of those options help/work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Dana Esquivel

Hi Jeana,

Have you considered using reports from the sheet to help you display this information? You could have a different report for each user type which you could tailor to only display the information relevant to them. You could also consider using the reports on a dashboard that is user specific and only displays information relevant to the user viewing the dashboard. Thoughts?

Dana

Jeana

Yes, I have produced reports from the tracking form and that works ok but there is still quite a bit to scroll through (horizontally). Part of what I'm looking for is a better way to present the tracking and link information so people don't have to sift through so much. I may just be over thinking it. :-)

Jeana

Andrée Starå

@Jeana

Hi Jeana,

I have a few alternatives. Here are two. The best one depends on one of the specifics of your process.

1. Intake sheet linked with another sheet(s) through cross-sheet formulas and VLOOKUP or INDEX/MATCH.
2. Premium add-on, Dynamic View.

Would any of those options help/work?

I hope that helps!

Have a fantastic week!

Best,

Andrée Starå

Workflow Consultant / CEO @ WORK BOLD

✅Did my post help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

Jeana

Hi Andrée,

Thanks so much for your reply. We don't have Dynamic View although I'm making a case for it now. :-)

In the mean time I'm going with the Index/Match option. Does it make sense to pursue this using the vertical view I shared using children as the actual steps in the process?

Thanks!

Jeana

Jeana

Andrée,

Can a field in a Form be dynamically pre-populated from another sheet's column that contains a list of current projects?

If not in the intake form itself can I pull from the current Project sheet to dynamically populate a DropDown?

Jeana

Andrée Starå

@Jeana

Happy to help!

Unfortunately, it's not possible at the moment to "sync" drop-down lists, but it's an excellent idea!

Please submit an Enhancement Request when you have a moment

Andrée Starå

@Jeana

Re. your first question.

Yes, it looks to make sense to me.