using INDIRECT function with dynamic array formula syntax (Excel O365)

Question

I am using the new dynamic array functions introduced in excel in 2018 (e. g. SEQUENCE, UNIQUE etc. functions). I have a list of cell references that are that are generated dynamically, and would like to apply the INDIRECT function to these list items. A simplified example:
cell A1: =SEQUENCE(5) (results in rows column A values 1,2,3,4,5 as expected)
cell B1: ="A"&A1# (results in rows column B values A1, A2, A3, A4, A5 as expected)
cell C1: =INDIRECT(B1#) this should give me rows in column C values 1,2,3,4,5, but in fact gives me #VALUE ,#VALUE ,#VALUE ,#VALUE ,#VALUE So the formula properly recognizes the number of rows of the original dynamic array, but for some reason does not dereference the cells properly. The strings seem to be of the proper format - a simple string function such as LEN also works: setting C1 to =LEN(B1#) results in 5 rows of the value 2.

The syntax per se seems to be OK.. for the special case of =SEQUENCE(1) in cell A1 everything works as intended. I tried the R1C1 reference format also, same result

EDIT Overall I am trying to achieve the following

import a list form a non-Excel data source list is not a dynamic array, it's just a TSV import. I don't now beforehand how many items are in this list, and it can vary a lot
do several different calculations on values of this list.
so far my approach was to use the COUNT function to determine the number of items in the imported list, and then use that to create the second list using SEQUENCE and INDEX to retrieve values.
the problem arises for some calculations where the data contains references to other rows so I have to use indirect addressing to get at that data

user_2872922 · Answer

The INDIRECT function cannot accept an array for an argument.

In other words:

=INDIRECT({"a1","a2"}) --> #VALUE! | #VALUE!

So you could, for example, refer to each cell in column B as a single cell:

eg:

C1: =INDIRECT(B1)

and fill down.

Depending on how you are using this, you could also use the INDEX function to return an individual element

To return the third element in the array generated by B1#:

  =INDIRECT(INDEX(B1#,3))

EDIT:

After reading your comment, and depending on details you have not shared, you may be able to use a variation of the INDEX function.

For example, to return the contents of A1:A5, based on your SEQUENCE function, you can use:

=INDEX($A:$A, SEQUENCE(5))

but exactly how to apply this to your actual situation depends on the details.

user_2474656 · Answer

As Rosenfeld points out, INDIRECT() does not accept an array as an input. If you need a function that:

"acts" like INDIRECT()
can accept an array as an input
can return an array as an output

Then we can make our own:

Public Function Indirect_a(rng As Range)
    Dim arr, i As Long, j As Long
    Dim rngc As Long, rngr As Long
    
    rngc = rng.Columns.Count
    rngr = rng.Rows.Count
    ReDim arr(1 To rngr, 1 To rngc)
    
    For i = 1 To rngc
        For j = 1 To rngr
            arr(j, i) = Range(rng(j, i).Value)
        Next j
    Next i
        
    Indirect_a = arr
End Function

and use it like:

Since it creates a "column-compatible" array, it will spill-down dynamically in Excel 365.
It can be used in versions of Excel prior to 365, but it must be array-entered into the block it occupies.

using INDIRECT function with dynamic array formula syntax (Excel O365)

2 Answers