+ Reply to Thread
Results 1 to 8 of 8

Search by Multiple Criteria, Put All Results into Drop Down List

  1. #1
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

    Search by Multiple Criteria, Put All Results into Drop Down List

    NOTE: I will also cross-post this question to MrExcel and OzGrid.

    I'm creating a price quote form for engine repairs and I need a way to narrow down my list of engine parts based on criteria entered into 2 drop down lists.

    For example, in drop down list #1 I want to select the engine's manufacturer (Caterpillar, Mercedes, etc.), and then in drop down list #2 I want to select the category of part (piston, valve guide, etc.).
    1. I want Excel to use those two criteria to find ALL matches (not just the first match) in my large list of parts.
    2. I want those results to appear in a third drop down list from which I can ultimately select the desired part.

    I've attached a stripped-down workbook as an example. It only contains the data relevant to my question.
    Forum_Question.xlsx

    One note: I cannot change anything about the source tables. They come from a supplier AND are in a SQL database to which I've connected.
    Last edited by Jeff C; 07-26-2012 at 04:17 PM. Reason: Added URL to cross-post at OzGrid

  2. #2
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Could you add a helper column to the source data, ie in the PartList table?
    Where there is a will there are many ways.

    If you are happy with the results, please add to the contributor's reputation by clicking the reputation icon (star icon) below left corner

    Please also mark the thread as Solved once it is solved. Check the FAQ's to see how.

  3. #3
    Forum Guru tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Jeff C,

    In your workbook, the vehicle ALFA ROMEO has a Captured Code of 15, but there is no Vehicle Code 15 in the Part List sheet. How does the Part List identify the vehicles? The same goes for the Part Categories. You have Pistones selected which provides a Captured Code of 18, but I don't see an 18 anywhere in the Part List sheet. How does the Part List identify the categories?
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble

  4. #4
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Quote Originally Posted by NBVC View Post
    Could you add a helper column to the source data, ie in the PartList table?
    Since, I am off soon... I will assume you can add a helper...

    In Parts tab F2 enter:

    =IF(AND(Table3[[#This Row],[Part_Category]]=FORM!$E$11,Table3[[#This Row],[Vehicle_Category]]=FORM!$E$9),COUNT(F$1:F1)+1,"")

    copied down

    this should expand when you refresh data...

    Then add a new sheet with formula in A1:

    =IFERROR(INDEX(PartList!C:C,MATCH(ROWS($A$1:$A1),PartList!F:F,0)),"")

    copied down as far as you need to ensure all matches are always retrieved.

    Name this column something like PartDesc

    Then for Data Validation in the Form sheet, use List and add formula:

    =INDEX(PartDesc,1):INDEX(PartDesc,COUNTIF(PartDesc,"?*"))
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Quote Originally Posted by NBVC View Post
    Could you add a helper column to the source data, ie in the PartList table?
    Yes, i could add a helper column. I just can't delete, move, or otherwise reorganize the existing columns from my supplier.

    Thanks for taking a look.

    ---------- Post added at 10:01 PM ---------- Previous post was at 09:58 PM ----------

    Quote Originally Posted by tigeravatar View Post
    Jeff C,

    In your workbook, the vehicle ALFA ROMEO has a Captured Code of 15, but there is no Vehicle Code 15 in the Part List sheet....The same goes for the Part Categories.....How does the Part List identify the categories?
    Well, when I created the sample workbook I deleted a lot of data. (Many worksheets have over 35000 record rows each!) So I'm sure I removed quite a few references to Alpha Romeo, Pistons, and other manufacturers/part categories. Rest assured, all of those manufacturer and part categories are represented in my original workbook.

    Thanks for taking the time to look.

    ---------- Post added at 10:22 PM ---------- Previous post was at 10:01 PM ----------

    Thank you NBVC. Excel accepted everything you suggested except for the formula in the data validation list. When I try to enter it, Excel gives me the following error:
    "You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria."
    Similarly, when I tried opening your attached workbook, Excel would not allow whatever was in the three data validation lists on the FORM worksheet and removed those formulas. (PS - I'm using Excel 2010.)

  6. #6
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    I don't have access to Excel2010 at the moment.... but I just re-opened it in Excel2007 and it works fine.

    Is it maybe due to your regional settings.. try using this formula in the data validation for the 3rd box:

    =INDEX(PartDesc;1):INDEX(PartDesc;COUNTIF(PartDesc;"?*"))

  7. #7
    Registered User
    Join Date
    07-03-2012
    Location
    Neuquen, Argentina
    MS-Off Ver
    Excel 2010
    Posts
    10

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Quote Originally Posted by NBVC View Post
    ...Is it maybe due to your regional settings...
    Thanks for thinking of that -- it is indeed an issue with my machine -- but I had already replaced the commas with semicolons. It still doesn't work.

    I think Excel is refusing to accept the array portion of your formula in the data validation list. I've looked through the options for Excel 2010, but I don't see anything that I can change to reverse this.

  8. #8
    Forum Expert NBVC's Avatar
    Join Date
    12-06-2006
    Location
    Mississauga, CANADA
    MS-Off Ver
    2003:2010
    Posts
    34,902

    Re: Search by Multiple Criteria, Put All Results into Drop Down List

    Does this formula work?

    =OFFSET(PartDesc;0;0;COUNTIF(PartDesc;"?*");1)

    ---------- Post added at 02:54 PM ---------- Previous post was at 02:50 PM ----------

    Or let's try this.

    Redefine the PartDesc dynamic range as:

    =Sheet1!$A$1:INDEX(Sheet1!$A:$A;COUNTIF(Sheet1!$A:$A;"?*"))

    and then for the Data Validation, use formula : =PartDesc

    Does that work better?

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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