30-Oct-2008 Using INDIRECT to refer to cells on Analyse-it analysis reports

A customer contacted us last week to ask how to refer to cells on an Analyse-it report worksheet, from a formula on another worksheet. The customer often used Analyse-it's refresh feature, to repeat the statistical analysis and update the statistics, and direct references to cells on the report were being lost on refresh.

As an example, suppose you have used Analyse-it linear regression to calculate the linear relationship between installation cost and the number of employees required, distance to the site, and the cost of machine being installed. Analyse-it would calculate the effect of each variable on the final cost, technically known as regression coefficients, which you can then use to predict installation costs for jobs in future.

You might setup a worksheet to predict and quote installation costs for future jobs. You could use an Excel formula to reference the coefficients directly from the Analyse-it report, for example:

= Employees * CostAnalysis!C17 + Distance * CostAnalysis!C18 + MachineCost * CostAnalysis!C19

By directly referencing the coefficients calculated by Analyse-it in your formula, you can be sure you’re using the exact values with no chance of error.

If you’ve used this technique before, you already know the problem. When you refresh the Analyse-it report, to repeat the analysis and recalculate the statistics, references to cells on the report worksheet are broken. In the above example, the references to cells on the CostAnalysis worksheet become #REF!, for example:

= Employees * #REF! + Distance * #REF! + MachineCost * #REF!

#REF! simply means the reference is broken and refers to a cell that no longer exists.

The reason the cells no longer exist is because when you click Refresh, Analyse-it repeats the analysis, creates a new report worksheet to present the statistics and charts, then finally deletes the old worksheet and replaces it with the new. The cell references are broken when Analyse-it deletes the old report worksheet.

Although not perfect there is a simple workaround for the problem using the INDIRECT function. Rather than refer directly to a cell, you wrap the cell-reference in INDIRECT(“..”), to indirectly refer to it. For example, the installation cost prediction formula would become:

= Employees * INDIRECT("CostAnalysis!C17") + Distance * INDIRECT("CostAnalysis!C18") + MachineCost * INDIRECT("CostAnalysis!C19")

If you now refresh the analysis, for example because you have collected more data, or found an error in the original data, the formula will still work. Cell references won’t be broken, and will refer to the latest calculated coefficients.