Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Jun 2003
    Location
    Surrey, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Name Range problems (Excel 2003)

    Hi,

    I posted yesterday re slow calculation performance after having gone to Excel 2003 from Excel XP. Upshot was, we had named ranges defined as columns and Excel XP only processes cells with something in them. Excel 2003 seems to process all cells in the range regardless of content. Result - greatly extended calculation time.

    Now, we are trying to edit the Name Ranges to meaningful limits (e.g. A1:A500), but we are finding that if/when we go back in to the range definition, they change. Excel seems to be moving the numbers around. We have even found that if we define a range whilst in 1 sheet of a workbook, it looks different when viewed within a different sheet.

    Does anyone know what is going on? We are at Office 2003 SP1.

    Ken.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Name Range problems (Excel 2003)

    "named Ranges" are actually "named formulas"

    The name may contain a sheet reference and a cell reference. If the sheet is not given, it will use the active sheet. If the cell reference is not absolute, it will be relative to the active cell.

    So if the active cell is B1 and you create a name:
    "Test"
    referes to:
    =Sheet!A1

    if you put the formula: in cell C1:
    =test
    it will return what is in Sheet1!B1 (1 cell to left relative!)

    If you look at the formula while in cell B1 the refers to will actually be:
    =Sheet1!B1.

    Steve

  3. #3
    New Lounger
    Join Date
    Jun 2003
    Location
    Surrey, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Name Range problems (Excel 2003)

    Steve,

    My fault - guilty.

    We have used absolutes (sheet1!A1:A500) - in fact all our named ranges are specified thus.

    My apologies if this omission confused anyone.

    Ken.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Excel Name Range problems (Excel 2003)

    Sheet1!A1:A500
    is relative not absolute (which is "relative to the active cell")

    Absolute would be
    Sheet1!$A$1:$A$500

    Steve

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

    Re: Excel Name Range problems (Excel 2003)

    Is this slow recalc seen every time?

    I seem to recall, that when Excel 2003 opens a file from an older version, the first time it will rebuild the calculation tree, causing the first recalc to be extremely slow. Subsequent calcs should do better though.

    BTW: I'd recommend using my Name manager to work with defined names.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    New Lounger
    Join Date
    Jun 2003
    Location
    Surrey, England
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Name Range problems (Excel 2003)

    Steve,

    Looks like you are right <img src=/S/smile.gif border=0 alt=smile width=15 height=15>

    Whilst we thought we have enough "$"'s in the references, it seems we were still missing some. Putting "$" in everywhere we could has fixed the ranges as intended.

    Many thanks for help with this - "user error"......

    Ken.

Posting Permissions

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