CODEWITHSUNDEEP
excel
dsauce
dsauce

Reputation: 612

Excel - Array formula as name range not working

I have a table with projects and people and I want to make a dynamic name range based on that. However, it seems to return no values. Any insights into this would be helpful. Thanks!

Table:

projperson
a   x1
a   x2
a   x3
b   y1
b   y2
b   y3

Name Range Definition:

=IF(temp[proj]=selected_project,temp[person],"")

where selected_project is one of the a or b from the project list (selection through data validation). This name range doesn't return any value.

My expected output would be a name range with {x1, x2, x3} in case project a is selected.

Upvotes: 0

Views: 3385

Answers (1)

XOR LX
XOR LX

Reputation: 7742

Define it in Name Manager as:

=INDEX(temp[person],N(IF(1,SMALL(IF(temp[proj]=selected_project,ROW(temp[proj])-MIN(ROW(temp[proj]))+1),ROW(INDIRECT("1:"&COUNTIF(temp[proj],selected_project)))))))

Note that entering this expression (or equally the Defined Name representing this expression) into an actual cell within the worksheet will not generate the required array, even if applied with CSE, since there is no external formula acting upon this expression which would otherwise induce an array of returns.

Of course, any suitable external formula will coerce that array return, though whether the resulting expression requires CSE or not is dependent on which of the above expression or the Defined Name representing that expression is referenced.

For example, if the above was stored as Rng, then the non-array formula:

=COUNTA(Rng)

will resolve to, using the example you give, as:

=COUNTA({"x1";"x2";"x3"})

However, unless array-entered (i.e. with CSE), the equivalent expression:

=COUNTA(INDEX(temp[person],N(IF(1,SMALL(IF(temp[proj]=selected_project,ROW(temp[proj])-MIN(ROW(temp[proj]))+1),ROW(INDIRECT("1:"&COUNTIF(temp[proj],selected_project))))))))

will not resolve as above, but rather to:

=COUNTA("x1")

Regards

Upvotes: 1

Related Questions