Question of the Week – DESTINI Estimator: How do I use a “References” tab to avoid broken formulas in the dashboard?

Questions of the Week are asked and answered in the Beck Technology Community. Anyone is encouraged to join the community to discover additional tips and tricks.

How do I use a “References” tab to avoid broken formulas in the dashboard?

This answer is provided by Casey Wilt, implementation specialist, and posted on our community as well as below.

Most Excel formulas are hard coded to a specific cell or range of cells. Examples….

=B1
=SUM(A1:A10)

These simple formulas are acceptable when you are working in a static data environment. However, the DESTINI Estimator dashboard is a dynamic environment, and we are live-linking to ever-changing data on a separate sheet.

The dynamic nature of the dashboard means that we cannot assume that the columns in the $$Line Items$$ sheet will be in the exact same order in every single estimate into which you bring the dashboard. Estimates created from the same template should be just fine, but a one-off estimate may have different WBS properties, etc. If the columns get moved around, you’ll see #REF, #N/A or bad quantities throughout the dashboard.

Don’t lose faith! There is a straightforward method to preventing these issues. Take a look at this dashboard…

Notice that the formula uses simple hard-coded cell links(Z:Z, etc). If any new WBS properties are added and the $$Line Items$$ page is rearranged, the links in that formula may be broken. The only fix is to go cell-by-cell and fix the broken references. There has to be a smoother approach, right?

There is! Let’s introduce a new tool – the References tab. With your dashboard open in Excel, add a new tab to the workbook. On this new tab, we will create two columns of data. First, Column A will be the estimate column name we are interested in  – System, Uniformat Level 1, etc. The second column is the Excel column name for that  – A, B, C, etc.

The end result is something like this …

Very simple right? The end goal here is that instead of saying our aggregate total range is …
‘$$Line Items$$’! $Z:$Z
we will instead introduce a dynamic environment…
INDIRECT(‘$$Line Items$$’! (References Tab, Cell B3) : (References Tab, Cell B3))

If we use this logic pattern for all of our formulas, the fix for a broken formula is limited to adjusting the values on the References tab. Changing 3-8 cells, at 3 or less characters apiece, is a lot easier than manually fixing 100 formulas! It looks a bit busy in the formula bar, but when executed correctly, it makes the dashboard architect’s life 10x easier.

The INDIRECT function returns the value of a cell that contains the text called out in the equation. We will basically be using it as an advanced CONCATENATE function. As such, the string sections will be brackets by “____” and joined by & symbol.

INDIRECT( ” ‘ Page Name ‘ ! ”  + “References Cell” + “:” + References Cell)

Line Item Aggregate Total
INDIRECT(“‘$$Line Items$$’!” & References!$B$3 & “:” & References!$B$3)
=
‘$$Line Items$$’!$Z:$Z

Uniformat Level 1
INDIRECT(“‘$$Line Items$$’!” & References!$B$4 & “:” & References!$B$4)
=
‘$$Line Items$$’!$BP:$BP

System
INDIRECT(“‘$$Line Items$$’!” & References!$B$5 & “:” & References!$B$5)
=
‘$$Line Items$$’!$B:$B

*Note: The $ on the column names locks that reference location, no matter where the formula is pasted. Formulas in excel are by default relative, so we are overriding that.*

The end result is the new SUMIFS formula for that cell. Replacing the hard-coded ranges with dynamic ones, and keeping the same criteria for each criteria range.

=SUMIFS(INDIRECT(“‘$$Line Items$$’!” & References!$B$3 & “:” & References!$B$3),INDIRECT(“‘$$Line Items$$’!” & References!$B$4 & “:” & References!$B$4),$A4,INDIRECT(“‘$$Line Items$$’!” & References!$B$5 & “:” & References!$B$5),$B$1)

Test your new dashboard in a couple of estimates. If the link to the Uniformat values is broken, adjust the References tab and make sure it corrects itself as expected. You’ve now got a tool to make sure that all new dashboards you create are agile, dynamic, and ready to take on whatever estimate you throw at it.

Don’t hesitate to reach out with questions or new ideas!
CW