ICAEW.com works better with JavaScript enabled.
Exclusive

Excel Tip of the Week #429 - Revisiting cross-sheet formulas

Author: David Lyford-Tilley

Published: 18 Jan 2022

Exclusive content
Access to our exclusive resources is for specific groups of subscribers.

Hello all and welcome back to the Excel Tip of the Week! This week we have a General User post in which we’re taking a fresh look at how to write and work with formulas that reference cells on other worksheets. This was first discussed in TOTW #275.

The basics

A reference to another worksheet looks like a reference within the same sheet, with a little extra to specify which sheet is being referenced. There are two variations that you will see:

Excel screenshot

For a sheet with a single word name, the name of the sheet is simply listed, followed by an exclamation mark and then the cell(s) being referenced.

Excel screenshot

For sheets with multiple words in their names, the sheet name is wrapped in a set of single quotes. The exclamation mark part remains the same.

Note that cross-workbook references build on this same model – see this reference to another workbook which is currently open:

Excel screenshot

And the same reference when the other workbook is closed:

Excel screenshot

In either case the filename is included in square brackets before the sheet name; if the other file is closed then the full file directory is also included.

The same syntax also works for references to ranges of cells on other worksheets; if you don’t have Office 365 or 2022 then you will need to include some kind of summary function in one of these:

Excel screenshot

3D references

You can expand further upon this idea by creating what is called a 3D reference. Just like a range can refer to a block of cells that lies between two addresses (like E4:N4 above), a 3D reference can refer to the same block of cells across a selection of adjacent worksheets. There are some wrinkles that make them a little tricky to work with, but for starters let’s look at how they work. Starting with adding up a single cell from each sheet:

Excel screenshot

Essentially you just append the first and last sheet names using a colon, the same way you would for a range of cells, inside the single quotes. The same approach can be used for ranges of multiple cells across multiple sheets:

Excel screenshot

Now, as mentioned, this isn’t a perfect approach. Of course it relies on all your individual sheets having identical layouts – which isn’t easy to check. Adding a row to one of the sub-sheets would lead to a material issue but be easily missed.

Secondly, because the first and last sheets are directly named in the formula, rearranging the sheets will change the results. If another sheet is added in between the two ends, it will be included in the sum – and vice versa, if a sheet is removed from the bookended range, it will be dropped out. Worst of all, if you move one of the end sheets to the “wrong” side of the other, then any 3D references to it will be permanently altered without so much as a warning.

For this reason, I usually recommend not using actual data sheets as the end points, but instead creating blank Start and End sheets, and then putting the data in between those. That should make it clearer to users a) which sheets are included and b) which can and can’t be rearranged.

A final note on 3D references – many more complex formulas simply don’t accept 3D references as inputs. For example you can’t do a SUMIFS that uses one. In most situations, you are better off using direct references to bring the values from your sub sheets onto a summary sheet and then using formulas on them. You could even use ADDRESS/INDIRECT to make this easier (see TOTW 314 for details) This has the added advantage of making it much clearer if an added row or other issue is present. You can see a demonstration of this idea, along with all the other examples from this week’s post, in the attached file.

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel