Array result from INDIRECT

1
votes

So, I have a Google Sheet connected to a Google Form that I use for debugging a series of games. Each game has a Unit number and an Activity number.

I added to the sheet a column that, basing on the Unit and on the Activity, retrieves the name of the developer, contained in another sheet.

=INDIRECT("Developers!"&CHAR(C1+64+1)&(B1+1))

(I have to add 1 to each value because the other table has headers)

The formula does work on a single cell, but it's not applied to the new lines inserted by the Google Form.

I've seen that ARRAYFORMULA() returns an array that automatically populates the cells below.

Is there a command I can use to apply a formula to an array of values and have an array of results returned?

1
arraysgoogle-sheetsgoogle-sheets-formulaarray-formulas

1 Answers

0
votes

if you want to get just a range do:

=INDIRECT("Developers!"&ADDRESS(B1+1, C1+1, 4)&":"&ADDRESS(ROWS(A:A), C1+1, 4))

or populated range:

=INDIRECT("Developers!"&ADDRESS(B1+1, C1+1, 4)&":"&ADDRESS(COUNTA(B:B), C1+1, 4))