Named Range Returns Wrong Range

[Snowman58]

OK, I have a huge SS with lots of named ranges. Many of which are dynamically assigned lists.

All of the existing ones work fine, but when try to add a new named range, it returns the cells from a previously named range. (Always the same old one.)

example:

A range (one of many) is named "Shift_List" and is defined using
=OFFSET(info!$C$3, 0, 0, COUNTA(info!$C$3:$C$2000),1)

New range is created named "PN_List" and is defines using
=OFFSET(info!AA$3, 0, 0, COUNTA(info!AA$3:AA$2000),1)

When I create a Data Validation List or otherwise use "Shift_List" as the source it works fine.

However if I do the same thing and refer to "PN_List", it returns the items from "Shift_List"

Any new named range returns the Shift_List cells, although older ones still work correctly.

Anyone have any thoughts on the cause??

 

[skywriter]

Without seeing your workbook I would check my Name Manager and sort by names first and see if I have more than one and would check the scope of these names and try to find a correlation between scope and where I'm using these ranges in validation or formulas.

 

[Snowman58]

I have indeed used the Name Manager to check the names. No Duplicates.
If I use Name Manager to edit the range, the dotted outline is around the correct cells.
However when I use the range in VBA or a formula, they are wrong.

It's like the internal pointer have been corrupted.

 

[Snowman58]

OK, here is what I have found. In Name Manager, the reference addresses changed over time for no apparent reason. Very Very strange.
The column reference was not absolute. I changed it to an absolute reference and the issue seems to be solved.

But I have never before seen a named range reference change for no reason.

 

[skywriter]

Very Very strange.

No, very very very strange, but glad you figured it out.

I'll put that in the back of my head and if that ever happens to me I'll hope to remember this.

 

[tusharm]

The new name you are creating is using relative cell addresses -- well, technically a combined relative-absolute address (as in AA$3).

So, the behavior of Excel is *as expected.* When you use a relative address in a named reference, the reference is relative to the current location. So, as the current location changes so does the named reference.

This is all documented. Check Excel's documentation of search google/bing.

OK, I have a huge SS with lots of named ranges. Many of which are dynamically assigned lists.

All of the existing ones work fine, but when try to add a new named range, it returns the cells from a previously named range. (Always the same old one.)

example:

A range (one of many) is named "Shift_List" and is defined using
=OFFSET(info!$C$3, 0, 0, COUNTA(info!$C$3:$C$2000),1)

New range is created named "PN_List" and is defines using
=OFFSET(info!AA$3, 0, 0, COUNTA(info!AA$3:AA$2000),1)

When I create a Data Validation List or otherwise use "Shift_List" as the source it works fine.

However if I do the same thing and refer to "PN_List", it returns the items from "Shift_List"

Any new named range returns the Shift_List cells, although older ones still work correctly.

Anyone have any thoughts on the cause??

 

[skywriter]

The new name you are creating is using relative cell addresses -- well, technically a combined relative-absolute address (as in AA$3).

So, the behavior of Excel is *as expected.* When you use a relative address in a named reference, the reference is relative to the current location. So, as the current location changes so does the named reference.

This is all documented. Check Excel's documentation of search google/bing.

You know I should've known that, but I went brain dead. I read about named formulas in a book and it gave me some ideas for some relative referenced ranges that I created, and actually have implemented in a spreadsheet, but I forgot all about them.

Well thanks to both of you, I'm sure this knowledge will be closer to the front of my brain now.

Thanks.

 

[Snowman58]

I've been using Excel for 30 years and this is the first time I have come across this problem. I would claim it was the first time I have made a mistake in assigning ranges to names, but that would be wrong. It's at least the third time......

 

[tusharm]

It's a documented feature. Just because you haven't used the capability for 30 years doesn't make it a problem. I've used this technique effectively when it's been appropriate to a business model that I'm developing.

I've been using Excel for 30 years and this is the first time I have come across this problem. I would claim it was the first time I have made a mistake in assigning ranges to names, but that would be wrong. It's at least the third time......