Excel 2010 showing items in drop down list where criteria is met

Question

I'm going around in circles with this and have tried as many different options as I can think of - VLOOKUP, INDEX/MATCH, IF etc... but I'm failing everytime.

I need to create a drop down list in sheet 3 (column A) that gets populated with values in column B of sheet 2, only where the value in column G of sheet 2 is "Yes". The values in sheet 2 look as follows:

This is what is shown at present:

I am using the following formula within Name Manager to get to this point:

=OFFSET(Units!$B$11,0,0,COUNTIF(Units!$B$11:$B$202,">"""),1)

and although this works, it's not quite as I need it to be. I've tried using Index/Match, small, VLOOKUP etc... but Excel reports that either I've too few arguments or can't reference a worksheet.

I've literally been though as many excel websites as I can find but no one seems to cover creating drop down lists where the drop down is dependent on a specific selection.

The values within sheet 2, column B are obtained from a hidden sheet and collected using the following query:

    =IF(ISERROR(INDEX(All_Units!$D$2:$D$660542,MATCH(Units!A11,All_Units!$C$2:$C$660540,0))),"",INDEX(All_Units!$D$2:$D$660542,MATCH(Units!A11,All_Units!$C$2:$C$660540,0)))

I wondered whether I could take this query and use it to generate the drop down list, something along the lines of:

=INDEX(Units!$B$11:$B$202,MATCH(Units!$G$11:$G$202="Yes",Units!$G$11:$G$202,0)))

but this returns an error. The closest I got was using the OFFSET formula above but performing a COUNTIF, however; this unsurprisingly wouldn't tie Column B to the corresponding 'Yes' in column G.

Does anyone have any ideas as to how I can get this working?

You will need an array formula and I do not believe you can use array formulas as named ranges or data validation. You will need another sheet in which you put the output of an array formula then use another formula in the named range to get extent of the returned data. — Scott Craner

Scott Craner Scott Craner · Accepted Answer · 2017-01-05T15:11:24

You will need to create another sheet into which you will place an array formula. This formula will return all the desired output.

so create a sheet and name it something like DataVal

In A2 put the following array formula:

=IFERROR(INDEX(Units!$B$11:$B$202,MATCH(1,(Units!$G$11:$G$202="Yes")*(COUNTIF($A$1:A1,Units!$B$11:$B$202)=0),0)),"")

Being an array formula it needs to be confirmed with Ctrl-Shift-Enter. If done correctly then Excel will put {} around the formula.

Then copy/drag down far enough to ensure capturing every possible return.

Then use another INDEX/MATCH to create the named range (OFFSET() is volatile and should be avoided when possible). The formula would be something like:

=DataVal!$A$2:INDEX(DataVal!$A:$A,MATCH("ZZZ",DataVal$A:$A))

This will dynamically set the named range to the extent of the dynamic list created by the array formula above.

Excel 2010 showing items in drop down list where criteria is met

1 Answers