Variance Report in Smartsheet with Multiple source sheet?

Pramod Siddarth

Hello Community!!

I'm Looking for a Solution which is quiet complicated in my business scenarios.

I would like to explain with sample data with the same concept what I'm trying to do in my organization.

1. I have the Workspace called "Global Database" where I have one Main source sheet called "Global Request Main Tracker"

PFA the Tracker sheet

2. In the same workspace I have the Metric sheet " Global Metric Sheet"

3. Since the cost data will occasionally change and need to be saved in the same workspace, we freeze the "Global Main Request Tracker" each month by saving it as new and naming it "Freezed Global Request Tracker (Month Name)". For Example:"-

This is the Current Architecture I have it in my Workspace

So now I need to create Variance between the " Global Main Request Tracker sheet " & " Freezed Global Request Tracker (Month Name)" in My " Global Metric sheet"

In the below Main Cost Data The Formula In Global Metric Sheet as follows

Now for the Freezed one the formula is same only source sheet will change =SUMIFS({Freezed Global Request Main Tracker July 2021 Range 1}, {Freezed Global Request Main Tracker July 2021 Range 2}, [Column2]$1, {Freezed Global Request Main Tracker July 2021 Range 3}, $Metrics@row)

Now between these 2 need to take the Variance

So now ,I'm looking for a solution because sometimes the frozen data changes month to month. If we change the frozen data's source sheet, the formula also needs to be changed manually. Is there a way to save the previous month's data here in the workspace as new, so that when we open the metric sheet, the value should automatically pick up in metric and display appropriately?

SoS | Dan Palenchar

Hello @Pramod Siddarth,

A much easier way would be to set up a copy row automation and copy the data of interest from your main Sheet to another Sheet. Set this up to run EOD last day of the month.

When rows copy all formulas become plain text.

This solution will give you one Sheet that has a monthly record of your financial data which you can compute against.

You may need to modify the structure of your main Sheet so it copes in a way that makes sense and you can use.

To easily copy the entire Sheet or multiple rows nest all of the data you want underneath a parent row and simply trigger that row to copy. By definition all child rows copy with their parent.