Help! I am having a problem with sort!

[RobertD]

I have a strange problem when I do a sort on data which has other sheets
linked to it. When I sort the data, the other sheets seemed locked to the
cell rather than the data.

For example, if sheet1 contains cell A1 with the formula ='sheet2'!B2 and I
do a sort of sheet2, what shows up in A1 is not the original data that was in
sheet2!B2. What shows up is the new data in sheet2, B2.

I tried putting =sheet2!$B$2 in the A1 cell in sheet1, but it doesn't make
any difference, I get exactly the same result.

However, if I cut and paste cell B2 in sheet2, then sheet1 A1 follows
sheet2B2 anywhere I put it.

How can I keep cell A1 pointing to the data in B2 through a sort?

Thanks,

RobertD

 

[Hank]

Hi Bobert,

I have a solution for you, since the link is to value of cell, not cell
itself. So you may need to have a column act as a index in sheet2, and in
sheet1 you have a same index, when you sort data in sheet2, include index
column, but not sort this column, and use vlookup in sheet1 to find data.
then whatever how you sort in sheet2, sheet1 is always point the data in
sheet2 by its index. here it is the example.

sheet2 data
column A (index) column B
1 aaa
2 bbb
3 ccc
4 ddd

sheet1 data
column A (index) column B
1 =VLOOKUP(A1,Sheet2!A:B,2,0)
2 =VLOOKUP(A2,Sheet2!A:B,2,0)
3 =VLOOKUP(A3,Sheet2!A:B,2,0)
4 =VLOOKUP(A4,Sheet2!A:B,2,0)

So when you sort data in sheet2 columnB, include columnA as well, so link in
sheet1 will point that data wherever it goes.

Hope it works for you
Hank

 

[RobertD]

Thanks Hank, I will give this a try. What you wrote seems to make sense.

RobertD