After sorting NamedRange positions are broken!

Question

I've excel sheet where each cell is associated with a named range (say cell B5 is named as 'MyCell' under Named ranges). When I sort the column B, data in columns are moved but Named Ranges are NOT moved.

Because of this issue now namedranges are pointing to wrong cell. As far as my understanding named ranges should be moved along with data cell (similiar to the behavior when we insert new colum/rows).

Is this a known bug in Excel? Any workaround to fix it so that namedranges link to a cell will not be broken?

My environment: Excel 2007/ Win 7.

Thanks

Pervez

 

Answer 1

Pervez There are Three ways you define the address of the Named range. One is using "$" and the other is without the "$" and the third is a mix of both. For example

1. =Sheet1!$B$5
2. =Sheet1!B5
3. =Sheet1!B$5 OR =Sheet1!$B5

The $ is an indication to Excel that if it is used before a Column then that Column will remain a constant. Same goes for the row.

So in the 1st case, when you sort the data, Excel will always point to B5 as you are telling Excel to keep the address as a constant.

In the 2nd and the 3rd case, there is a bug. I believe it was reported some time ago, but I am not able to recollect when and by who. The bug can be reproduced like this.

• Create a named range and assign an address to it using the 2nd or the 3rd way.
• Save it and 'Exit' the named range dialog box
• Click anywhere in the worksheet
• Open the named range dialog box again and you will see that the address has changed for that named range!

EDIT: Perhaps any Excel MVP can confirm the above? Cheers.

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 2

This is not a bug: its the way named ranges and formulas are supposed to work. ( A named range is actually a formula that is not stored in a cell)

The data is sorted but not the formula that references the data.

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 3

Thank you Charles :)

However the address of the named range can change to 'anything' by simply clicking an 'any' cell? If it changes after sorting then I can understand but simply by exiting the named range dialog box and clicking another cell and re-entering named range dialog box, it changes it to anything it wants... That I don't understand. Perhaps I am missing a point here?

EDIT

>>>The data is sorted but not the formula that references the data.

That's what I am referring to. It does change it in the 2nd and the 3rd scenario but not as expected.

EDIT

I think we both are referring to two different things? I am talking about the Named Range Address in the Named Range Dialog Box and I guess (I could be wrong) you are referring to the formula stored in the cell which of course doesn't get sorted as you rightly mentioned.

 

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 4

A fourth way of naming a range is to do it dynamically.  For example, =OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1) will give you all the data in column A if the data is contiguous.

Pervez, if you can figure out a way to dynamically define your named ranges you won't have this problem.

 

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 5

Thanks Sid and Eric for the reply. My problem is still NOT solved. I'm refering the cell reference as #1 (like =Sheet1!$B$5) so that when user inserts a new row or column these reference will be automatically offseted like formulas. And also named range 'position' move to the new position. I'm fine with this.

Only issue is with sorting. I'm having separate named range for each individual cell. So, when I sort a column (as data moves I'm expecting the cell name should also move along with it) my functionality is broken. It looks to me a bu

None of the above option (including dynamic named ranges with offset) seems working fine for me. I do not have multiple cells associated with a named range (only one cell per named range). So, when I sort the column i'm expecting cell name (named range) also moved along the new position.

 

Thanks

- Pervez

Answer 6

And Pervez, that is what I was stating from the beginning :)

If you use OPTION ONE where you are using the $'s to make your row and column constant, the sorting won't have any effect on the named range. The address will still remain the same unless you insert a row or delete a row.

The only other option was to use OPTION THREE where you don't use the $ for the row. Some thing like this

=Sheet1!$B5

so that the column remains constant and the row changes after sorting. However like I mentioned above, the moment you remove the "$", Excel goes crazy. That address will change every time you click on a cell... leave aside sorting... and hence WHY I said that it is a bug :)

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 7

Yes, Sid .. I too feel this is a bug in Excel.

BTB I cannot use option#3 also.  I do not want either column or row ref to be fixed as it will break the functionality when user inserts new rows and/or columns. So, I need to go with option#1 itself with sorting supported. Any workarounds..?

Answer 8

Hi Sid,

A Named Range is actually a Named Formula. The Refersto of the Name is the formula. But a Named Range does not of itself have a location so when you make the refersto formula a relative formula it works relative to whatever cell the Name is being used in, or if you just look at the refersto its relative to whatever the active cell is.

If you define a relative name and then switch to R1C1 mode you will see that the refersto formula does NOT change when you select another cell: its also much easier to understand relative names in R1C1 mode.

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 9

Pervez, Here is an interesting article which might be what you need.

Topic: Excel Sort Command And Named Range Scramble Prevention

Link: http://www.eggheadcafe.com/tutorials/aspnet/28ff2b12-6318-40b5-96cc-a12f2f24610e/excel-sort-command-and-named-range-scramble-prevention.aspx

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 10

You are right Charles when you say

>>>if you just look at the refersto its relative to whatever the active cell is.

But that is while you are creating the Named range. The named range should not change it's  Refersto to any active cell once you exit the Name Manager. And that is my point. It keeps on changing the Refersto every time you select a different cell, even though you are not in the Name Manager. This only happens if you don't use a $.

 

Edit:

>>>If you define a relative name and then switch to R1C1 mode you will see that the refersto formula does NOT change when you select another cell: its also much easier to understand relative names in R1C1 mode.

Yes you are right again. However I am loosing the ability to keep my column constant and the row a "variable". The intention is that we want the row to change when I sort the data which we are not able to because of refersto changing every time, I select some cell, before I can even sort the data.

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 11

Pervez,

From what you've written, I think that you expect named ranges to work different than Excel intends them to work. If you want cell B5 to be the named range Freep, and you define Freep = $B$5 and then insert a column, Freep will be $C$5.  If you then sort column C, Freep is still $C$5.  The named range does not move with the sort.  This is not a bug, it is as intended.

 

 

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 12

Eric :)

No one is pointing here that it is a bug if you use $ in both column and row name :)

We are referring to a completely different scenario when you don't use "$" and the refersto changes after you have declared a named range and you try clicking anywhere in the sheet. :)

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 13

Named ranges use the absolute reference by definition.  If you don't, you are not properly defining the named range.  What you are describing is not a bug, but a user error.

---

HTH,

Eric

Tu ne cede malis sed contra audentior ito

Answer 14

>>Named ranges use the absolute reference by definition.

Very True Eric :)

>>>If you don't, you are not properly defining the named range.  What you are describing is not a bug, but a user error.

Then why does the name manager accept it and not give an error? :)

Try typing

"Abcd" in the RefersTo. It gives you an error message.

It shouldn't allow you in the first place ;-)

And hence I am referring to it as a bug since it is not behaving in the manner it is supposed to...

---

Sid (A good exercise for the Heart is to bend down and help another up)

Please do not email me your questions. I do not answer questions by email unless I get paid for it :) If you want, create a thread in Excel forum and email me the link and I will help you if I can.

Answer 15

Guys,

There is nothing wrong with using relative references in the Names Refersto formula: you just need to understand whats happening:

If you select cell B2 and define a name Fred=A1 you have defined a relative Name with a formula that refers to a cell one row higher and one column to the left. In R1C1 mode this is not =A1 but =R[-1]C[-1]

if you now select cell z5 the refersto for Fred will now be one row higher and one row to the left of Z5 : =Y4  BUT in R1C1 mode the formula has not changed, its still =R[-1]C[-1]

Internally Excel always works in R1C1 mode, it dynamically translates from R1C1 mode to A1 mode when you ask it to.

But none of this helps Pervez with his original request - its just (for good reasons) not the way excel works, so Pervez would need to use some VBA workaround (or lock the cells) if he really wants his Named Range (Named Formula) to follow the data rather than the cell reference.

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 16

Thanks Charles for the last point for the tip. How to lock the cell so that even when we sort it namedranges moves along with the data (and at the same time refersto field in the namedrange should also offset when we insert new rows and columns)?

Answer 17

Thanks Sid. Very interesting article indeed!. I cannot underrstand why Microsoft does not say it as bug! When we sort it move ALL the attributes (including xl comments). So, it should definitely move the 'names' of cells also (here referred to as namedrange). May be named ranges are internal representation of a formula, but the bottom line is that we are 'naming' a cell with namedrange. Then, named ranges associated with a cell should also move along with the sort.

Answer 18

Pervez,

the point is that Excel's defined names are NOT attributes of cells or ranges: they are a completely separate set of named formulas which contain executable statements.

One somewhat complicated way to do what you want just using formulae is to add a column (indexcolno) to the data to contain an index (1 to N where N is the number of rows).

Then make your names for the cells contain a formula like =INDEX(Data,indexrownum,indexColno)

Then you just have to make sure that when your data is sorted the index column is sorted together with the data

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 19

Aaargh.... Can we stop pretending that this is not an awful implementation choice (aka "design bug") that punishes those who thought they were making their spreadsheets a little more foolproof by using names.... 

Answer 20

I think most of the problem is because people keep talking about "Named Ranges" as though the Names were names for the cells, which of course they are not.

For most purposes the current design of Defined Names having a refersto formula works well.

But for something like sorting like Pervez wants you would need an alternative and completely different implementation where the cell name is a property of the cell.

So if you had 1,4,3 in cells A1:A3 with cell names CellOne CellFour CellThree and in cells D1:D3 you had formulas =CellOne =CellFour and =CellThree and then you sorted cells A1:A3 the data in A1:A3 would get sorted, along with the cell names, BUT Cells D1:D3 would still show the original order.

To make all this work Excel would need to have both kinds of implementation (and the opportunities for errors would probably increase).

---

Charles Excel MVP The Excel Calculation Site http://www.decisionmodels.com/

Answer 21

Charles,

Would you be able to post a screen shot of the solution your referencing when you say "One somewhat complicated way to do what you want just using formulae is to add a column (indexcolno)..."?