Excel Tip of the Week #275 - Cross-sheet formulas

This blog is brought to you by the Excel Community where you can find additional blogs, 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 Filtered. There is also an online forum where you can ask questions and share ideas with other community members.

Hello all and welcome back to the Excel Tip of the Week!  This week, we have a General User post in which we're going to look at how references and formulas can work across multiple Excel sheets.

Referring to a single other sheet

The easiest way to see how a cross-sheet cell reference looks is just to type = and then go click on a cell elsewhere in the workbook:

Note that the reference style is slightly different depending on whether the sheet name is a single string or whether it contains spaces - for sheets with spaces in the name, the sheet name is inside single quotes.  In any case, after the sheet name, there's an exclamation mark followed by the usual cell reference text.

This same approach also lets us refer to ranges on other sheets:

3D ranges

We can also make a special so-called "3D range", which is a range which includes the same cell(s) on a series of neighbouring sheets.  Here are some examples:

These formulas add all values from the stated cells across every worksheet from OneWord to Multiple words.  You can see how the same colon operator is used to conjoin the two sheet names as is used to conjoin two cell addresses in a standard range.  Also note how the entire sheet element is encased in a set of single quotes.

These references are useful for e.g. totting up across a set of identical subsidiary sheets.

However, an important caveat: Changing the order of sheets in your workbook can mess up a 3D reference.  If an extra sheet is moved in between the two ends, then cells in that new sheet will then be added to the total - and likewise if a sheet is moved outside of the range then it will be dropped from the total.  However, if the "end sheet" is moved to the left of the "start sheet", then the formula will be irrevocably changed to one that doesn't refer to the range - you can't use Undo or put the sheet back to revert this kind of change.

A piece of good practice that's often used to get around this is to have any sheets that feed into a 3D reference sit between a pair of blank sheets called something like START and END; this makes it much clearer that the sheets belong in a certain range and allows reordering without the risk of messing up a formula.

All the examples discussed above, plus a demonstration of the START/END method, can be found in the example file linked at the bottom of this post.

Previous post - Mixing multiple chart types
TOTW index 
Next post - Conditional formatting redux

Anonymous