Excel OFFSET function with a dynamic row argument-CodePudding

Sorry about my newbieness here.

So... I have this table:

And I want to collect some data into a different sheet:

As example, I am collecting D14, with this formula: =OFFSET(Test_Sheet!$A$18,2,9,1,1).

Problem is: when I drag the formula to Row 15, the value stays the same because the Argument "rows" it is static. How can I drag down this formula with "rows" argument increasing 1 unit per row? Expected result should be =OFFSET(Test_Sheet!$A$18,3,9,1,1).

Thank you all

CodePudding user response：

Your solution is to create what I call "anchor cells" as references and calculate your offsets from those anchors. In your example, your first anchor is Test_Sheet$A18. For my own convenience, I usually create a named range for that cell.

In my example here, you can see that cell A4 is named DataAnchor.

Your next anchor is the FIRST cell of your formula in relation to the anchor cell. (Clearly, if you move things around your relative "constants" will change. So be advised.)

In this example, my first cell with the formula is F8.

To get the relative row offset between the anchor row and my formula row, you calculate:

ROW()-ROW(DataAnchor)-2

That's the current row number minus the row number of the anchor minus two.

Then the formula in F8 copied down becomes

=OFFSET(DataAnchor,ROW()-ROW(DataAnchor)-2,2,1,1)