Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex calculations | Excel Discussion (Misc queries) | |||
Calculations based on PivotTable information | Excel Discussion (Misc queries) | |||
looping through a set of calculations | Excel Worksheet Functions | |||
Calculations crossing multiple sheets | Excel Discussion (Misc queries) | |||
time interval calculations in excel | Excel Discussion (Misc queries) |