Arrayformula using cells as parameters

Question

I would like to use the ARRAYFORMULA combined with two cells as range. For example:

Cell A1 value: A3
Cell A2 value: B7

Resulting formula: ARRAYFORMULA(A3:B7)

Is it possible? How can I do?

Answer 1

It's possible with

=arrayformula(indirect(A1 & ":" & A2))

Indeed, the string concatenation A1 & ":" & A2 produces the string "A3:B7", and indirect picks up the corresponding range.

Actually, =indirect(A1 & ":" & A2) is already enough to return an array of values, but arrayformula may be needed if you are going to do some computation with those values.

---

If the addresses in A1 and A2 refer to another sheet, only the first of them should have the sheet name: i.e., put Sheet2!A3 in A1 and simply B7 in A2. This is because stating sheet name twice is redundant, and indirect won't like that.