CODEWITHSUNDEEP
excelexcel-formula
KayBee
KayBee

Reputation: 51

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

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 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

Upvotes: 5

Views: 5845

Answers (5)

Marcos
Marcos

Reputation: 1

I found a way using INDEX and MAKEARRAY:

=MAKEARRAY(rows;columns;LAMBDA(r;c;INDIRECT("A"&INDEX(B4#;r;c))))

INPUT example: rows = 3; columns = 1; A1# = {c;d;e}; B4# = {1;2;3}

OUTPUT: {c;d;e}

Upvotes: 0

Minh Lai
Minh Lai

Reputation: 31

I found a very interesting point about this function. Although we all assume that Indirect does not work with array, but in this case it seems to work. For example, I have 3 cells with values: A1=3,B2=4,C3=5. If I write =INDIRECT({A1;B2;C3}) it returns #VALUE!. But if I write =CELL("contents",INDIRECT({A1;B2;C3})), it returns an array of 3,4 and 5. The same goes to your case with CELL and INDIRECT. And if I wrap it with a function like SUM or COUNT, it still works. The CELL + INDIRECT formula still works in the older Excel versions. However, the combination of SUM, CELL, and INDIRECT doesn't. So if you just want to achieve what you want, this is already the solution. If you want to achieve more, I suggest changing to another answer.

Upvotes: 3

Julio César Velasco Vaca
Julio César Velasco Vaca

Reputation: 11

You can use the following formula =BYROW(B1#,LAMBDA(a,INDIRECT(a)))

Upvotes: 1

Gary's Student
Gary's Student

Reputation: 96753

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

  1. "acts" like INDIRECT()
  2. can accept an array as an input
  3. 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:

enter image description here

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.

Upvotes: 1

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

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.

Upvotes: 1

Related Questions