0 votes

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

Sep 25, 2022 in Others 759 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.

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)`
answered Sep 26, 2022 by
• 63,700 points

0 votes
1 answer

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

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

0 votes
1 answer

## IF function in combination with an Round function Excel

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

0 votes
1 answer

## Excel - IF Formula with a FIND

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

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

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

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

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

0 votes
1 answer

## Retrieve epay.info Balance with VBA and Excel

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

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

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