Excel: Start a Named Range from the beginning no matter where the list is referenced

Question

I have a named list that has letters a-k. I have named the list "NamedList" and I want to reference that list else where in the sheet using =NamedList and dragging down. But it does not start from the beginning when I do this.

How do you start it from the beginning regardless of location?

Answer 1

This formula =INDEX(NamedList,i), i is the index number (from 1), is to find the corresponding value from your named range with index number. For example, =INDEX(NamedList,10) will return j.

From here, I have derived a formula that you can copy-paste anywhere and it should always start from the beginning except the very first row. But copy this and paste into cell G20 for the very first time. From there, you can copy-paste anywhere you want.

=IFERROR(INDEX(NamedList,ROW(INDIRECT(ADDRESS(IF(G19<>"",MATCH(G19,NamedList)+1,1),COLUMN())))),"")

The MATCH(G19,NamedList) is to find the index number from the prior cell. And then I use ROW, INDIRECT and ADDRESS to return numeric value for the INDEX function. Please try and let me know.

Answer 2

Try this formula,

=INDEX(NamedList,ROW(A1),)

Answer 3

Select all 11 vertical cells you want to contain the list and enter
=NamedList
and then use Control-Shift-Enter instead of just Enter. This creates a multi-cell array formula (you can see the { } that excel puts around the array formula).
Make sure you select all the cells and use CSE rather than entering into one cell and dragging down.

The reason your original method did not work the way you want is that Excel is using Implicit Intersection to select the intersect of your Named Range with the Row. Using an array formula switches off Implicit Intersect.

Answer 4

You can try naming the start and end cells of your named list, e.g. click on cell B2 and then give it a name (e.g. NamedListStart). Do the same for cell B12, as shown here:

Then to use the range, simply highlight a range of cells where you want to fill with the range and enter the following formula:

=NamedListStart:NamedListEnd

Make sure to enter this as an array formula using SHIFT + CTRL + ENTER