Need advice on a new spreadsheet that can show me best price from a particular supplier

[Massivebarra]

hi,

I have multiple suppliers that I ask them to supply plumbing items that I choose at their best price.

My trouble is the suppliers are becoming more eg. 10 suppliers.

Each supplier can supply their 'own brand' of tap or Pipe

Each supplier may have a different QTY for what they are offering at price.

i want to be able to locate the best price for an item and then somehow order from the spreadsheet without revealing a competitors price to them

I hope this makes sense?

ITEM	SUPPLIER 1	QTY	SUPPLIER 2	QTY	SUPPLIER 3	QTY	SUPPLIER 4	QTY
TAP	$3.10	1000	$3:30	500	$2.70	2000	$3.20	1000
PIPE	$10.00	100	$10.50	200	$11.00	100	$10:00	100
CU	$30.50	50	$30.20	50	$31.00	50	$25.50	200
FITTING	$5.00	100	$5.00	100	$5.00	200	$5.50	100
GLUE	$3.70	60	$4.00	50	$3.70	100	$3.70	60
VENT	$8.10	100	$5.00	200	$8.10	100	$7.50	100

<tbody>
</tbody>

 

[Massivebarra]

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
1			taps		pipes		fittings	(copy and paste these top two lines for your order)
2	order:		1000		200
3									total:
4	suppler 1		3100		2000				5100
5	supplier 2		3300		2100				5400
6	supplier 3		n/a		2200				n/a
7	supplier 4		3200		2000				5200
8
9
10	supplier	tap price	tap qty	pipe price	pipe qty	fitting price	fitting qty
11	supplier 1	$3.10	1000	$10.00	100	$5.00	100	(fill in price info in this grid)
12	supplier 2	$3.30	500	$10.50	200	$5.00	100
13	supplier 3	$2.70	2000	$11.00	100	$5.00	200
14	supplier 4	$3.20	1000	$10.00	100	$5.50	100

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

sheet1​

worksheet formulas cell formula c4 =if(c$2>0,if(c$2>=c11,(c$2*b11),"n/a"),"") c5 =if(c$2>0,if(c$2>=c12,(c$2*b12),"n/a"),"") c6 =if(c$2>0,if(c$2>=c13,(c$2*b13),"n/a"),"") c7 =if(c$2>0,if(c$2>=c14,(c$2*b14),"n/a"),"") e4 =if(e$2>0,if(e$2>=e11,(e$2*d11),"n/a"),"") e5 =if(e$2>0,if(e$2>=e12,(e$2*d12),"n/a"),"") e6 =if(e$2>0,if(e$2>=e13,(e$2*d13),"n/a"),"") e7 =if(e$2>0,if(e$2>=e14,(e$2*d14),"n/a"),"") g4 =if(g$2>0,if(g$2>=g11,(g$2*f11),"n/a"),"") g5 =if(g$2>0,if(g$2>=g12,(g$2*f12),"n/a"),"") g6 =if(g$2>0,if(g$2>=g13,(g$2*f13),"n/a"),"") g7 =if(g$2>0,if(g$2>=g14,(g$2*f14),"n/a"),"") i4 =if(countif(c4:g4,"n/a"),"n/a",sum(c4:g4)) i5 =if(countif(c5:g5,"n/a"),"n/a",sum(c5:g5)) i6 =if(countif(c6:g6,"n/a"),"n/a",sum(c6:g6)) i7 =if(countif(c7:g7,"n/a"),"n/a",sum(c7:g7)) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

thanks lenny....you've helped me out alot. I may have more questions as i build this spreadsheet