link problem

[Guest]

dear all

table1 table
ref ref pr
1001 1001 $100
1002 1002 $1002
1003 1003 $1003
1004 1004 $100

table2 ref field is a link cell to table1 ref field. if i add in additional ref in between the list in table1 say between 1002 and 1003, there's 10022 as belo
table1
re
100
100
1002
100
100

as there's a change of ref in table1, i need to do a cell link again in table2 as it will not auto refresh. after the link, the prc value will no longer tie to the re
table
ref pr
1001 $1001
1002 $100
10022 $100
1003 $1004
100

my question. how to make the prc value tie to ref like follow using function or any method

ref pr
1001 $1001
1002 $100
10022
1003 $1003
1004 $100

pls help me

 

[Frank Kabel]

Hi
you may use VLOOKUP in your second sheet. e.g., enter the following in
B1 (the prc column)
=VLOOKUP(A1,'table1'!$A$1:$B$100,2,0)

--
Regards
Frank Kabel
Frankfurt, Germany

dear all,

table1 table2
ref ref prc
1001 1001 $1001
1002 1002 $1002
1003 1003 $1003
1004 1004 $1004

table2 ref field is a link cell to table1 ref field. if i add in

additional ref in between the list in table1 say between 1002 and 1003,
there's 10022 as below

table1
ref
1001
1002
10022
1003
1004

as there's a change of ref in table1, i need to do a cell link again

in table2 as it will not auto refresh. after the link, the prc value
will no longer tie to the ref

 

[Guest]

dear frank
i think u got me wrong. what i want is when there is additional ref no insert in between the list, the prc will not tie to the correct re

ref pr
2001 56
2002 236
2003 19

when insert a ref no in between the list like the follow using cell lin
ref pr
2001 56
20011 236
2002 19
2003

frank, u see the point? prc 2365 should tie to 2002 and not 20011. the ref is a cell link to another sheet which has no prc column. so i cannot use vlookup. remember the table1 without prc

pls hel

 

[Frank Kabel]

Hi
one question: You have linkes the ref number from a spearate sheet
(probably with something like the following in A1: ='sheet1'!A1). doese
this list (in which you insert a new row) contain other information?.
They don't you insert the new row in you list with ref's and prc's?

--
Regards
Frank Kabel
Frankfurt, Germany

dear frank,
i think u got me wrong. what i want is when there is additional ref

no insert in between the list, the prc will not tie to the correct ref

ref prc
2001 567
2002 2365
2003 190

when insert a ref no in between the list like the follow using cell link
ref prc
2001 567
20011 2365
2002 190
2003

frank, u see the point? prc 2365 should tie to 2002 and not 20011.

the ref is a cell link to another sheet which has no prc column. so i
cannot use vlookup. remember the table1 without prc?

 

[Guest]

frank
there are 2 sheets, sheet1 and sheet2. only sheet2 with price field and sheet1 with other field but no price field

sheet
ref nam
20 and
21 jimm
22 fion

sheet
ref pric
20 34
21 509
22 74

ref in sheet2 using cell link. i put = and click the first ref cell in sheet1 to get the link. let say i insert new ref in between in sheet1 as follow and do a link in sheet

sheet
ref nam
20 and
21 jimm
211 saya
22 fion

sheet
ref pric
20 34
21 509
211 74
2

u see the problem in sheet2? 748 should be 22 and not 211. so my problem is how to make the price tie to correct ref after the cell link on ref field in sheet2? bear in mind price field only maintain in sheet2. ref field in sheet2 must be the same as ref field in sheet1 that is why i use cell link. or you hv good idea of how to do this
thank

 

[Frank Kabel]

Hi
as you're doing it now I don't see a chance to achieve this. Excel is
not a database in which you can link datasets. In your case I would
strongly suggest to combine both sheets into one as this would make
this a lot simpler (and would be consistent with your data structure).
Sorry but I don't see a better - easy to use - solution

--
Regards
Frank Kabel
Frankfurt, Germany

frank,
there are 2 sheets, sheet1 and sheet2. only sheet2 with price field

and sheet1 with other field but no price field.

sheet1
ref name
20 andy
21 jimmy
22 fiona

sheet2
ref price
20 345
21 5095
22 748

ref in sheet2 using cell link. i put = and click the first ref cell

in sheet1 to get the link. let say i insert new ref in between in
sheet1 as follow and do a link in sheet2

sheet1
ref name
20 andy
21 jimmy
211 saya
22 fiona

sheet2
ref price
20 345
21 5095
211 748
22

u see the problem in sheet2? 748 should be 22 and not 211. so my

problem is how to make the price tie to correct ref after the cell link
on ref field in sheet2? bear in mind price field only maintain in
sheet2. ref field in sheet2 must be the same as ref field in sheet1
that is why i use cell link. or you hv good idea of how to do this?