Use same cell name on mulitple sheets

  • Thread starter Thread starter biebs
  • Start date Start date
B

biebs

I am trying to figure out how to use the same cell name on
mulitple sheets. I have a workbook setup with 7 sheets,
and they all have the same layout. I want to rename
cell "e4" to a new name, say, "common". When I click on
the cell then click in the name box and type in the name
(common) it will work on one sheet but I can not use the
same name on the other 6 sheets. I followed the guide
that microsoft has online where they suggest to go
to 'insert' 'name' 'define', enter the new name, click on
each tab that you want to include, then click on the
cell. But that does not seem to work. Any ideas??
 
Biebs, the procedure you describe in your post creates a named 3-D
reference, where a single name stands for a range of cells that spans
multiple worksheets. This is not what you want.

Note that a global name, such as "common," can only be used once per
workbook. This is good, because it means that you can refer to a specific
cell or range on a specific worksheet simply by using that name.

What you want to do is create local names. To do this, include the name of
the worksheet in the name, thus: "Sheet1!common." Local names will appear in
the Name box dropdown list on that sheet only (and without the sheet name).
In the Insert > Name > Define dialog box, they will appear only on that
sheet, and will have the sheet name listed along with them.

You can refer to a local name from anywhere in the workbook. So on sheet2
you can enter a formula such as "=Sheet1!common" to refer to a name that is
local to sheet1. Note that you don't have to use the sheet name when you
refer to a local name on the sheet on which you defined it.

DDM
"DDM's Microsoft Office Tips and Tricks"
www.ddmcomputing.com
 
DDM said:
What you want to do is create local names. To do this, include the
name of the worksheet in the name, thus: "Sheet1!common." Local names
will appear in the Name box dropdown list on that sheet only (and
without the sheet name). In the Insert > Name > Define dialog box,
they will appear only on that sheet, and will have the sheet name
listed along with them.
Click to expand...
....

Perhaps. An alternative is creating a generic defined name, such as common
referring to =!$E$6. Note that the exclamation point immediately following
the equal sign is intentional. Names like this apply in all worksheets but
always refer to ranges in the same worksheet as the formulas containing
references to them. So foo!X99 had the formula =common+1, it'd be evaluated
as foo!$E$6+1.

One downside to this sort of defined name is that they don't appear in the
names drop-down list (at least not in XL2K).
 
two more downsides to names with refersto starting with !

- they give incorrect answers when any calculate method is called from VBA
(they always refer to the active sheet)
- the Trace precedents commands either crash Excel (XL97) or do not work
(XL2002)

regards
Charles
______________________
Decision Models
FastExcel Version 2 now available.
www.DecisionModels.com/FxlV2WhatsNew.htm
 
two more downsides to names with refersto starting with !

- they give incorrect answers when any calculate method is called from VBA
(they always refer to the active sheet)
- the Trace precedents commands either crash Excel (XL97) or do not work
(XL2002)
Click to expand...
...

Things are never easy with Excel. OK, to make the name common refer *safely*
to cell E6 on any worksheet, define it as =INDIRECT("E6"). This avoids the
recalc problem and crashing XL97, though XL97 doesn't show such references as
precedent indirect cells, but XL97 doesn't draw tracing arrows when any indirect
references are involved.
 
Back
Top