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

**Jeff C** · 07-26-2012, 04:06 PM

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

I want Excel to use those two criteria to find ALL matches (not just the first match) in my large list of parts.
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.

**NBVC** · 07-26-2012, 04:35 PM

Could you add a helper column to the source data, ie in the PartList table?

**tigeravatar** · 07-26-2012, 04:38 PM

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?

**NBVC** · 07-26-2012, 04:43 PM

Originally Posted by NBVC

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,"?*"))

**Jeff C** · 07-26-2012, 09:22 PM

Originally Posted by NBVC

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 ----------

Originally Posted by tigeravatar

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

**NBVC** · 07-27-2012, 08:05 AM

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;"?*"))

**Jeff C** · 07-27-2012, 02:24 PM

Originally Posted by NBVC

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

**NBVC** · 07-27-2012, 02:54 PM

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?