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.

I would like the named range to pick the values for A where c is 1 - or in this example 1,3,5,6. And I would like to see what have you tried till now and where are you stuck? Questions asking for code must demonstrate a minimal understanding of the problem being solved.Include attempted solutions, why they didn't work, and the expected results. See also: Stack Overflow question checklist — Siddharth Rout
– Siddharth Rout, Commented Dec 10, 2013 at 1:57
I have tried using various methods I have found on here, and on the internet in general, as well as a solution that involved a pivot table. However, this was not ideal as it required refreshing the pivot table and named range when data changed. — Martin Bergin
– Martin Bergin, Commented Dec 10, 2013 at 2:04

teylyn · Accepted Answer · 2013-12-10 02:22:27Z

5

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.

enter image description here

answered Dec 10, 2013 at 2:22

teylyn

36.2k6 gold badges58 silver badges76 bronze badges

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

1 Comment

Siddharth Rout Over a year ago

+ 1 Nicely explained Ingeborg :)

Collectives™ on Stack Overflow

Creating a dynamic named range in Excel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related