Copying cells from another worksheet

[Mark Rose]

Hi i have a worksheet that has part no, desciption and cost in then on the
other worksheet i have a list of items with part no, description, and cost
on workshhet one if i select the description i want it to fill the other
cells with the part number and cost . i have created a avaidation that gives
me a list box with the equipment in but how do i link it to the cells.

Thanks

Mark

 

[Arvi Laanemets]

Hi

At first, be sure that the key column (description) is leftmost in item
list, i.e. when you want part no to appear automatically when you select
description, then redesign items list, p.e. Discription, PartNo, Cost. I
assume the items list is in range Sheet1!A2:C100

Now, when on another sheet you have in some cell, p.e. B2, a value for
description, then:
PartNo=VLOOKUP($B2,Sheet1!$A$2:$C$100,2,FALSE)
Cost=VLOOKUP($B2,Sheet1!$A$2:$C$100,3,FALSE)

To avoid errors when p.e. description isn't selected jet, you can modify
formulas above:
PartNo=IF(ISERROR(VLOOKUP($B2,Sheet1!$A$2:$C$100,2,FALSE)),"",VLOOKUP($B2,Sh
eet1!$A$2:$C$100,2,FALSE))
Cost=IF(ISERROR(VLOOKUP($B2,Sheet1!$A$2:$C$100,3,FALSE)),"",VLOOKUP($B2,Shee
t1!$A$2:$C$100,3,FALSE))

When you are sure, there never is nonexistent (in items list) descriptions
on second sheet, then you can use shorter error check:
PartNo=IF($B2="","",VLOOKUP($B2,Sheet1!$A$2:$C$100,2,FALSE))
Cost=IF($B2="","",VLOOKUP($B2,Sheet1!$A$2:$C$100,3,FALSE))

 

[J.E. McGimpsey]

One way:

Assume on sheet1 you have Part No. in column A, description in
column B and Cost in column C.

Assume that in sheet2 you have validation lists in column A that
list descriptions, and you wish to have Part No. in column B and
Cost in column C. Then in Sheet2:

B2: =INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$B:$B,FALSE))
C2: =INDEX(Sheet1!$C:$C,MATCH($A2,Sheet1!$B:$B,FALSE))