Excel OFFSET function with a dynamic row argument

0 votes

I have this table:

enter image description here

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

enter image description here

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

Sep 25, 2022 in Others by Kithuzzz
• 27,940 points
163 views

1 answer to this question.

0 votes

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.

enter image description here

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.

enter image description here

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)
answered Sep 26, 2022 by narikkadan
• 51,600 points

Related Questions In Others

0 votes
1 answer

IF function in combination with an Round function Excel

I'm not sure if it is a ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 51,600 points
124 views
0 votes
1 answer

Excel - IF Formula with a FIND

What about using mid() to see if ...READ MORE

answered Sep 27, 2022 in Others by narikkadan
• 51,600 points
111 views
0 votes
1 answer

Excel use SUMIF but with a multiplier before summation: x2, x3, x4, etc

The product of the sum and the ...READ MORE

answered Sep 30, 2022 in Others by narikkadan
• 51,600 points
114 views
0 votes
1 answer

MS Excel - Concat with a delimiter

Use TEXTJOIN() instead: =TEXTJOIN(";",TRUE,A1:A2000) I hope this ...READ MORE

answered Oct 2, 2022 in Others by narikkadan
• 51,600 points
134 views
0 votes
1 answer

Excel offset where reference is to another worksheet

Solution:  =OFFSET((INDIRECT(ADDRESS(1,1,,,"Sheet2")),ROW(),0,,) References: OFFSET(starting point, num of rows, num ...READ MORE

answered Oct 8, 2022 in Others by narikkadan
• 51,600 points
299 views
0 votes
1 answer

VBA Range.Offset Error 1004 Range beyond scope of sheet Dynamic Range

Check the .Row first: With Sheets("Line 3").Range("G1024").End(xlUp) ...READ MORE

answered Jan 12 in Others by narikkadan
• 51,600 points
44 views
0 votes
1 answer

Excel VBA - Subscript Out of Range Error (Run Time: Error 9)

Set rangePartner = Range(Partner.Cells(2, 2), Partner.Cells(2, 2).End(xlDown)) Needs ...READ MORE

answered Jan 21 in Others by narikkadan
• 51,600 points
36 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
523 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,600 points
1,106 views
0 votes
1 answer

How to create a drop-down in excel with custom values

You can accomplish that using code rather ...READ MORE

answered Sep 25, 2022 in Others by narikkadan
• 51,600 points
140 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP