Hi all,
I have created a cost/bid workbook for my office to use but I always run into issues and am forced to second guess my work. I've received help here from various users and admin and it has always been very helpful.
See attached workbook for reference and ignore any/all hidden sheets as they are not applicable.
Quick run-down of how workbook flows:
1) Customer sends RFQ with item descriptions they need and quantity.
2) These descriptions and quantities are entered in on the "Supplier Pricing" Sheet.
3) Based on Type/Material, the "Suggested Sourcing" sheet provides a list of Suppliers to go to and the list of items to send them taken from the "Supplier Pricing" sheet.
**1st Question: My original goal with the Suggested Sourcing sheet was to, instead of using a drop-down menu to select each material, to have a list of suppliers generated based ONLY on if they provide one or more of the items I need to price. Another way of saying it is I do not want to see any of the 300+ suppliers that do not offer at least 1 of the items I am looking for. I can be looking for 1-150+ items so will need to utilize columns instead of trying to utilize the space vertically to list different suppliers. It would go one step further to split my lists between 4 separate sheets. 1 for US - Distributors & 1 for US Manufacturers & 1 for UK - Distributors & 1 for UK - Manufacturers.
The goal here is to have it also provide the list of any/all items that supplier is capable of doing. Including the details for that line item as shown in my below referenced example sheet. I would use this to copy/paste that information into my emails to greatly expedite sending out RFQ's. It would save me a lot of time on larger quotes with 20-150+ line items.
I've added 1 quick example of what I mean on the sheet titled "DesiredSourcingLayout".
4) Once I have my pricing back from suppliers and entered on "Supplier Pricing" sheet, the "Bid Summary" sheet updates.
5) Bid Summary is based entirely around the best price per unit (Column O). Each other column is supposed to pull that columns applicable information from the "Supplier Pricing" sheet that matches that exact pricing.
**2nd Question: I am having trouble wrapping my head around how to write index/match to do this. I've tried countless variations that get more and more complex but it either always gives me an error, N/A, returns a blank (my IF formula), or some other error variation.
What is the proper index/match formula to accurately pull the data from the Supplier Pricing sheet? At one point I gave up and created helper columns on the Supplier Pricing sheet in columns BT through EP.
2nd/3rd Question: "Supplier Pricing" Helper Columns located in "DY-EP" - My goal with these is to have the information in Column B on Bid Summary sheet starting at B8 to update depending on if the supplier is offering an alternative item description or if they are not, in which case I want the item description requested from my customer (Column B on Supplier Pricing) to be pulled instead.
-This is really the same question as my 2nd question but I went 1 step further and created a table to try and pull the desired information from and still not working for me.
Side Issue: I haven't been able to figure out how to fix the "Update/Error" popups that happen when you first open the workbook. I haven't tried hard to look into it yet but if you know about these issues, any help would again be appreciated.
Any help that you can provide is greatly appreciated.
Thank you for your time.
Bookmarks