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 452 views

## 1 answer to this question.

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)`
• 63,160 points

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

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

## IF function in combination with an Round function Excel

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

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

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

## 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

## 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

## Retrieve epay.info Balance with VBA and Excel

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