Smartsheet demo on tracking and weekly and historical project status using Data Shuttle

There are a variety of scenarios where stakeholders wish to view the trend of project status over time and the weekly / monthly snapshot. This can equally apply to financials, among a whole host of use cases.
In this video I demo how you can take a scheduled weekly snapshot using Data Shuttle and then add that snapshot to a rolling record that in turn can be used to feed metric sheets and dashboard views, as well as simply serving as a reference sheet for anyone wanted to see the historical data.
Note this can be done without DataShuttle by using the copy row function - it just means you end up with a lot more columns, but maybe you'll find them useful for future reference by having a history of the weekly snapshot.
Any questions, or for help with your PMO Smartsheet solution, including using any of Prodactive's extensive range of solutions, please get in touch. We're an award-winning Smartsheet partner based in the UK, and helping clients all over the world, from North America to Australia, achieve great things with Smartsheet.
www.prodactive...

[@neilwatson168]

Great use case example of data shuttle, automation and metric sheets.

[@Smartsheetexpert]

Hi @neilwatson168! We haven't seen you around in a while! Did you see our latest video on automating tasks? If not, be sure to check it out!

[@cymandebryant276]

Awesome video. Can you explain the INDEX COLLECT formula at the end that is used to generate the Weekly Trend Report.

[@Smartsheetexpert]

INDEX COLLECT allows you to use multiple criteria in a look-up. In this case the item being INDEXED, ie that is being brought in, is the RAG status from the archive sheet which here is a cross-sheet reference {RAG} to that sheet. After this first part, it then searches the project name column {Project} and finds the project of the name in Project@row, then searches the {update date} column and finds a date that is greater that the previous week and equal to or less than the week of that column. Hope that helps! If not clear, have a play around with the index collect formula on some other items then replicate the sheet as best you can and keep playing until you crack it. Or get in touch!

[@marvelz6355]

Awesome video. Thanks 🙏

[@Smartsheetexpert]

Glad you found it useful!

[@abarker2]

This is really good. Thanks so much for your content. Question: Is Data Shuttle truly necessary for this particular scenario? Why not just move the rows from the first sheet to the second and run metrics from there?

[@Smartsheetexpert]

I used Data Shuttle in this scenario so it only pulled the data from certain columns, rather than every column on the portfolio sheet and it's really easy to schedule the timing for a specific day of the week. My aim is to show an approach that people can they modify to their own scenarios and needs. I've got a simpler version of this I use elsewhere without Data Shuttle where I'm happy for it to copy across all the columns of a simple weekly report. The great thing about Smartsheet is there are always many ways to achieve things!