Reputation: 45

Retain cell address when sorting list

Here is a reduced version of what I need. Essentially, we have a list of components and their status (imagine they have been tested). Component BBB has two sub-components, BBB1 and BBB2. I want their status to reflect that of the BBB component 'parent'. Easily done, apart from the list is very long, and I need to retain the option to sort it as I wish. If I user a simple cell reference to "=B3", then this is invalid once the list is sorted. Similarly with a named range as B3; the named range address is static and does not dynamically alter when the list is sorted.

I could fix this by having a 'static' BBB status somewhere that is not in the sort area, but this is inelegant, and I don't like inelegance!

Any ideas?

http://FileHost.JustFreeSpace.Com/158complist.xls

Upvotes: 0

Answers (6)

Rustom

Reputation: 1

If the cell reference is to a cell in another ROW, it will change after sorting even in spite of locking the cell reference with the $ sign.

Upvotes: 0

hmb

Reputation: 1

For references within the same row, use relative references. (This is not your question.)

For references to other rows, use VLOOKUP (with FALSE as the last parameter) if you can identify a unique key to each row. If there is not a natural key, construct a surrogate and make sure it is and remains unique. [I display the next value to use in the table header and use conditional formatting to highlight the non-uniques. This breaks quite easily, if you insert a copied row, for example.]

Absolute references won't work. They retain the exact cell reference - but after sorting this now points to whatever value was sorted into that position. I believe this is a bug in Excel, either in the code or in the specification. The behaviour one would expect is that the values in a tuple remain unchanged, even when that tuple is moved around by a sort. With absolute references, the value in the cell with the absolute reference changes, even if - or rather because - the reference itself remains the same.

Upvotes: 0

JustPlainBill

Reputation: 276

I'm not fully sure of your use and needs, but try this litte formula:

=ADDRESS(MATCH("BBB",A:A,0),1)

It will return the cell address where BBB sits. If you remove the ADDRESS portion of the formula, it will return the Row number.

It can also be modified to pull the Value in your 'Status' Column.

If you put this formula in the 'Status' column for rows BBB1 & BBB2, then it will update when BBB changes:

=INDEX(A:C,MATCH(LEFT(A2,3),A:A,0),2)

Let me know if I'm only warm, or if I got it.

Upvotes: 2

Andrew Scagnelli

Reputation: 1604

If you're just using =B3, on sorting Excel will update that on its own when you sort, as long as you don't sort using VBA. If you want to keep a handle on that cell, you may need to find it again manually or keep tabs on where it goes to as you move it around.

Upvotes: 0