Slight typo - missing brackets plus absolute references:

=If(iserror(vlookup($A2,Sheet2!$A:$D,2,False)),"", vlookup($a2,Sheet2!$A:$D,2,False))

=If(iserror(vlookup($A2,Sheet2!$A:$D,3,False)),"", vlookup($a2,Sheet2!$A:$D,2,False))

=If(iserror(vlookup($A2,Sheet2!$A:$D,4,False)),"", vlookup($a2,Sheet2!$A:$D,2,False))

"Toppers" wrote:


In Sheet1: (assuming your data starts in row 2)

in cell B2:
=If(iserror(vlookup(A2,Sheet2!A:D,2,False),"",vloo kup(a2,Sheet2!A:D,2,False)
in cell C2:
=If(iserror(vlookup(A2,Sheet2!A:D,3,False),"",vloo kup(a2,Sheet2!A:D,2,False)
in cell E2:
=If(iserror(vlookup(A2,Sheet2!A:D,4,False),"",vloo kup(a2,Sheet2!A:D,2,False)
inF2: =If (D2<0,D2*E2,"")


Copy down as required


HTH
"shone" wrote:


Hi all,

I have two sheets, first is for calculating invoice and other one is
for storing product details (simple database).

Sheet #1 contains following columns:
A: Product ID
B: Description
C: Manufacturer
D: Quantity
E: Price per unit
F: Subtotal

Sheet#2 contains folowing columns:
A: Product ID
B: Description
C: Manufacturer
D: Price per unit

What I need is when I type ID and QUANTITY of the product into the
sheet#1 Excel should automaticly fill the remaining fields (from
sheet#2) and do the calculations.


// Example:


ID DESC. MANUFACT. QUANT. PRICE/UNIT SUBTOTAL
--------------------------------------------------------------------------------
1001 BEER HEINEKEN 20 $5.00 $100.00
--------------------------------------------------------------------------------
1030 SHIRT NIKE 1 $15.00 $15.00
--------------------------------------------------------------------------------