+ Reply to Thread
Results 1 to 2 of 2

Complex Index/match formula to pull data based on 1 criteria. (Bid/Cost Sheet)

  1. #1
    Registered User
    Join Date
    05-29-2019
    Location
    Houston, Texas
    MS-Off Ver
    Office 365
    Posts
    8

    Complex Index/match formula to pull data based on 1 criteria. (Bid/Cost Sheet)

    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.
    Attached Files Attached Files
    Last edited by Blackmagic42; 07-18-2019 at 08:41 AM. Reason: Attempting to make title clearer.

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Business (Win 10 - Work) & MS 365 Subscription Insider (Win 10 - Home)
    Posts
    47,110

    Re: Complex Index Match Trouble Shooting

    Administrative Note:

    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.)
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!

    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.
    Don't forget to say "thank you" to those who have helped you in your thread. If you wish, you can also reward them by clicking on their reputation star bottom left.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Complex Index Match trouble
    By Aquarock in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 02-19-2019, 10:15 AM
  2. [SOLVED] Trouble Shooting formulas #REF #VALUE
    By king10001 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 08-29-2013, 07:33 AM
  3. [SOLVED] sum formula trouble shooting
    By kurl01 in forum Excel Formulas & Functions
    Replies: 5
    Last Post: 05-03-2013, 07:43 AM
  4. Trouble Shooting VBA Vlookup
    By Decoderman in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-14-2011, 12:28 PM
  5. Trouble shooting Excel?
    By meersr in forum Excel General
    Replies: 1
    Last Post: 04-15-2006, 03:15 PM
  6. [SOLVED] trouble shooting section
    By danielle in forum Excel General
    Replies: 1
    Last Post: 04-25-2005, 06:06 PM
  7. Excel trouble shooting - no gridlines
    By Stumped in forum Excel General
    Replies: 11
    Last Post: 01-12-2005, 07:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1