Correct argument for multiple supplliers in a po form

[Vicdv]

I am working on a purchase order form that have multiple suppliers and different prices. I already searched the forum related to what i'm doing but it seems I can't find similar case. I used data validation for my pick list and basic vlookup function to display the item and corresponding price and it works just fine for only one supplier, since we have multiple suppliers with diffrent materials and cost, What i want to do is when i select a particular supplier it would display the materials that they serve and pick an item from that list and will automatically display the price.

 

[pjmorris]

Hi Vic,

You've set yourself an entirely possible challenge, but its not easy!

I think its a two/three step process:

1. Create a unique list of the suppliers. This formula, entered with CTRL-SHFT-ENTER rather than simply Enter, should be entered into cell E2:
=IFERROR(INDEX($A$2:$A$31,MATCH(0,COUNTIF($E$1:$E1,$A$2:$A$31),0)),"") will extract the unique list from your list of products and suppliers. Cells $A$1:$A$31 have the names of the suppliers. The cell reference $E$1:$E1 should be the cell above the top of the extracted list of suppliers. So if you wanted the list starting at cell H7 then it would become $H$6:$H6.

2. Create a list of products extracted from the list of products and suppliers. This formula, again entered with CTRL-SHFT-ENTER, should be entered into cell F2:
=IFERROR(INDEX($B$2:$B$31, MATCH(0, IF($I$2=$A$2:$A$31, COUNTIF($F$1:$F1, $B$2:$B$31), ""), 0)),"") the information in column B would be the product list, cell I2 has the name of the supplier on the Purchase Order form.

3. In order to create dropdown validation lists you could create a named range using the following formula:
=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$29)-COUNTBLANK(Sheet1!$F$2:$F$29)) F2 refers to the top of the extracted list of products, $F$2:$F$29 is the range through which you copied the formal create at step 2 above. It is important to ensure that the two ranges match - it will fail otherwise because the COUNT functions get confused by formulas in cells!

You may well need to play with these to get them to work to match your exact layout.

Good luck and hope this helps.

Regards

 

[Vicdv]

I am working on a purchase order form that have multiple suppliers and different prices. I already searched the forum related to what i'm doing but it seems I can't find similar case. I used data validation for my pick list and basic vlookup function to display the item and corresponding price and it works just fine for only one supplier, since we have multiple suppliers with diffrent materials and cost, What i want to do is when i select a particular supplier it would display the materials that they serve and pick an item from that list and will automatically display the price.

https://ibb.co/mZ8XDk

 

[Vicdv]

here's the example of my suppliers item list

[/IMG]

thank you for the reply

 

[Vicdv]

this is the example of my suppliers item list and price.

[/IMG]

 

[Vicdv]

Thank you pjmorris for the reply. I just posted the example of my suppliers item list and price.

Hi Vic,

You've set yourself an entirely possible challenge, but its not easy!

I think its a two/three step process:

1. Create a unique list of the suppliers. This formula, entered with CTRL-SHFT-ENTER rather than simply Enter, should be entered into cell E2:
=IFERROR(INDEX($A$2:$A$31,MATCH(0,COUNTIF($E$1:$E1,$A$2:$A$31),0)),"") will extract the unique list from your list of products and suppliers. Cells $A$1:$A$31 have the names of the suppliers. The cell reference $E$1:$E1 should be the cell above the top of the extracted list of suppliers. So if you wanted the list starting at cell H7 then it would become $H$6:$H6.

2. Create a list of products extracted from the list of products and suppliers. This formula, again entered with CTRL-SHFT-ENTER, should be entered into cell F2:
=IFERROR(INDEX($B$2:$B$31, MATCH(0, IF($I$2=$A$2:$A$31, COUNTIF($F$1:$F1, $B$2:$B$31), ""), 0)),"") the information in column B would be the product list, cell I2 has the name of the supplier on the Purchase Order form.

3. In order to create dropdown validation lists you could create a named range using the following formula:
=OFFSET(Sheet1!$F$2,0,0,COUNTA(Sheet1!$F$2:$F$29)-COUNTBLANK(Sheet1!$F$2:$F$29)) F2 refers to the top of the extracted list of products, $F$2:$F$29 is the range through which you copied the formal create at step 2 above. It is important to ensure that the two ranges match - it will fail otherwise because the COUNT functions get confused by formulas in cells!

You may well need to play with these to get them to work to match your exact layout.

Good luck and hope this helps.

Regards

 

[Stiuart_W]

You do realise this is a public forum? If those aren't garbage prices they really shouldn't be there

 

[Vicdv]

You do realise
this is a public forum? If those aren't garbage prices they really shouldn't be there

It was a file from previous suppliers and they aren't existing anymore and besides i didn't include the name.