Naming cells

W

Watercolor artist

I want to give the same cell in two different worksheets the same name,
"Company," but Excel won't allow me to. Is there a way to do that?
 
B

Bob Phillips

Sheet1!Company and Sheet2!Company

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
G

Guest

Watercolor said:
I want to give the same cell in two different worksheets the same name,
"Company," but Excel won't allow me to. Is there a way to do that?
Click to expand...

--------------

If by "worksheets" you actually mean worksheets and not workbooks, then I don't
believe you can do what you want. Range names are defined across the entire
workbook, not for individual sheets. That's kind of the point of names -- to
make it easy to reference stuff from other sheets.

You might be able to convince "labels" to do what you want. I've never played
with them enough to know, but I believe they're local to a sheet rather than global.

Bill
 
W

Watercolor artist

Bob,

One of my sheets is called "June 05." When I change a cell to 'June
05'!Company per your directions and press ENTER, the function becomes
'MOD-job-report_2005.xls'!Company, which is the whole file's name. What am I
doing wrong?

Howard
 
G

Guest

Bob said:
Not correct Bill.

See http://www.xldynamic.com/source/xld.Names.html
Click to expand...

-----------

I'm a bit confused (a common state). Is this global/local span something which
has changed with Excel versions? In my Excel97 I don't seem to have cell names
local to a sheet as your web page describes. The following steps illustrate:

1) define a cell to be named "Test" and verify I can access the cell using that
name from that sheet.

2) go to another sheet and define a cell to be named "Test" and verify I can
access the cell using that name.

3) go back to the first sheet, and the name "Test" now links to the cell on the
second sheet -- even when used in the first sheet.

4) go into insert>name>define and I see that Excel only shows one cell by that
name and it's linked to the second sheet (most recently created link).

Am I somehow creating the name incorrectly for local use, or does my Excel97
behave differently from your version? Or have I totally misunderstood?

Thanks...

Bill
 
G

Guest

Bill said:
-----------

I'm a bit confused (a common state). Is this global/local span
something which has changed with Excel versions? In my Excel97 I don't
seem to have cell names local to a sheet as your web page describes.
The following steps illustrate:

1) define a cell to be named "Test" and verify I can access the cell
using that name from that sheet.

2) go to another sheet and define a cell to be named "Test" and verify I
can access the cell using that name.

3) go back to the first sheet, and the name "Test" now links to the cell
on the second sheet -- even when used in the first sheet.

4) go into insert>name>define and I see that Excel only shows one cell
by that name and it's linked to the second sheet (most recently created
link).

Am I somehow creating the name incorrectly for local use, or does my
Excel97 behave differently from your version? Or have I totally
misunderstood?

Thanks...

Bill
Click to expand...

---------

Never mind... I understand now that the trick is to create the two cell names
differently (by including the sheet name) and that Excel will then treat the two
names as the same by dropping the sheet name when used locally. Whew!

Thanks...

Bill
 
D

Dave Peterson

Did you use Insert|Name|Define to create that sheet level name first?

If you work with names, do yourself a favor and get a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp
 
D

Dave Peterson

When you do Insert|name|Define, you get a dialog where you can enter the name of
the range and what it refers to.

If you put the sheet name in that "names in workbook" box, like:

Sheet1!Test
or
'Sheet 22 of 23'!Test

Then you've created a sheet level name.

Did you do it this way?

And xl97 to xl2003 have worked the same way.

I put this in the other branch of the thread, too...

If you work with names, do yourself a favor and get a copy of Jan Karel
Pieterse's (with Charles Williams and Matthew Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

(It's much easier to use that then the built in Insert|Names dialog.)
 
B

Bob Phillips

Bill Martin -- (Remove NOSPAM from address) said:
Bill Martin -- (Remove NOSPAM from address) wrote:

Never mind... I understand now that the trick is to create the two cell names
differently (by including the sheet name) and that Excel will then treat the two
names as the same by dropping the sheet name when used locally. Whew!

Thanks...

Bill
Click to expand...

Bill,

You beat me to the answer.

The other thing to be aware of is to have the sheet that the name applies
to, not necessarily the range that it refersto, to be active when you try to
create the local name, otherwise it will fail.
 
Top