Excel OFFSET function with a dynamic row argument

Question

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

You don't need Offset. You could put a direct reference: =Test_Sheet!D14 and drag that down. — Ike
– Ike, Commented Apr 29, 2022 at 12:52
I did not mention it in my question but I have several small arrays in the same sheet where I need to use this formula. I need to use the OFFSET function (or at least something similar to that) — rochinha44
– rochinha44, Commented Apr 29, 2022 at 13:04

PeterT · Accepted Answer · 2022-04-29 12:59:16Z

2

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)

answered Apr 29, 2022 at 12:59

PeterT

8,5971 gold badge19 silver badges46 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

rochinha44 Over a year ago

Yes, it is much better but, like you said: "if you move things around your relative "constants" will change". Any tip to overcome this? I might have several "anchors" in the same worksheet where your functions will be implemented.

PeterT Over a year ago

Honestly? More named anchors or "delta" constants (such as the 2 that's in the formula ROW()-ROW(DataAnchor)-2) can also be turned into another adjustable named value. You can even calculate those values to automagically adjust as needed. This all depends on your data, your arrangement of the data, etc.

Collectives™ on Stack Overflow

Excel OFFSET function with a dynamic row argument

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related