Global Defined Names

Created by Bob Umlas and David Hager

To define a global range name in Excel, go to Insert, Name, Define and, as an example, in the Names in Workbook box type “cellA1? and in the RefersTo box type this formula: =OFFSET(!$A$1,,,,).

Now, type =cellA1 in a cell on any worksheet in the workbook and it will return the value in A1 for that worksheet.

Name box showing defined name

Two windows showing different sheets using defined name

Editors note: And all this time I’ve been setting up PrevCell for each sheet in the workbook. What a chump I am.

Name box showing PrevCell

PrevCell used in a formula

Based on that, it appears that you don’t need the OFFSET function. You can define the name cellA1 as =!$A$1

Posted in Uncategorized

8 thoughts on “Global Defined Names

  1. How does this differ from just entering myCell in the name box for Sheet1!$A$1 and hitting enter? No matter where I am in the workbool =myCell would return the value of “Sheet1!$A$1?

    What am I missing?

  2. Hi Juan Pablo,

    You are right on the dot.

    If you use names like that, as long as Excel itself is firing the recalculation of the worksheet, all is well.


    As soon as VBA fires a recalc (e.g. Application.Calculate)

    Any cell that uses this global sheet-independent name will get the value of cell A1 ON THE ACTIVE SHEET!

    This may lead to wrong results as I need not explain.

  3. DON’T DO THAT!!

    It’s even worse than Jan Karel suggests. If, for example, you have calculation set to automatic, you can trigger a recalc in VBA by setting a cell’s value in any worksheet. If you do, the ‘global’ name will refer to the worksheet in which the recalc was triggered.

  4. So what are we saying here? Don’t use globals at all? Or use Offset(…) or SheetName!GlobalRange only?

  5. We are saying: don’t use the !A1 construct (without sheetname) in a defined name if you want a global name that always refers to a range on the sheet you are using the name in.

    Better to use =INDIRECT(“A1?), which has the desired effect too, without the VBA recalc bug.

    Only drawback is that the reference is fixed, but I guess that is no problem because there is no way to know when the reference needs to change, unless all sheets are selected.

  6. You can create global relative names using INDIRECT with R1C1 mode reference, but personally I would prefer not to use such a construction!


Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.