Naming cells

  • Thread starter Thread starter Tony B.
  • Start date Start date
T

Tony B.

HI,

I named the cell A1 in sheet1 of my workbook and make
reference to it in other formulas within the same sheet,
when i copy the sheet1 into sheet2. I would like the
named cell to correspond to A1 in sheet2. Obviously as of
now it is still referring to A1 in sheet1. Is there a way
to link a named cell to an active spreadsheet?
Thanks,
 
When you create you name, make it a worksheet-level name by
entering the sheet name followed by an exclamation point followed by
your cell name in the name box, e.g.:

Sheet1!myName

When you copy to sheet2, the reference will become a sheet-level
name in sheet2: Sheet2!myName
 
When you name the cell, prefix the name with the sheet name followed by an
exclamation (!) point.
For example, click in the name box of each individual sheet and type:
Sheet1!value1
Sheet2!value1
Sheet3!value1
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


HI,

I named the cell A1 in sheet1 of my workbook and make
reference to it in other formulas within the same sheet,
when i copy the sheet1 into sheet2. I would like the
named cell to correspond to A1 in sheet2. Obviously as of
now it is still referring to A1 in sheet1. Is there a way
to link a named cell to an active spreadsheet?
Thanks,
 
My names are already created in Sheet1. When i go into
Insert/Names/Defined i see sheet1!$A$1. What should i do
from there. I am not sure i followed you with the Sheet1!
myName(this looks more like VB to me)...Do i have to
create the name somewhere before i copy the worksheet?
Thanks,
 
Heck, I've been typing unnecessarily all this time !
Didn't know it worked that way, John.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

When you create you name, make it a worksheet-level name by
entering the sheet name followed by an exclamation point followed by
your cell name in the name box, e.g.:

Sheet1!myName

When you copy to sheet2, the reference will become a sheet-level
name in sheet2: Sheet2!myName
 
Both Workbooks and Worksheets have Name properties.

Unless you used Sheet1!myName when defining myName, then myName,
which refers to a cell in sheet1, is a Workbook-level name and will
apply throughout the workbook.

Delete the Workbook-level name, and create the Worksheet-level name
instead.

The easiest way to create a sheet level name is to select your cell,
then enter Sheet1!myName in the Name box at the left of the Formula
bar. Alternatively, you can create it in the Insert/Name/Define
dialog.

This Worksheet-level name will only be accessible from the
Worksheet. It should be created before you copy the worksheet.

It's possible to have Workbook-level and Worksheet-level names that
have the same name.

Sheet1! has nothing to do with VBA (and is not a valid VBA
construct).
 
And just to add to the discussion:

I like to use Jan Karel Pieterse's (with Charles Williams and Matthew Henson)
utility "Name Manager.xla" from
http://www.bmsltd.co.uk/mvp/

It really makes life a lot simpler.

There's even a button that will "localise" or "globalize" an existing name.


====
And I'm not sure what you meant by "worksheet-level names will only be
accessible from the worksheet".

I can refer to those names if I use the whole name:

=Sheet1!testme
or
=Sheet2!testme
 
I started from scratch, deleting the name i wanted to use.
I re-created it this way: Sheet1!myName. Then copied the
worksheet to another one. It worked fine, however when i
tried to copy it in a second Spreadsheet i encoutered
problems as it told me that the name was already used and
it suggested that i chose another name.

Any suggestions?
 
Back
Top