=OFFSET(INDIRECT($A$4),5 row()-2,4,1,1)

I'm looking to repeat the above formula in all row within the same column, so the calculation will change based on the row(). I tried to put it like below, column H is where the result will be going. But the row() is not following, all result are just using the formula row instead. The $A$4 is a where I'm using to input the range name.

=ARRAYFORMULA(D3:D&OFFSET(INDIRECT($A$4),5 row()-3,4,1,1))

I know I can drag it down, but automatic is always better. Before I'm over complicating the formula, is there a simple solution to make this work?

Updates: Sample sheet link https://docs.google.com/spreadsheets/d/1di8a9I0Fv-vuQFDNfV3sPsnWbDuBWTNDYlvUAI5pOCk/edit?usp=sharing

CodePudding user response：

I've added a new sheet ("Erik Help") with two formulas, each of which will return the same results:

=IFERROR(OFFSET(INDIRECT("CLASS_"&$A$4),5 row()-3,4,COUNT(INDEX(INDIRECT("CLASS_"&$A$4))),1),"NO DATA")

-or-

=IFERROR(QUERY({INDEX(INDIRECT("CLASS_"&$A$4),,5)},"Select * WHERE Col1 Is Not Null",0),"NO DATA")

The D3 formula is better, because it is not dependent on the row where the formula is found (i.e., it can be placed anywhere and get the same result). It is "cleaner" (i.e., it has no redundancy of the INDIRECT clause). It's also easier to read and edit if necessary.