Track Daily Variance Between Forecast to Actuals Across Multiple Cells and Sheets into Master View

cyndi_walker108886

Hello,

I am desperate for some guidance on the best way to handle my situation.

First, I have 2 sheets that I'm attempting to provide an executive view for:

1. First sheet is a forecast that indicates the number of people we should be training in any product line.  The individual product lines are listed in the primary column; they ladder up to partners, which ladder up to master products - all so I can see the daily headcount values at each hierarchy.  The columns are all dates, where each column is a new date.  The first date column is 11/20/19 and the last date column is 5/1/20.  So I have LOTS of variances to measure on a daily basis.
2. Second sheet is for actuals.  It is an exact match to the first sheet, except it doesn't have any forecast numbers in it.  Partners are responsible for going into the sheet daily and updating their sections accordingly.

Now, I need to track for the variance between the forecast and actuals so that we can pre-emptively adjust when we're falling behind forecast.

I thought I could accomplish this via a METRIC sheet, where I would just create a sheet 1 cell 1 - sheet 2 cell 1 type of formula all the way across all the cells..... but it doesn't look like I can do that.

Any suggestions on how I can solve for this?  Is there a better way to get there that I'm just not seeing?  I am terrifed of having to do this via the Sheet Summary - way too many fields that I'd need to solve for to get what I need.

Alejandra

Hi Cyndi,

You could certainly create a metric sheet using cross-sheet formulas. If needed, more information on cross-sheet formulas can be found in our help article: https://help.smartsheet.com/articles/2476606-formulas-reference-data-from-other-sheets