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
--------------------------------------------------------------------------------
1001 BEER HEINEKEN 20 $5.00 $100.00
--------------------------------------------------------------------------------
1030 SHIRT NIKE 1 $15.00 $15.00
--------------------------------------------------------------------------------
.
.
.
.
.
.
--------------------------------------------------------------------------------
TOTAL $115.00


Thanks in advance

[Toppers]

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

in cell B2:
=If(iserror(vlookup(A2,Sheet2!A:D,2,False),"",vlookup(a2,Sheet2!A:D,2,False)
in cell C2:
=If(iserror(vlookup(A2,Sheet2!A:D,3,False),"",vlookup(a2,Sheet2!A:D,2,False)
in cell E2:
=If(iserror(vlookup(A2,Sheet2!A:D,4,False),"",vlookup(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
> --------------------------------------------------------------------------------
>

[Toppers]

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),"",vlookup(a2,Sheet2!A:D,2,False)
> in cell C2:
> =If(iserror(vlookup(A2,Sheet2!A:D,3,False),"",vlookup(a2,Sheet2!A:D,2,False)
> in cell E2:
> =If(iserror(vlookup(A2,Sheet2!A:D,4,False),"",vlookup(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
> > --------------------------------------------------------------------------------
> >

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

[Toppers]

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