Advertisement

How to reference formulas in the same Excel worksheet

Spreadsheet modellers should take care to remove unneeded mentions of the current worksheet name.
How to reference formulas in the same Excel worksheet

Here's an issue most of us encounter every day but don't really notice. Imagine you are working in the worksheet "Sheet1" of a particular workbook and you write a formula such as the one shown in the screenshot "Formula Reference 1".


Formula reference 1

bastick-4


That's right. Instead of using cell references on this worksheet, partway through the calculation I have linked to another sheet ("Sheet2") and then linked back to this sheet again afterwards. The result is:

=C8*Sheet2!C6+Sheet1!C4

I am sure we have all produced formulas such as this over the years.

As a model auditor, though, I have a problem with this calculation — in particular, the "Sheet1" reference. The formula

=C8*Sheet2!C6+C4

is not only shorter but also easier to understand. I know it is a reference to a cell on this worksheet, and that makes it easier to check and follow.

But there's more to it than that.

Let me make a copy of "Sheet1" as the formula is presently written. Copying the worksheet creates a new worksheet "Sheet1 (2)", as shown in the screenshot "Formula Reference 2":


Formula reference 2

bastick-3


Amazing, I know. I can rename the sheet, the formula will update, and other than the fact the formula is longer than it needs to be necessarily (a bit like this sentence), it doesn't appear to be a big deal. However, let me now copy the worksheet a different way.

In this instance, I am going to insert a new blank worksheet (say, "Sheet4") and then simply copy and paste in the entire "Sheet1" worksheet, using Ctrl+C and then Ctrl+V, as shown in the screenshot "Formula Reference 3".


Formula reference 3

bastick-2


The first thing you will notice is that my gridlines returned, but more importantly, take a look at my formula:

=C8*Sheet2!C6+Sheet1!C4

This is not referring to "Sheet4" as expected. An end user may think it is correct, too, given the (correct) cell reference to "Sheet2". You might argue that the formula is "OK" — just ensure the worksheet is copied correctly — but exactly how do you enforce the former method of sheet copying in a workbook when others may use it?

I find this Excel behaviour quite dangerous, as it trips up accomplished modellers, too. For example, I have seen highly experienced analysts build a template forecast sheet for a given business unit and then have it reviewed by model auditors — seemingly a very prudent course of action. Once checks have been completed, the sheet has been copied over and over again for a multitude of business units only to have certain calculations all reference the template sheet — something not picked up at the review stage.

Get into the practice of always removing sheet references to the current worksheet — then this cannot happen.

Excel's built-in functionality Find and Replace (Ctrl+H) may be used (ensure Workbook is selected as the Within: category and that Formulas is selected from the Look in: dropdown). In the case of the current worksheet being named "Sheet1", the Find and Replace dialog box would look like the one pictured in the screenshot "Sheet1 Find and Replace".


Sheet1 find and replace

bastick-1


If you cannot see all of these options, click on the Options button in the bottom right-hand corner of the dialog box.

Alternatively, you may use a macro instead. This is particularly useful if a worksheet is hidden and/or protected. A simple example of a macro can be viewed here. This macro does not look for a specific worksheet name but instead loops through each worksheet and removes the self-references on that worksheet regardless of what the worksheet name is.

Word to the wise

The macro may need to be amended if one or more worksheets are protected with a password or if a sheet is "very hidden". No doubt someone will email me with some such instances!

Liam Bastick, FCMA, CGMA, FCA, is director of SumProduct, a global consultancy specialising in Excel training. He is also an Excel MVP (as appointed by Microsoft) and author of Introduction to Financial Modelling. Send ideas for future Excel-related articles to him at liam.bastick@sumproduct.com. To comment on this article or to suggest an idea for another article, contact Jeff Drew, an FM magazine senior editor, at Jeff.Drew@aicpa-cima.com.