Retain cell address when sorting list

Question

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

Answer 1

Perhaps the VLOOKUP worksheet function does what you need? See http://office.microsoft.com/en-us/excel/HP052093351033.aspx

Answer 2

There's a very easy solution. Just used an absolute reference, e.g. =$B3 or =B$3 or =$B$3

If I user a simple cell reference to "=B3", then this is invalid once the list is sorted.

Sorting won't invalidate these absolute references.

Answer 3

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.

Answer 4

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.