When you create formulas that refer to other sheets Excel typically includes the name of the current sheet when you return to the current sheet and refer to a cell.
These references are redundant and do not need to be in the formula. Excel inserts the current sheet name whenever you return from navigating to another sheet whilst building a formula.
If you never leave the sheet Excel will not insert the current sheet name. This provides a clue that you don’t need the sheet name in the reference for the formula to work.
As an example, I have two sheets. One is called Data and the other State Report. I am creating a formula in the State Report sheet that refers to the Data sheet. The layout of the Data sheet is shown below.
As I create the SUMIFS formula you can see that when I return to the State Report sheet Excel inserts the name into the formula. See image below.
You can completely remove the reference (highlighted in yellow above)
'State Report'!
from the formula and the formula will still work.
I have included in the image below the before and after formulas, so you can see the difference in the formula length.
Note: Excel inserts apostrophes around any sheet names that have spaces in them. Which makes the formulas even longer. The longer the sheet name, the longer the formula.
To get around the problem you can just type in the current sheet formula reference, which saves you having to edit it later.
Using a short sheet name like Data also helps keep the formula shorter.
Interesting concept but it may be difficult for another user to troubleshoot the formula
Most references don’t have sheet names in them, so it shouldn’t be a problem.
Thanks Neal for sharing this useful tip. I’ve been looking for ways how I can shorten my formulas across different sheets and this solves the problem.
You are welcome – glad it helped.
Neale – Does this work if you have multiple sheet references in one formula?
Hi Richard
It sure does. The other sheets need their names but any cells in the current sheet don’t need the current sheet name.
Regards
Neale