Relationships / Calculations

[shone]

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

 

[Guest]

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

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


Copy down as required


HTH

 

[Guest]

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))

:

 

[shone]

@Toppers
Thank you, it worked like a charm
-----
One more question...

How to calculate interest in % (difference between two prices)?

// Example
I paid certain article $15 and I'm selling it for $18

 

[Guest]

Do you mean profit rather than interest?

profit=(Selling Price-Buying Price)/Buying Price

so formula would be:

=(B1-A1)/A1 where A1 is Buying Price, B1 is selling price

with your data:

($18-$15)/$15 = $3/$15 = 20% (format cell as %)

HTH