Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Taunton, Somerset, England
    Posts
    27
    Thanks
    5
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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 <font color=blue> 3-D reference</font color=blue> 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.
    - Ricky

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    See if This Thread answers your question.
    Legare Coleman

  4. #4
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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.
    -John ... I float in liquid gardens
    UTC -7ąDS

  5. #5
    Silver Lounger
    Join Date
    Mar 2001
    Location
    Dallas, Texas, USA
    Posts
    1,680
    Thanks
    0
    Thanked 1 Time in 1 Post

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

    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. <img src=/S/confused.gif border=0 alt=confused width=15 height=20>. Excuse me while I make my way back to the Puzzle Board..... <img src=/S/flee.gif border=0 alt=flee width=25 height=25>
    - Ricky

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    As already referenced earlier:

    <!post=This post,241906>This post<!/post>

    Describes that there are local and global names. Check out the entire thread for more information!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  7. #7
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

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

    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. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    New Lounger
    Join Date
    Dec 2002
    Location
    Dumfries, Virginia, USA
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •