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.
Editors note: And all this time I’ve been setting up PrevCell for each sheet in the workbook. What a chump I am.
Based on that, it appears that you don’t need the OFFSET function. You can define the name cellA1 as =!$A$1
I thought that this names weren’t recommended…
http://www.mrexcel.com/board2/viewtopic.php?p=462059#462059
http://www.mrexcel.com/board2/viewtopic.php?p=496013#496013
http://www.mrexcel.com/board2/viewtopic.php?p=586711#586711
I’d like to hear what Jan Karel has to say on that too :-)
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?
Never mind… I figured it out. What a chump I AM :-D
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.
BUT.
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.
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.
So what are we saying here? Don’t use globals at all? Or use Offset(…) or SheetName!GlobalRange only?
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.
You can create global relative names using INDIRECT with R1C1 mode reference, but personally I would prefer not to use such a construction!
=INDIRECT(R[-1]C1,False)