Excel - how to drag down formula while keeping horizontal cell reference continuity

I been trying few formulas and google search, but wasn't able to find anything that would help my problem. To keep it simple, as explained in the attached picture, how do I drag down formula vertically, while continue the horizontal cell reference in the formula

?

As in picture - formula vertical direction - green arrow, while following the horizontal cells - orange arrow.

Thank you a million!

Solution 1:

You can use the OFFSET function in order to specify a cell offset from cell A1. Even if the formula is being copied downward, this can still be a horizontal offset. For example, the following formula inserted into cell B2 in your screenshot will offset to the right when it is copied downward (it uses the numbers in column A as the offset values):

=OFFSET($A$1, 0, A2)

Solution 2:

A non-volatile suggestion with Index:

=A2-INDEX($1:$1,ROW(A2))

Offset is volatile and will cause the whole workbook to recalculate any time any cell is changed. That can lead to slowness in larger files with many formulas.

Explanation (after request for clarification):

The first row has data starting in column B, which is the 2nd column. The Index() function above looks at the entire row 1 and then gets pointed to the second column because the function Row(A2) returns the value 2.

If the formula is dragged down, the Row(A2) bit changes to Row(A3) and the number 3 is returned, hence Index will return the third column of row 1.

To adjust this scenario to another setting you need to inspect the setup. Which column in row 1 has the first data value? The second, the third, or the nth column?

The column number with the first value is what you need for the number part in the reference for the Row() function. If your data starts in column 2, then it will be Row(A2). If your data starts in column 7 then it will need to be Row(A7). Then drag down to let Excel do its thing with adjusting relative references.

The important bit is that you get the starting number right.