Naming a cell

  • Thread starter Thread starter janet_alexander
  • Start date Start date
J

janet_alexander

I have a workbook that contains several worksheets. I
named a cell in Worksheet A by using the "Insert - Name -
Define" function which gave the cell a specific reference
to Worksheet A. I named another cell in Worksheet B using
the same name and function. When I change the number in
the cell in Worksheet A, it changes on the specific
worksheet. However, when I change the number in the cell
in Worksheet B, it changes across all the worksheets. How
do I keep the name specific to the worksheet and not
global across all worksheets?
 
A simple way to name a range (or cell), and make it "worksheet specific"
(not seen by other sheets), is to select the range (cell), and click in the
name box.
Type the sheet name, followed by an asterisk, followed by the name of the
range (cell).

For example:

Sheet1!myrange

If you would go to Sheet2, you would not see this name listed in the name
box.
If you would do,
<Insert> <Name> <Define> while in Sheet2, that name would not be listed
there also.
If you went to <Insert> <Name> <Define> while in Sheet1, you would see the
name listed, and also the sheet name in the right side of the window.
--

HTH,

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


message I have a workbook that contains several worksheets. I
named a cell in Worksheet A by using the "Insert - Name -
Define" function which gave the cell a specific reference
to Worksheet A. I named another cell in Worksheet B using
the same name and function. When I change the number in
the cell in Worksheet A, it changes on the specific
worksheet. However, when I change the number in the cell
in Worksheet B, it changes across all the worksheets. How
do I keep the name specific to the worksheet and not
global across all worksheets?
 
"I named a cell in Worksheet A by using the "Insert - Name - Define"
function which gave the cell a specific reference to Worksheet A."

Well and good.

"I named another cell in Worksheet B using the same name and function."

....which overwrote, without warning, the original cell reference. Excel will
let you do that if you use Insert > Name > Define, but not if you try to
(re)define a name by typing it in the Name Box.

"When I change the number in the cell in Worksheet A, it changes on the
specific worksheet."

....because all you're doing is changing a value in a single cell.

"However, when I change the number in the cell in Worksheet B, it changes
across all the worksheets."

I'm guessing that you have linked cells, where the link formula references
the original cell by name.

"How do I keep the name specific to the worksheet and not global across all
worksheets?"

Use this syntax when defining names: Worksheetname!Rangename. Use it
whatever method you use to define a name. Names so defined will appear in
the Name Box dropdown (and in the Define Name dialog box) for their own
sheet only. Names defined without a sheet name will appear everywhere. You
can refer to a "local" name from any worksheet by using the syntax
=Worksheetname!Rangename. (On its own sheet only the name is necessary).
 
Back
Top