How to use INDIRECT with a dynamic array?

[alexcr]

Let's say "A1#" is a dynamic array that returns cell addresses from multiple worksheets. For example, it might return something like this...

Sheet1!Z9
Sheet8!Z9
Sheet14!Z9
Sheet26!Z9

Again, this is a dynamic array, so these addresses could vary at any given time.

I'm attempting to use the INDIRECT function with this array, i.e. "=INDIRECT(A1#)".

But when I attempt this, the function is returning an array of "#VALUE" errors, as opposed to returning the values from the cells being referenced.

What am I messing up here? Any help would be greatly appreciated.

 

[Cubist]

I don't believe INDIRECT can take an array. Try:

=BYROW(A1#,LAMBDA(br,INDIRECT(br)))

If your array spills from left to right then change to BYCOL.

 

[StephenCrump]

Or you could try:

=CELL("contents",INDIRECT(A1#))

 

[alexcr]

Thanks so much, @Cubist and @StephenCrump! I went with the CELL solution, as I'm a little more familiar with the function, and it seems to be working so far. This was a huge help, I appreciate it.