0

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
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.