Excel OFFSET function with a dynamic row argument

Question

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).

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

Answer 1

The answer is to make "anchor cells" as references, from which you may calculate your offsets. Your first anchor in the given example is Test Sheet$A18. I normally make a designated range for that cell for my own convenience. 

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.

You compute: To determine the relative row offset between the anchor row and my formula row:

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)