I have a work book that I'm trying to use to store supplier prices for 1-15 products over certain postal codes 1-99999999? currently set out like table below
The postcodes are in the B column from 35 down, the products are in 34 C across currently stopping at L but possibly more to be added
PAGE NAME (Supplier A)
I am using
=INDEX(Supplier A!$B$2:$AB$999,MATCH($D$3,Supplier A!$A$2:$A$999,0),MATCH($D$4,Supplier A!$B$1:$AB$1,0))
on a separate page to find the price of say a 6yd skip in kt1 the trouble I am having is, I'm adding more suppliers on separate sheets and I would like to pull up the cheapest (and possibly second cheapest) price for the size and postcode inputted in table below
PAGE NAME (PRICES)
I have tried some MIN variations with my limited knowledge and got warnings and pop ups so fluffed it pretty good. The prices on each supplier sheet will be on the same tables positions to make it uniform. ie c35:L999 on every sheet
I've tried to be as clear and concise as possible but if you need more info don't hesitate to ask
Thanks in advance for all your help.
The postcodes are in the B column from 35 down, the products are in 34 C across currently stopping at L but possibly more to be added
PAGE NAME (Supplier A)
33 | B | C | D | E | F | G | H | I | J | K | L |
34 | 4YD | 6YD | 8YD | 8YD Enclosed | 10YD | 12YD | 12YD Enclosed | 14YD | 16YD | 16YD Enclosed | |
35 | KT1 | 185 | 215 | 255 | 270 | 305 | 325 | 335 | 380 | 410 | 440 |
36 | KT10 | 195 | 215 | 255 | 270 | 305 | 325 | 335 | 380 | 410 | 440 |
37 | KT12 | 195 | 215 | 255 | 270 | 305 | 325 | 335 | 380 | 410 | 440 |
38 | KT13 | 195 | 215 | 255 | 270 | 305 | 325 | 335 | 380 | 410 | 440 |
39 | KT14 | 195 | 215 | 255 | 270 | 305 | 325 | 335 | 380 | 410 | 440 |
I am using
=INDEX(Supplier A!$B$2:$AB$999,MATCH($D$3,Supplier A!$A$2:$A$999,0),MATCH($D$4,Supplier A!$B$1:$AB$1,0))
on a separate page to find the price of say a 6yd skip in kt1 the trouble I am having is, I'm adding more suppliers on separate sheets and I would like to pull up the cheapest (and possibly second cheapest) price for the size and postcode inputted in table below
PAGE NAME (PRICES)
inputted fields below | G | H | I | J | |||||
Skip prices | 2 | Primary | Supplier | secondary supplier | |||||
Post Code | KT1 (postcode in drop down box) | 3 | NAME | (would like cheapest supplier here) | NAME | (would like next cheapest supplier here) | |||
Type of Skip | 4YD (product in dropdown box) | 4 | PRICE | PRICE | |||||
Price to Customer is | 185 (above formula in here) |
I have tried some MIN variations with my limited knowledge and got warnings and pop ups so fluffed it pretty good. The prices on each supplier sheet will be on the same tables positions to make it uniform. ie c35:L999 on every sheet
I've tried to be as clear and concise as possible but if you need more info don't hesitate to ask
Thanks in advance for all your help.