Named Range

[razaas]

I have a dynamic named range (_nameslist) with this Formula: 1'!$AE$7:INDEX('1'!$AE$7:$AE$22,COUNTA('1'!$AE$7:$AE$22)). This working well asit (Expands/Shrink) when names added or deleted.
But, when I use this formula: IFERROR(_namesList,"") in the range C7 to C22 it works well if I have more than 1 names in the dynamic named range/list, if there is only 1 name then it fills the whole range C7 toC22 with the same name that I have in my dynamic list. If I enter a blank space in the next cell (AE8) in the dynamic list then also it works.
Could anyone tell why is this happening and what is thesolution for this problem?
Thanks

 

[RoryA]

Welcome to the forum.

Change the formula to:

=IFERROR(INDEX(_Nameslist,ROWS($A$1:$A1)),"")

in C7 and copy it down.

 

[razaas]

Welcome to the forum.

Change the formula to:

=IFERROR(INDEX(_Nameslist,ROWS($A$1:$A1)),"")

in C7 and copy it down.

RoryA,

Thanks a lot it works, just curious why my original formula: =IFERROR(_Nameslist,"") does not work if there is only 1 name in the dynamic list, for more than 1 name it works fine. If possible please explain.

Highly appreciate your quick response and the solution.

 

[RoryA]

Your original formula relies on implicit intersection with an array (which is not a good idea generally, in my opinion). When there is only one cell, you just have a value, not an array, so you simply get that value returned to every cell.

 

[Marcelo Branco]

Implicit intersection is somewhat obscure. To better understand, take a look at
https://www.myonlinetraininghub.com/excel-implicit-intersection

M.

 

[razaas]

Your original formula relies on implicit intersection with an array (which is not a good idea generally, in my opinion). When there is only one cell, you just have a value, not an array, so you simply get that value returned to every cell.

Thanks a lot, highly appreciate.

 

[razaas]

Implicit intersection is somewhat obscure. To better understand, take a look at
https://www.myonlinetraininghub.com/excel-implicit-intersection

M.

Thanks a lot, highly appreciate.