Defining Static Names

**jerryliang2k** · 12-22-2008, 04:45 PM

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

**shg** · 12-22-2008, 04:49 PM

You can define it like this:

myRange Refers to: =INDEX(Sheet1!$1:$65536, 2, 2):INDEX(Sheet1!$1:$65536, 5, 6)

**jerryliang2k** · 12-22-2008, 05:46 PM

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?

**jerryliang2k** · 12-22-2008, 06:01 PM

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

**shg** · 12-22-2008, 06:18 PM

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)

**jerryliang2k** · 12-22-2008, 06:25 PM

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)

**shg** · 12-22-2008, 06:34 PM

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.

Defining Static Names

LinkBack

Thread Tools

Rate This Thread

Display

Defining Static Names

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions