ok it's not pretty, (it looks better in excel i swear!) but tell me if this is something like what you wanted:
In row 2 you fill in your order quantities. Rows 4-7 show the subtotals (qty x item price) for each supplier. If the order quantity is lower than the minimum, it shows "n/a". If the order field is blank, it stays blank. Column "i" shows the order total for each supplier. If there are any n/a's in that row, it shows n/a (i.e. You can't make that order from that supplier unless you increase quantity). Rows 11-14 are where you put your actual suppliers and prices.
The only thing left is to look at line i and see which is the cheapest total. I think the easiest way to do that would be conditional formatting --> color scales. (but play with the other conditional formatting options; data bars might be good too.)
excel 2010
| a | b | c | d | e | f | g | h | i | j | k | l |
---|
taps | pipes | fittings | (copy and paste these top two lines for your order) | | | | | | | | | |
order: | | | | | | | | | | | | |
total: | | | | | | | | | | | | |
suppler 1 | | | | | | | | | | | | |
supplier 2 | | | | | | | | | | | | |
supplier 3 | n/a | | n/a | | | | | | | | | |
supplier 4 | | | | | | | | | | | | |
| | | | | | | | | | | | |
| | | | | | | | | | | | |
supplier | tap price | tap qty | pipe price | pipe qty | fitting price | fitting qty | | | | | | |
supplier 1 | (fill in price info in this grid) | | | | | | | | | | | |
supplier 2 | | | | | | | | | | | | |
supplier 3 | | | | | | | | | | | | |
supplier 4 | | | | | | | | | | | | |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[td="align: Center"]1[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]2[/td]
[td="align: Right"][/td]
[td="align: Right"]1000[/td]
[td="align: Right"][/td]
[td="align: Right"]200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]3[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]4[/td]
[td="align: Right"][/td]
[td="align: Right"]3100[/td]
[td="align: Right"][/td]
[td="align: Right"]2000[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"]5100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]5[/td]
[td="align: Right"][/td]
[td="align: Right"]3300[/td]
[td="align: Right"][/td]
[td="align: Right"]2100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"]5400[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]6[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"]2200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]7[/td]
[td="align: Right"][/td]
[td="align: Right"]3200[/td]
[td="align: Right"][/td]
[td="align: Right"]2000[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"]5200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]8[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]9[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]10[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]11[/td]
[td="align: Right"]$3.10 [/td]
[td="align: Right"]1000[/td]
[td="align: Right"]$10.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]12[/td]
[td="align: Right"]$3.30 [/td]
[td="align: Right"]500[/td]
[td="align: Right"]$10.50 [/td]
[td="align: Right"]200[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]13[/td]
[td="align: Right"]$2.70 [/td]
[td="align: Right"]2000[/td]
[td="align: Right"]$11.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.00 [/td]
[td="align: Right"]200[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Center"]14[/td]
[td="align: Right"]$3.20 [/td]
[td="align: Right"]1000[/td]
[td="align: Right"]$10.00 [/td]
[td="align: Right"]100[/td]
[td="align: Right"]$5.50 [/td]
[td="align: Right"]100[/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
[td="align: Right"][/td]
</tbody>
sheet1
[table="width: 85%"]
<tbody>[tr]
[td]
worksheet formulas[table="width: 100%"]
<thead>[tr="bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]
[th="width: 10px"]cell[/th]
[th="align: Left"]formula[/th]
[/tr]
</thead><tbody>[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c4[/th]
[td="align: Left"]=if(
c$2>0,if(c$2>=c11,(c$2*b11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c5[/th]
[td="align: Left"]=if(
c$2>0,if(c$2>=c12,(c$2*b12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c6[/th]
[td="align: Left"]=if(
c$2>0,if(c$2>=c13,(c$2*b13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]c7[/th]
[td="align: Left"]=if(
c$2>0,if(c$2>=c14,(c$2*b14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e4[/th]
[td="align: Left"]=if(
e$2>0,if(e$2>=e11,(e$2*d11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e5[/th]
[td="align: Left"]=if(
e$2>0,if(e$2>=e12,(e$2*d12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e6[/th]
[td="align: Left"]=if(
e$2>0,if(e$2>=e13,(e$2*d13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]e7[/th]
[td="align: Left"]=if(
e$2>0,if(e$2>=e14,(e$2*d14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g4[/th]
[td="align: Left"]=if(
g$2>0,if(g$2>=g11,(g$2*f11),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g5[/th]
[td="align: Left"]=if(
g$2>0,if(g$2>=g12,(g$2*f12),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g6[/th]
[td="align: Left"]=if(
g$2>0,if(g$2>=g13,(g$2*f13),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]g7[/th]
[td="align: Left"]=if(
g$2>0,if(g$2>=g14,(g$2*f14),"n/a"),"")[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i4[/th]
[td="align: Left"]=if(
countif(c4:g4,"n/a"),"n/a",sum(c4:g4))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i5[/th]
[td="align: Left"]=if(
countif(c5:g5,"n/a"),"n/a",sum(c5:g5))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i6[/th]
[td="align: Left"]=if(
countif(c6:g6,"n/a"),"n/a",sum(c6:g6))[/td]
[/tr]
[tr]
[th="width: 10px, bgcolor: [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=dae7f5]#dae7f5[/URL] "]i7[/th]
[td="align: Left"]=if(
countif(c7:g7,"n/a"),"n/a",sum(c7:g7))[/td]
[/tr]
</tbody>[/table]
[/td]
[/tr]
</tbody>[/table]