Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help! I am having a problem with sort!
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 -- www.woodpeckings.com |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help! I am having a problem with sort!
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" wrote: 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 -- www.woodpeckings.com |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Help! I am having a problem with sort!
Thanks Hank, I will give this a try. What you wrote seems to make sense.
RobertD -- www.woodpeckings.com "Hank" wrote: 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" wrote: 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 -- www.woodpeckings.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sort Problem | Excel Discussion (Misc queries) | |||
Sort problem - help! | Excel Discussion (Misc queries) | |||
Sort problem | Excel Discussion (Misc queries) | |||
A "sort" problem | Excel Discussion (Misc queries) | |||
Sort Problem | Excel Worksheet Functions |