Drop down to generate list from table

[MissWorkaholic]

Hi

I am looking to generate a list from a drop-down menu.

On sheet 1 there is a table which lists “keywords” in column A and “policies” in row 1. Within the table “x” is marked where a keyword is found in a policy.</SPAN>

On sheet 2 I have a drop down list with all the keywords and what I would like it to do is generate a list below of all the policies where “x” is marked.</SPAN>

Any help on this would be greatly appreciated!</SPAN>

 

[XOR LX]

Hi.

Unfortunately this is not so easy to do, and requires that reduced lists be created for each of your keywords.

Personally I would do it as follows:

Assuming your table of Keywords/Policies is in Sheet1!A1:L27 (with the Policies in B1:L1, Keywords in A2:A27 and each entry in B2:L27 containing either a blank (or null string "") or "x"), enter this formula in cell M2, just to the right of that table:

=IF(COLUMNS($A:A)>COUNTIF($B2:$L2,"x"),"",INDEX($1:$1,AGGREGATE(15,6,COLUMN($B1:$L1)/($B2:$L2="x"),COLUMNS($A:A))))

Copy down and across to fill the range M2:W27.

Now, assuming that the list of Keywords for which you wish to create the drop-downs is in Sheet2!A1:A26, then, making sure that the active cell in the workbook is Sheet2!B1 (important), go to Name Manager (Formulas tab) and define:

Name: Val
Refers to: =INDEX(Sheet1!$M$2:$M$27,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$27,0)):INDEX(INDEX(Sheet1!$M$2:$W$27,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$27,0),0),MATCH(TRUE,LEN(INDEX(Sheet1!$M$2:$W$27,MATCH(Sheet2!$A1,Sheet1!$A$2:$A$27,0),0))=0,0)-1)

Then go to Data/Data Validation, choose List in the Allow: box and then enter:

=Val

in the Source: box.

Copy this validation down to cell B26.

Regards

 

[MissWorkaholic]

XOR LX thank you so much! This option certainly works very well for me.

Much appreciated.

 

[XOR LX]

You're welcome!

Thanks for the feedback.

Cheers