data sort while maintining reference with cell in different worksh

[Jackie]

I welcome any suggestions. I have two worksheets in the same workbook.
Called:
1. "Parts Price"
2. "Catalogue"

One column in "Catalogue" directly reflects the value in a column in the
"Parts Price" worksheet. Currently I have simply used the formula eg
"='Parts Price'!G239" and so on for several hundred entries.

However I need to sort the rows in "Parts Price". The above works ok if I
cut and paste rows. However I do really need to utilise data sort of rows in
"Parts Price". However when I do that, the cell reference in "Catalogue"
remains with G239 rather than go to the new location in "Parts Price".

How can I do a data sort so that the link in "Catalogue" moves with the
information in "Parts Price"? I hope that this is kind of clear. Many
thanks for any assistance.
Jackie

 

[Jacob Skaria]

Try the formula VLOOKUP(); which will lookup the value in the other sheet
based on a index value. VLOOKUP stands for vertical lookup and search for a
value in the first column of a table array and returns a value in the same
row from another column in the table array. Try the below formulas with a
similar data and try changing the name in Cell C1. Once you are able to try
this you can easily convert your formulas to VLOOKUP...

ColA ColB ColC ColD ColE ColF
Name Age Sex Adam =formula1 =formula2
Adam 32 M
Marie 24 F
Francis 33 M
Joel 34 M
Susan 26 F
- - -
- - -
- - -
- - -

in place of formula1
=VLOOKUP($D$2,$A$2:$C$10,2,FALSE)

in place of formula2
=VLOOKUP($D$2,$A$2:$C$10,3,FALSE)

Since you are referring to different sheet you can use
=VLOOKUP($D$2,Sheet1!$A$2:$C$10,3,FALSE)


If this post helps click Yes