Excel drop down list with conditional values

Question

I have a table containing project name and is_live. I'd like to populate the conditional dropdown to show only those that have is_live = 1. the table looks like

PROJECT1 | 1
PROJECT2 | 0
PROJECT3 | 0
PROJECT4 | 1
PROJECT5 | 1

The dropdown should contain PROJECT1, PROJECT4 and PROJECT5 as they have is_live = 1. If I flip PROJECT2 to 1 then that should then show.

I tired creating a pivot table and having the is_LIVE as a filter. I then created a named list based on the rows in the pivot. This worked well apart from the named list doesnt grow or shrink as the rows grow and shrink.

Does anyone know if this is possible in Excel?

Thanks

One thing I've done before is used VBA macros to populate the range for my drop down on workbook load.. I'm not sure if that would be something that would work for your situation. — TyloBedo
– TyloBedo, Commented Nov 17, 2016 at 2:30
If the pivot table approach works, then I'd suggest you use that, but change your named range to a Table (ListObject). This way, when your source data grows or shrinks. This link has some other ideas: stackoverflow.com/questions/16218332/… — Hambone
– Hambone, Commented Nov 17, 2016 at 2:57

Community · Accepted Answer · 2017-05-23 10:32:38Z

0

From an answer on this question I believe a formula like this might do the trick

=IF(ISERROR(INDEX($A$1:$A$5,SMALL(IF(1=$B$1:$B$5,ROW($B$1:$B$5)),ROW(1:1)),1)),"",INDEX($A$1:$A$5,SMALL(IF(1=$B$1:$B$5,ROW($B$1:$B$5)),ROW(1:1)),1))

You'll want to adjust the ranges to fit your spreadsheet then press ctrl+shift+enter and then drag the formula down.

edited May 23, 2017 at 10:32

CommunityBot

11 silver badge

answered Nov 17, 2016 at 2:56

TyloBedo

4952 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Excel drop down list with conditional values

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related