Relationships / Calculations
 

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

Relationships / Calculations
 

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

Relationships / Calculations
 

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

Relationships / Calculations
 

@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.


--
shone
------------------------------------------------------------------------
shone's Profile: http://www.excelforum.com/member.php...o&userid=35126
View this thread: http://www.excelforum.com/showthread...hreadid=548842

Relationships / Calculations
 

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

"shone" wrote:


@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.


--
shone
------------------------------------------------------------------------
shone's Profile: http://www.excelforum.com/member.php...o&userid=35126
View this thread: http://www.excelforum.com/showthread...hreadid=548842