Linking in excel

[kunalb33]

Been assigned a project @ work and havent been able to fig it out for days now.
someone help

I have an excel file which contains a large list that consists of 3 types of customers
A
B
C

I have another spreadsheet which I have linked each A type customer to.

I am trying to sort the first spreadsheet and have the second linked spreadsheet update as I sort.
The sorting works just fine but the second file keeps the same links and does not move as I would like.

if row 1 on File B is linked to row 30 on File A
After sorting it still references row 30 .
But I want it to reference the old value that used to be on row 30 but now has moved to row 45 after sorting.

I know I havent explained this too well...but any input is appreciated.

Thanxs.

[AlfD]

Hi!

The following bit of juggling sorts data on Sheet2 when data on Sheet1 is sorted. Is this along the lines you need?

Enter on Sheet1 col A numbers from, say, 1 to 20 and in column B a miscellany of strings (I used A,B,C in random fashion.

Enter on Sheet2 column A the same numbers as for Sheet1 col A.
Now put in B1 ="Sheet1!B" & A1 and put in C1 =INDIRECT(B1).
Copy these down as far as you need.

Go back to Sheet1 and sort both of columns A & B (Select the occupied part of A & B and use Data > Sort) using Col B.
The table on Sheet2 should have sorted, too.

Come back if you don't understand what I'm doing or I don't understand what you are doing!

Alf

[kunalb33]

hey,
ure tip seems interesting & I tried it.
not sure how I can apply this to my problem

I have 2 separate file

File A

Name Code Value
aaa f 1000
bbb c 2000
ccc c 3000
ddd f 4000
eee c 5000

File B (Cells linked to File A)

Name Code Value
aaa f 1000
ddd f 4000


Each cell in FIle B is linked to File A.
I want to be able to sort File A by Code or any other field and still show the data I have in File B currently.
I have tried absolute , relative & mixed linking ...but nothing seems to work.
It almost seems like I need to apply some C,C++ style theory of pointers but i dont think excel supports that.
any IDEAS.

Thanks

[AlfD]

Hi!

My suggestion was simply to show a link can e established between the two lists which survives a "sort". If you did as I said, you would see that happen.

As for usefulness: you would no doubt like to carry the next column's data along with the sort.

Put some date in Sheet1 column C.
In Sheet2 D1 put =OFFSET(INDIRECT(B1),0,1).
This simply refers to the next cell on the right from the place where INDIRECT(B1) points.
Copy this down.
If now you sort the Sheet1 data, you should find your new column going with it.

Any more columns? Use =OFFSET(INDIRECT(B1),0,2) and so on. If you want to include data in the search which is in columns to the left of B, you can use a negative offset.

BTW: THe great virtue of an index column( col A on Sheet1) is that you can rewind back to the original order by sorting on that column. And the rest should follow with it.

Alf

[kunalb33]

Hey
thanks for the tip on using Indirect. Did not know you could do that in excel.
anyway....u have given me some ideas which I can try out for my project

thanks again
kb