Creating a dynamic named range in Excel

Question

I'am trying to create a named range to use as part of a data validation drop down menu. I'm creating this from a table, however, I only want to display the values from Column A which meet a certain criteria(true or false) in Column C. However, the values in Column C can change so I need the named range to be able to change as well.

My table looks like this:

    A    B    C
    1    ..   1
    2    ..   0
    3    ..   1
    4    ..   0
    5    ..   1
    6    ..   1

I would like the named range to pick the values for A where c is 1 - or in this example 1,3,5,6.

Answer 1

Try with a helper column:

In D2 enter

=IF(C2,ROW(),"")

Copy down. Create a list of values in column F, starting in F2 with

=IFERROR(INDEX(A:A,SMALL(D:D,ROW(A1))),"")

Copy down. Create a named range that refers to the formula

=Sheet1!$F$2:INDEX(Sheet1!$F:$F,MATCH(99^99,Sheet1!$F:$F,1))

Use that range name in the data validation list value.

When a value in column C changes from 0 to 1 or vice versa, the validation list will update immediately.