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
• 38,020 points
989 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
• 63,720 points

Related Questions In Others

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
• 63,720 points
994 views
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
• 63,720 points
3,343 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
• 63,720 points
685 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
• 63,720 points
1,206 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
• 63,720 points
2,217 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, 2023 in Others by narikkadan
• 63,720 points
801 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, 2023 in Others by narikkadan
• 63,720 points
962 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
1,094 views
0 votes
1 answer

Excel AVERAGEIF function in combination with MOD to average over every nth row not behaving as expected

Average Every Nth Row All three finish in ...READ MORE

answered Mar 27, 2023 in Others by narikkadan
• 63,720 points
353 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
• 63,720 points
3,560 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