reference links to cells don't move when original data sorted

Table A has some real values in it ...

Table A
| A | B
1 | apples | 8
2 | oranges | 9
3 | pears | 10

Table B contains refences to the cells in TableA ...

Table B
| A | B
101 | apples | =b1 (displays 8)
102 | oranges | =b2 (displays 9)
103 | pears | =b3 (displays 10)

I have decided that I want to sort table A by descending
value on column B. So I sort using Data-Sort-
DescendingOnColumnB and Table A now looks like this ...

Table A after sort
| A | B
1 | pears | 10
2 | oranges | 9
3 | apples | 8

However I do not want the displayed values in table B to
change (e.g. we still only have 8 apples). So for example
what I want is after the sort is for the reference in cell
B101 to be changed to =b3 so that the viewed value for
apples in the second table is still 8. However this does
not happen and I end up with a "corrupted" Table B which
looks like this ...

Table B
| A | B
101 | apples | =b1 (displays 10)
102 | oranges | =b2 (displays 9)
103 | pears | =b3 (displays 8)

Note that if instead we do the "sort" on TableA manually
using Cut and InsertCutCells, is correctly changes the
links in TableB and leaves the displayed values unchanged
as follows.

Table B
| A | B
101 | apples | =b3 (displays 8)
102 | oranges | =b2 (displays 9)
103 | pears | =b1 (displays 10)

I believe that this is the "correct" behaviour and I would
expect the sort to do the same. Can anybody tell how to
get the sort the do what I want.

Glenn Hill wrote:
I believe that this is the "correct" behaviour and I would
expect the sort to do the same. Can anybody tell how to
get the sort the do what I want.


You need to change the formulas in table B.
So B101 would be =VLOOKUP(A101,$A$1:$B$3,2,FALSE)
YOu can then copy this down to B102 and B103.

Sorting the table in A1:B3 will then not affect the result of the
formula (provided there are not duplications in column A)

Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

Thanks very much, Bill. I can see how this works and it is
a very clever solution. However I tried to pick a simple
example to illustrate a general problem. Your solution
solves the example but not the general problem.

Say Table B does not have column have a column 1 - in fact
it might not be a table at all. As an even simpler
example, say have a single reference to a value in Table
A, e.g. ...

Cell B201 : =b1

If we were use Cut and InsertCut cells to reorder table A,
the reference in B201 would change to point to a new
position for number of apples in table A. However if we
try using Date-Sort, B201 keeps the entry "=b1" which now
points to wrong value.

Any other suggestions?

wrote:
Any other suggestions?

No
There is no way to have direct cell references follow the data they
refer to when you sort. That is why you would need to use a LOOKUP or
equivalent function to find the data you want based on other cells that
are sorted with it.


Bill Manville
MVP - Microsoft Excel, Oxford, England
No email replies please - respond to newsgroup

That does seem a big weakness in the software. I
discovered this "problem" accidentally by doing a sort on
one worksheet which had lot of links to it from another
worksheet. It was some time later that I noticed that
there second worksheet was now completely "corrupted" and
had to be reworked. I would guess that I am not the only
Excel user to have been caught out by this.

But thanks very much for your time and advice, Bill. I
look into the use of of lookup functions in future as you
suggest.

Cheers
Glenn