Excel formula not carrying down correctly, using wrong intervals of rows?

[lichldo]

Hello, including a sample of my sheet to help explain below. What is included was done manually, but I have to repeat this a few dozen more times so hoping to figure out how to drag this down correctly? Essentially I am trying to summarize data from another sheet. In this summary sheet, it needs to read every 30th row from the raw data sheet. However when I try to carry the set formulas on to the next PM "section", it puts 13 rows in between the data, not 30 - I think because my summary sheet has 13 rows in between each "set"? I'm struggling with how to explain this better but hopefully that gets the idea across?

So for more info, on this summary sheet, my first "section" for PM A -
C2 ='Option 1 Data'!B2
C3 ='Option 1 Data'!B32
C4 ='Option 1 Data'!B62
etc. - so you see it's reading every 30th row from my other sheet named Option 1 Data

Now my second "section" for PM B should add 1 to each of the above -
C15 ='Option 1 Data'!B3
C16 ='Option 1 Data'!B33
C17 ='Option 1 Data'!B63

so still every 30th row, but 1 down from PM A

I need to repeat this many times so trying to figure out a way to copy/paste this or drag it down accurately?

BP Aggregation by PM.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI
1	PM	Month	1st	2nd	3rd	4th	5th	6th	7th	8th	9th	10th	11th	12th	13th	14th	15th	16th	17th	18th	19th	20th	21st	22nd	23rd	24th	25th	26th	27th	28th	29th	30th	31st	Mid Month Difference	Month Difference
2	A	January	0	0	47829.41	47829.41	47829.41	47829.41	47829.41	0	0	47829.41	47829.41	39755	39755	39755	0	0	39755	39755	39755	39755	46935	0	0	46935	46935	46935	46935	66220.71	0	0	64320.71	61.79%	N/A
3		February	104,502.35	17,175.00	17,175.00	17,175.00	0.00	0.00	17,175.00	11,775.00	11,775.00	11,775.00	11,775.00	0.00	0.00	11,775.00	11,775.00	11,775.00	11,775.00	11,775.00	0.00	0.00	11,775.00	11,775.00	11,775.00	11,775.00	9,075.00	0.00	0.00	9,075.00	0.00	0.00	0.00	-100.00%	-100.00%
4		March	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
5		April	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
6		May	0	19521.43	19521.43	0	0	30443.06	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
7		June	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
8		July	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
9		August	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
10		September	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
11		October	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
12		November	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
13		December	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	0	N/A	N/A
14
15	B	January	0	0	93027.45	93027.45	71062.41	71062.41	69517.97	0	0	58011.86	58011.86	58011.86	58011.86	58011.86	0	0	58011.86	58011.86	58011.86	58011.86	58011.86	0	0	58280.61	57245.66	57245.66	56178.54	55756.58	0	0	55756.58
16		February	125,987.79	125,987.79	125,987.79	125,987.79	0.00	0.00	125,987.79	74,109.35	74,603.38	74,603.38	74,603.38	0.00	0.00	74,603.38	68,565.56	68,565.56	66,359.55	66,529.55	0.00	0.00	63,811.63	63,811.63	63,811.63	63,534.96	63,412.65	0.00	0.00	63,412.65	0.00	0.00	0.00
17		March	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
18		April	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
19		May	0.00	88,200.08	88,200.08	88,200.08	137,068.52	95,666.35	0.00	0.00	88,219.92	88,219.92	67,583.79	67,583.79	67,583.79	0.00	0.00	88,219.92	88,219.92	67,583.79	67,583.79	67,583.79	0.00	0.00	74,685.47	74,685.47	78,715.17	77,698.28	76,507.46	0.00	0.00	76,507.46	76,507.46
20		June	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
21		July	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
22		August	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
23		September	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
24		October	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
25		November	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
26		December	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00	0.00
27
Option 1 Summary by PM

Cell Formulas
Range	Formula
C2:AG2	C2	='Option 1 Data'!B2
AH2:AH3,AH11	AH2	=IFERROR((AG2-P2)/P2,"N/A")
AI2:AI13	AI2	=IFERROR((AG2-C2)/C2,"N/A")
C3:AG3	C3	='Option 1 Data'!B32
C4	C4	='Option 1 Data'!B62
D4:AG4	D4	='Option 1 Data'!D62
AH4:AH5,AH7:AH10,AH12:AH13	AH4	=IFERROR((AG4-Q4)/Q4,"N/A")
C5:AG5	C5	='Option 1 Data'!B92
C6:AG6	C6	='Option 1 Data'!B122
AH6	AH6	=IFERROR((AG6-R6)/R6,"N/A")
C7:AG7	C7	='Option 1 Data'!B152
C8:AG8	C8	='Option 1 Data'!B182
C9:AG9	C9	='Option 1 Data'!B212
C10:AG10	C10	='Option 1 Data'!B242
C11:AG11	C11	='Option 1 Data'!B272
C12:AG12	C12	='Option 1 Data'!B302
C13:AG13	C13	='Option 1 Data'!B332
C15:AG15	C15	='Option 1 Data'!B3
C16:AG16	C16	='Option 1 Data'!B33
C17:AG17	C17	='Option 1 Data'!B63
C18:AG18	C18	='Option 1 Data'!B93
C19:AG19	C19	='Option 1 Data'!B123
C20:AG20	C20	='Option 1 Data'!B153
C21:AG21	C21	='Option 1 Data'!B183
C22:AG22	C22	='Option 1 Data'!B213
C23:AG23	C23	='Option 1 Data'!B243
C24:AG24	C24	='Option 1 Data'!B273
C25:AG25	C25	='Option 1 Data'!B303
C26:AG26	C26	='Option 1 Data'!B333

 

[StephenCrump]

Will this work for you:

	A	B	C	D	E	F	G
1		Month	1st	2nd	3rd	4th	5th
2		January	b2	c2	etc	0	0
3		February	b32	c32	etc	0	0
4		March	b62	c62	etc	0	0
5		April
6		May
7		June
8		July
9		August
10		September
11		October
12		November
13		December
14
15		January	b3	c3	etc	0	0
16		February	b33	c33	etc	0	0
17		March	b63	c63	etc	0	0
18		April
19		May
20		June
21		July
22		August
23		September
24		October
25		November
26		December
27
28		January	b4	c4	etc	0	0
29		February	b34	c34	etc	0	0
30		March	b64	c64	etc	0	0
31		April
32		May
33		June
34		July
35		August
36		September
37		October
38		November
39		December
Sheet1

Cell Formulas
Range	Formula
C28:AG30,C15:AG17,C2:AG4	C2	=INDEX('Option 1 Data'!B:AF,INT(ROWS(C$2:C2)/13)+30*MOD(ROWS(C$2:C2),13)-28,)
Dynamic array formulas.

	A	B	C	D
1
2		b2	c2	etc
3		b3	c3	etc
4		b4	c4	etc
31
32		b32	c32	etc
33		b33	c33	etc
34		b34	c34	etc
61
62		b62	c62	etc
63		b63	c63	etc
64		b64	c64	etc
65
Option 1 Data

 

[lichldo]

Will this work for you:

	A	B	C	D	E	F	G
1		Month	1st	2nd	3rd	4th	5th
2		January	b2	c2	etc	0	0
3		February	b32	c32	etc	0	0
4		March	b62	c62	etc	0	0
5		April
6		May
7		June
8		July
9		August
10		September
11		October
12		November
13		December
14
15		January	b3	c3	etc	0	0
16		February	b33	c33	etc	0	0
17		March	b63	c63	etc	0	0
18		April
19		May
20		June
21		July
22		August
23		September
24		October
25		November
26		December
27
28		January	b4	c4	etc	0	0
29		February	b34	c34	etc	0	0
30		March	b64	c64	etc	0	0
31		April
32		May
33		June
34		July
35		August
36		September
37		October
38		November
39		December
Sheet1

Cell Formulas
Range	Formula
C28:AG30,C15:AG17,C2:AG4	C2	=INDEX('Option 1 Data'!B:AF,INT(ROWS(C$2:C2)/13)+30*MOD(ROWS(C$2:C2),13)-28,)
Dynamic array formulas.

	A	B	C	D
1
2		b2	c2	etc
3		b3	c3	etc
4		b4	c4	etc
31
32		b32	c32	etc
33		b33	c33	etc
34		b34	c34	etc
61
62		b62	c62	etc
63		b63	c63	etc
64		b64	c64	etc
65
Option 1 Data

I'm sorry but can you explain what do to with that?

 

[StephenCrump]

I have given you a single formula for C2, which can replace the individual formulae you currently have in C2:G2 of your Option 1 Summary by PM sheet.

You can copy that single formula down to C2:C13, C15:C26, C28:C39 etc.

The Option 1 Data sheet I attached just has dummy data to illustrate which cells the formula is pointing to.

Did you know that you can click here, go to A1 in a blank worksheet, and paste the contents, including formulae?

 

[bebo021999]

I doubt that from row B32, next left cell A32 has "A" or "B" ?
like:
row 32: A ----January
row 33: B ----January

If yes, another approach with SUMIF would be the best.