Complex Index/match formula to pull data based on 1 criteria. (Bid/Cost Sheet)
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.
Last edited by Blackmagic42; 07-18-2019 at 08:41 AM.
Reason: Attempting to make title clearer.
Re: Complex Index Match Trouble Shooting
We would very much like to help you with your query, however the thread title does not really convey what your request is about.
Please take a moment to amend your thread title. Make sure that the title properly explains your request. Your title should be explicit and not be generic (this includes function names used without an indication of what you are trying to achieve).
Please see Forum Rule #1 about proper thread titles and adjust accordingly. To edit the thread title, open the original post to edit and then click on Go Advanced (bottom right) to access the area where you can edit your title.
(Note: this change is not optional. No help to be offered until this moderation request has been fulfilled.)
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.
Forum Rules (updated September 2018): please read them here.
How to use the Power Query code you've been given: help here
. More about the Power suite here
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By Aquarock in forum Excel Formulas & Functions
Last Post: 02-19-2019, 10:15 AM
By king10001 in forum Excel Formulas & Functions
Last Post: 08-29-2013, 07:33 AM
By kurl01 in forum Excel Formulas & Functions
Last Post: 05-03-2013, 07:43 AM
By Decoderman in forum Excel Programming / VBA / Macros
Last Post: 04-14-2011, 12:28 PM
By meersr in forum Excel General
Last Post: 04-15-2006, 03:15 PM
By danielle in forum Excel General
Last Post: 04-25-2005, 06:06 PM
By Stumped in forum Excel General
Last Post: 01-12-2005, 07:06 PM
Search Engine Friendly URLs by vBSEO 3.6.0 RC 1