Formula Help

[bloodybrit90]

Hi all,

Can anyone help a noob out?

I have a list of part numbers in column A in sheet 1 and different vendor pricing in columns C-G. Here is what I have for sheet 2.

<table border="0" cellpadding="0" cellspacing="0" width="488"><col style="width: 75pt;" width="100"> <col style="width: 91pt;" width="121"> <col style="width: 56pt;" width="74"> <col style="width: 64pt;" width="85"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl74" style="height: 15.75pt; width: 75pt;" width="100" height="21">Item #</td> <td class="xl74" style="width: 91pt;" width="121">Quantity</td> <td class="xl75" style="width: 56pt;" width="74">Vendor 1</td> <td class="xl75" style="border-left: medium none; width: 64pt;" width="85">Vendor 2</td> <td class="xl75" style="border-left: medium none; width: 81pt;" width="108">Vendor 3</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" height="20">45623</td> <td class="xl71">5</td> <td class="xl72">
</td> <td class="xl72">
</td> <td class="xl72">
</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl71" style="height: 15pt;" height="20">45856</td> <td class="xl71">2</td> <td class="xl73">
</td> <td class="xl73">
</td> <td class="xl73">
</td> </tr> </tbody></table>
under vendor 1 I would like it to display the price (sheet 1 C:C)for the part number (sheet 2! A2) times the quantity (sheet 2 B2). The prices are in sheet 1.

This is what I have so far.

=if('Sheet 1'!A:A,"='Sheet 2'!A2",

I need the formula to sate =if(an item # in sheet 1 "=item # sheet 2 A2", then multiply column c in same row that it found the item number in sheet 1 by sheet 2 B2)

How would I type this? Cheers.




<table border="0" cellpadding="0" cellspacing="0" width="488"><col style="width: 75pt;" width="100"> <col style="width: 91pt;" width="121"> <col style="width: 56pt;" width="74"> <col style="width: 64pt;" width="85"> <col style="width: 81pt;" width="108"> <tbody><tr style="height: 15.75pt;" height="21"> <td class="xl74" style="height: 15.75pt; width: 75pt;" width="100" height="21">
</td><td class="xl74" style="width: 91pt;" width="121">
</td><td class="xl75" style="width: 56pt;" width="74">
</td><td class="xl75" style="border-left: medium none; width: 64pt;" width="85">
</td><td class="xl75" style="border-left: medium none; width: 81pt;" width="108">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl71" style="height: 15pt;" height="20">
</td><td class="xl71">
</td><td class="xl72">
</td><td class="xl72">
</td><td class="xl72">
</td></tr><tr style="height: 15pt;" height="20"><td class="xl71" style="height: 15pt;" height="20">
</td><td class="xl71">
</td><td class="xl73">
</td><td class="xl73">
</td><td class="xl73">
</td></tr></tbody></table>

 

[bloodybrit90]

Bump

 

[markmzz]

Try this:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Number</td><td style="font-weight: bold;text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Vendor 1</td><td style="font-weight: bold;text-align: center;;">Vendor 2</td><td style="font-weight: bold;text-align: center;;">Vendor 3</td><td style="font-weight: bold;text-align: center;;">Vendor 4</td><td style="font-weight: bold;text-align: center;;">Vendor 5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">45623</td><td style="text-align: center;;"></td><td style="text-align: center;;">654</td><td style="text-align: center;;">759</td><td style="text-align: center;;">422</td><td style="text-align: center;;">449</td><td style="text-align: center;;">333</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">46089</td><td style="text-align: center;;"></td><td style="text-align: center;;">717</td><td style="text-align: center;;">730</td><td style="text-align: center;;">267</td><td style="text-align: center;;">352</td><td style="text-align: center;;">430</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">46322</td><td style="text-align: center;;"></td><td style="text-align: center;;">562</td><td style="text-align: center;;">365</td><td style="text-align: center;;">530</td><td style="text-align: center;;">704</td><td style="text-align: center;;">251</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">45856</td><td style="text-align: center;;"></td><td style="text-align: center;;">516</td><td style="text-align: center;;">262</td><td style="text-align: center;;">216</td><td style="text-align: center;;">612</td><td style="text-align: center;;">693</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="font-weight: bold;text-align: center;;">Number</td><td style="font-weight: bold;text-align: center;;">Quantity</td><td style="font-weight: bold;text-align: center;;">Vendor 1</td><td style="font-weight: bold;text-align: center;;">Vendor 2</td><td style="font-weight: bold;text-align: center;;">Vendor 3</td><td style="font-weight: bold;text-align: center;;">Vendor 4</td><td style="font-weight: bold;text-align: center;;">Vendor 5</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">45623</td><td style="text-align: center;;">5</td><td style="text-align: right;;">3,270</td><td style="text-align: right;;">3,795</td><td style="text-align: right;;">2,110</td><td style="text-align: right;;">2,245</td><td style="text-align: right;;">1,665</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">45856</td><td style="text-align: center;;">2</td><td style="text-align: right;;">1,032</td><td style="text-align: right;;">524</td><td style="text-align: right;;">432</td><td style="text-align: right;;">1,224</td><td style="text-align: right;;">1,386</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">46089</td><td style="text-align: center;;">3</td><td style="text-align: right;;">2,151</td><td style="text-align: right;;">2,190</td><td style="text-align: right;;">801</td><td style="text-align: right;;">1,056</td><td style="text-align: right;;">1,290</td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet2</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">C2</th><td style="text-align:left">=INDEX(<font color="Blue">Sheet1!$C$2:$G$5,MATCH(<font color="Red">$A2,Sheet1!$A$2:$A$5,0</font>),MATCH(<font color="Red">C$1,Sheet1!$C$1:$G$1,0</font>)</font>)*$B2</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[Marcelo Branco]

Hi,

Assuming your data in Sheet1 like (headers in row 1)

A B C

<TABLE style="WIDTH: 144pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=192><COLGROUP><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 width=64>Item #</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Vendor</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Price</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45623</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45856</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>12</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45623</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor2</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>14</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45856</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor1</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>16</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45623</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>18</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=20 align=right>45856</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65>Vendor3</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>20</TD></TR></TBODY></TABLE>


Try this formula in Sheet2 C2

=SUMPRODUCT(--(Sheet1!$A$2:$A$200=$A2),--(Sheet1!$B$2:$B$200=C$1),Sheet1!$C$2:$C$200)*$B2

copy across and down

HTH

M.

 

[bloodybrit90]

Thanks for the help.

 

[Marcelo Branco]

Thanks for the help.

You are welcome.

M.