Non Sequential formula copying

**Clementina** · 03-31-2016, 10:51 AM

Hi all,

I'm trying to create a worksheet that brings together data from 9 other worksheets into a collated doc for analysis. I have been trying to work out how to copy the formulas without the pattern jumping 9 rows each time.

Eg.
Main Sheet:
E3 refers to Sheet1 D5
E4 refers to Sheet2 D5
E5 refers to Sheet3 D5
E6 refers to Sheet4 D5 etc.

when I get to E12 I want it to refer to Sheet1 D6, E13 to Sheet2 D6 etc. but instead I get a 9 row jump so E12 refers to D14

Help! I've tried to create an indirect formula but don't think I've got the math right as its not working when I drag down. Any genius solutions?

Massive thank you in advance. I feel like there is a simple solution but I'm just not seeing it.

**Tony Valko** · 03-31-2016, 11:48 AM

This is super convoluted and I hope it doesn't get any more complicated!

Entered in E3 and copied down:

=INDIRECT("Sheet"&MOD(ROWS(E$3:E3)-1,9)+1&"!D"&4+CEILING(ROWS(E$3:E3)/9,1))