# How can I drag a formula that references one row that extends to multiple row without jumping cell due to the multiple row

I apologize for the question being vague as I can't seem to put my problem into words. I will try.

I have data spanning 400 rows in excel. In another sheet, a row would extend to multiple row. Let's say one row from the original row is equivalent to 8 rows in the new sheet with formula. Now if I copy/drag the formula of the 8 rows below, I want it to reference Row 2 instead of row 9.

• Build proper reference using `(ROW()+const)/8` and `INDIRECT()`. This is draggable. – Akina Oct 7 at 8:25
• Thank you for this. Can you expand/explain this further? I appreciate it. – Josh Oct 7 at 9:01

Example.

Create new workbook with worksheets named "Sheet1" and "Sheet2".

Fill column A on Sheet1 with some data.

Put the next formula into A1 on Sheet2:

`=INDIRECT("Sheet1!R"&INT((ROW()+7)/8)&"C1",FALSE)`

Drag thes formula down.

The values from Sheet1 will be copied onto Sheet1 by blocks 8 copies in each (i.e. A1 will be copied into A1:A8, A2 into A9:A16 and so on).

To adjust the position in any particular case you need to adjust the constant values:

`+7`, which defines the final offset

`8`, which defines the block size

`1`, which defines the source column

You may use the same technique if you need to create horizontal blocks - in such case you must perform calculations in column part of the address (replace `"C1"` with some `"C"&INT((COLUMN()+const1)/const2)` and use constant row part). Or you may create rectangle blocks using formulas in both address parts.

`Sheet1!` constant sets source worksheet. While copying in the same worksheet it can be skipped.