+ Reply to Thread
Results 1 to 7 of 7

Defining Static Names

  1. #1
    Registered User
    Join Date
    10-23-2008
    Location
    Canada
    Posts
    25

    Defining Static Names

    I'm surprised this topic hasn't been posted before (or maybe my searches didn't hit them), but it's fairly simple:

    I've defined a number of rows in a named range.

    When I insert or delete a row within that range, I find that excel automatically adjusts the named range to include/remove the cells I insert/delete.

    I DO NOT want this. I wan't the reference to my named range to REMAIN THE SAME no matter how many rows I insert or delete.

    I am not using code to define these names.

    Thanks,

    Jerry

  2. #2
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677
    You can define it like this:

    myRange Refers to: =INDEX(Sheet1!$1:$65536, 2, 2):INDEX(Sheet1!$1:$65536, 5, 6)
    Entia non sunt multiplicanda sine necessitate

  3. #3
    Registered User
    Join Date
    10-23-2008
    Location
    Canada
    Posts
    25
    Hi Shg,

    I'm afraid I have no idea what that code is supposed to do... although I tried it and it does successfully box in a range.

    Can you give a few details about what modifying those numbers will do?

  4. #4
    Registered User
    Join Date
    10-23-2008
    Location
    Canada
    Posts
    25
    Nevermind, figured it out.

    However, the code doesn't work. When I delete rows specified by the range, the row reference numbers still change.... Maybe I'm using your code wrong. Here is a sample range:

    =INDEX('Valid Access'!$5:$5, 1, 2):INDEX('Valid Access'!$2004:$2004, 1, 2)

    This code defines a name that boxes in B:5 to B:2004.

    I simply kept changing numbers untill the area I wanted was boxed in... so its quite possible that I'm using your technique incorrectly
    Last edited by jerryliang2k; 12-22-2008 at 06:05 PM.

  5. #5
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677
    Insert > Name > Define myRange Refers to: =INDEX(Sheet1!$1:$65536, 2, 2):INDEX(Sheet1!$1:$65536, 5, 6) So myRange is B2:F5 and will never change.

    Then reference myRange in your formulas; e.g., =COUNT(myRange)

  6. #6
    Registered User
    Join Date
    10-23-2008
    Location
    Canada
    Posts
    25
    but if you delete 65536 rows from your worksheet (not neccessarily at once), the reference will break because excel will automatically readjust the named range to:

    =INDEX(Sheet1!$1:$1, 2, 2):INDEX(Sheet1!$1:$1, 5, 6)

  7. #7
    Forum Guru shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2003, 2010
    Posts
    40,677
    but if you delete 65536 rows from your worksheet (not neccessarily at once),
    You'll kill the definition if you delete all rows at the same time. Do you do that a lot?

    It's possible to cirumvent that, using INDIRECT and OFFSET, but the result would be volatile, which would cause needless recalculation.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1