Naming Cells across mult. sheets. (W2K, O2K.)

Topic

New Reply

Please can someone tell me how to name cells across multiple worksheets.
It’s the same cell in each worksheet, there are 52 sheets and I have a lot of cell names that haven’t copied across, for some reason.
I’ve tried following the Excel help pages, (doesn’t work for me), I can’t find it in SE Using Office 2000, and I’m going mad.
The trouble is, I only do this once a year and can’t remember what I’ve done before.

An example would be cell I62 to be named WTDBanking.
Thanks
Jim

Reply | Quote

Replies

Jim –

Excel will let you use a range name only once within a workbook. Even if that name is applied to a single cell. Imagine using that “name” in a formula and you will see that it must point to just one place.

Perhaps, you’re thinking of a 3-D reference within your formulas. I’m using Excel XP and the Excel Help on the subject is fairly straight forward. Give it a look and see if this is what you need.

Reply | Quote

Ricky, how do you mean that a range name can only point to one place? Here’s a range name I use which points to more than one cell. In this case, the ranges are all on one sheet, but they could also be 3D:

=Inter!$AS$37,Inter!$AZ$37,Inter!$BG$37,Inter!$AT$38:$AT$42,Inter!$BA$38:$BA$42,Inter!$BH$38:$BH$42,Inter!$AU$50,Inter!$BB$50,Inter!$BI$50

(There is a maximum length at, I think, 255 characters.)
Using a name defined like this will not work with a number of math operators, but it can be summed.

Reply | Quote

John, I’m sure I’ve made an error in the way I stated things – still learning this mid-western dialect. I understand that a name can be applied to a cell, a contiguous range of cells or a 3D range. But still, isn’t it true that a name can be used just once within a workbook?

I tried a simple test as follows:
1) In a new workbook, with Sheet1, Cell A1 selected, I applied the range name, test, then;
2) I selected Sheet2, Cell A1 and attempted to apply the same name, test, to that cell.

Excel would not accept it and simply returned me to Sheet1, Cell A1 as the selected cell. Then it occurred to me that if the same range name could be used multiple times, then any formula that used that range name as an argument would “get confused”.

Seemed reasonable, but it’s probably me who’s confused. . Excuse me while I make my way back to the Puzzle Board…..

Reply | Quote

As already referenced earlier:

This post

Describes that there are local and global names. Check out the entire thread for more information!

Reply | Quote

We were indeed meaning slightly different issues. Jan Karel has pointed you to an answer to your question. Seeing you’re from Missouri, I’ll give you the benefit of the doubt.

Reply | Quote

See if This Thread answers your question.

Reply | Quote

I saw someone who had this problem once. I know this is painstaking, but the best way is to make sure each cell that is referenced is a numerical value. Sometimes a space is accidentally placed in the cell making it a text value. Then, go through and make sure you are referencing the correct cell on each page.

Hope this helps.

JT.

Reply | Quote