Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
shone
 
Posts: n/a
Default 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
--------------------------------------------------------------------------------
  #2   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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
--------------------------------------------------------------------------------

  #3   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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
--------------------------------------------------------------------------------

  #4   Report Post  
Posted to microsoft.public.excel.misc
shone
 
Posts: n/a
Default 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

  #5   Report Post  
Posted to microsoft.public.excel.misc
Toppers
 
Posts: n/a
Default 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


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
complex calculations juliadesi Excel Discussion (Misc queries) 6 March 17th 06 02:14 PM
Calculations based on PivotTable information jerry Excel Discussion (Misc queries) 0 October 3rd 05 07:25 PM
looping through a set of calculations Anand Excel Worksheet Functions 2 September 14th 05 07:12 PM
Calculations crossing multiple sheets Stephen McArthu Excel Discussion (Misc queries) 4 June 6th 05 04:35 PM
time interval calculations in excel Krishna Excel Discussion (Misc queries) 6 April 8th 05 02:57 PM


All times are GMT +1. The time now is 07:47 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"