... until the collector arrives ...

This "blog" is really just a scratchpad of mine. There is not much of general interest here. Most of the content is scribbled down "live" as I discover things I want to remember. I rarely go back to correct mistakes in older entries. You have been warned :)

2006-10-12

Worksheet-Scoped Names in Excel

It is possible to created names in Excel that are scoped to a worksheet, but there are gotchas to watch out for.  If you prefix a name with its worksheet name, e.g. Sheet1!MyName, then the name will be local to that worksheet.  That seems easy enough, but if you forget the prefix then you get a global name.  Subtleties can arise when you copy a worksheet because Excel will create local copies of all names in the new worksheet -- even copies of the global names.  Note that local names can shadow global names, although Excel will not let you create such a situation in the sheet that is the target of a global name.

If you want the references to be relative to the current sheet, and not the sheet that was active when the name was defined, you must prefix the cell reference with an exclamation mark (i.e. !B1 will work but B1 will reference the original sheet).

Blog Archive