Reporting using saved snapshots

Evren Tekin

Hi,

We are attempting to migrate & automate parts of Project Budgeting and Pipeline reports into Smartsheets. Each project will be in a separate file. The project managers will be continuously updating each project but we want reporting to work on snapshots/checkpoints created by the project managers. Scheduled snapshots do not work for us and we strictly need them triggered manually.

Our reports will track the movement across all projects between two selected dates. To give an example, the manager for project A created snapshots at following dates:

• 6 Jan
• 24 Jan
• 25 Jan
• 30 Jan
• 2 Feb
• 20 Feb

If we want to generate a report for date range 1st Jan-1st Feb, we will only get the snapshot from 30th Jan. So the movement will be reported as the figures from 30th Jan minus zero.

A report for date range 15 Jan-15 Feb will take difference of the metrics from 2 Feb and 6 Jan.

I am sure this should be achievable considering the widespread community of Smartsheets but I am still relatively inexperienced using this tool so I couldn't figure it out yet.

Many thanks for all your help,

Evren

Mark Cronk

Hi @Evren Tekin ,

I think you can make this work with VLOOKUP with a TRUE match type which will find the value that is equal to or less than the search value.

You'll need to use dates and not text.

Your formula would be along the lines below. The example would return the change in column 2 (1st column to right of the snapshot date). You'll need to change my cumn names and placeholders to match your sheets:

=VLOOKUP([report end date], [snap shot]:[snap shot], 2, TRUE) - VLOOKUP([Report Start date], [snap shot]:[snap shot], 2, TRUE)

Work?

Mark