Just want to use names across worksheets

  • Thread starter Thread starter LairBob
  • Start date Start date
L

LairBob

This is driving me crazy--I just want to use named cells across
formulas in multiple worksheets, and Excel is just fighting me every
step of the way. (I'm using 2003 Beta 2 update, but I don't think
that's especially relevant, since I've wrestled with this before.)

In this case, specifically, I had started building a big model on one
single worksheet, and had just named single cells as variables for
legibility/flexibility. When I decided to make a copy of the workbook
and break the sections of the model out across multiple worksheets,
things started getting really screwy.

It seems that every worksheet has it's own set of "Names", that it
maintains separately. I had accepted that I would have to "re-wire" a
lot of the Name definitions as I spread things out across sheets, but
rather than re-define each Name once, on its new sheet, it seems like
I'd have to re-define every name on every sheet. None of the
re-definition changes I make on one given sheet propagates across to
any others--I can even delete Names on one sheet, and they persist,
with their "old" definitions, on all the other sheets.

When I look at the "Define Names" dialog on any given sheet, all the
refs have the 'current' sheet in their definitions, even the refs that
I've already defined--for that current sheet--as pointing to another
sheet!

I'd be willing to re-define each of the names, once, if there was some
flexible way to go about it. Am I just wasting my time trying to
actually get this to work?

Thanks in advance for your help.
 
Names can either be global or local. When they are local their name is
prefixed by the name of the sheet they are local to.
It sounds as though you have what was originally a global name which has now
been duplicated as a local name on multiple sheets.

You may find it easier to handle if you download the Name manager that Jan
Karel Pieterse and I wrote: this allows you to see all the local names at
the same time, multiple deletes, convert local to global etc.

download from http://www.DecisionModels.com/downloads.htm


hth
Charles
______________________
Decision Models
The Excel Calculation Site
www.DecisionModels.com
 
Hi,

Just an extra little tip.

You can define a name such, that it always refers to a
range on the sheet the name is used in. E.g.:

Insert, name, define

Name: ReferToThisSheet
Refers To: =!$A$1

Now once you use this (global) name on ANY sheet, it will
always refer to cell A1 on THAT sheet.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
Hi,

Please disregard this last message of mine, these kind of
names are not reliable. They even crash XL97 when using
the trace dependents option.

Regards,

Jan Karel Pieterse
Excel TA/MVP
 
That was my general understanding as well, regarding the names'
domain. What seems wierd is that global names apparently take on the
name of the local sheet when you look at them (among other
wierdnesses).

In any case, I'll gladly download the tool you suggested--it seems
that this is one of those places that MS has left woefully
underdeveloped.

Just in case anyone else has run across this problem, I did finally
get my own situation to work by erasing _every single one_ of the
names that were present on _every single one_ of the worksheets. (75+
names duplicated locally on four sheets--that's a lot of
"click-'Delete's.) I then went through and re-created the names using
the "Name Box" (which was news to me)--I had created the first batch
through the "Define|Names" dialog.

One last point that seemed to make a difference in helping things work
this time around was using absolute references. I had originally set
up the model in one long column, with the names all relative to the
'local' column--I wanted to be able to copy the column several times
across a worksheet, and run comparative scenarios. Even using the
technique I'm using now, if I go into "Define|Names" and make the
references relative, things get very wonky.

Thanks for your help.
 
Hi ,
Just in case anyone else has run across this problem, I did finally
get my own situation to work by erasing _every single one_ of the
names that were present on _every single one_ of the worksheets. (75+
names duplicated locally on four sheets--that's a lot of
"click-'Delete's.)
Click to expand...

Of course with the Name Manager (in Multi-mode), you can select lots
of names and then delete them all in one go.
One last point that seemed to make a difference in helping things work
this time around was using absolute references. I had originally set
up the model in one long column, with the names all relative to the
'local' column--I wanted to be able to copy the column several times
across a worksheet, and run comparative scenarios. Even using the
technique I'm using now, if I go into "Define|Names" and make the
references relative, things get very wonky.
Click to expand...

Maybe some elaboration about relative names is needed here.

If you define a relative name, the address(es) in that name's refersto
are relative to the ACTIVE cell. Example:

Select cell C3
Define a name Test, refersto =Sheet1!A1

Now type =TEST into cell C3 and copy down.
Cell C3 will now show the value of A1, but cel C4 will show the
content one row down, thus cell A2. Similarly, it =Test is placed in
Cell E4, you'll get the value of cell C2.

If you select cell E4 and open the insert, name, define box, you'll
see the address in the name Test has changed to reflect your move from
cell C2 to cell E4 and now refersto Sheet1!C2.

This can be a handy feature, but one must take care with what cell is
selected when (re-)defining the name.

The name manager detects relative names and warns you when you try to
edit them.

Regards,

Jan Karel Pieterse
Excel MVP
 
Back
Top