Excel Formula Patterns

[default_name]

Hello,

I have an issue that I have really been scratching my head on.
Perhaps I am overthinking this, but I will try to describe it in detail.

I have a large spreadsheet (over 5k rows).
There is a formula pattern that I cant seem to get right when I try to apply it all the way down the worksheet.

I'll explain the first couple rows.
A value is gathered from another slightly smaller sheet (called 'Data Input') and placed on cell H2.
The row beneath it will keep track of a cumulative count from the data above it, beginning in H3.
A value is gathered from the 'Data Input' on cell I2, and beneath it the cumulative count is calculated.

Not sure if that makes sense. I will try to illustrate below.

Here are the formula patterns that I would WANT to happen:

	...H	I	J	K		DI
...2	='Data Input'!H2	='Data Input'!I2	='Data Input'!J2	='Data Input'!K2	.....	='Data Input'!DI2
3	=H2	=H3+I2	=I3+J2	=J3+K2	.....	=DH3+DI2
4	='Data Input'!H3	='Data Input'!I3	='Data Input'!I3	='Data Input'!I3	.....	='Data Input'!DI3
5	=H4	=H5+I4	=I5+J4	=J5+K4	.....	=DH5+DI4
6	='Data Input'!H4	='Data Input'!I4	='Data Input'!I4	='Data Input'!I4	.....	='Data Input'!DI4
7	=H6	=H7+I6	=I7+J6	=J7+K6	.....	=DH7+DI6
8	='Data Input'!H5	='Data Input'!I5	='Data Input'!I5	='Data Input'!I5	.....	='Data Input'!DI5
9	=H8	=H9+I8	=I9+J8	=J9+K8	.....	=DH9+DI8
10	='Data Input'!H6	='Data Input'!I6	='Data Input'!I6	='Data Input'!I6	.....	='Data Input'!DI6
11	=H10	=H11+I10	=I11+J10	=J11+K10	.....	=DH11+DI10

Here are the formulas that CURRENTLY happen (the issue).

	...H	I	J	K		DI
...2	='Data Input'!H2	='Data Input'!I2	='Data Input'!J2	='Data Input'!K2	.....	='Data Input'!DI2
3	=H2	=H3+I2	=I3+J2	=J3+K2	.....	=DH3+DI2
4	='Data Input'!H3	='Data Input'!I3	='Data Input'!J3	='Data Input'!K3	.....	='Data Input'!DI3
5	=H4	=H5+I4	=I5+J4	=J5+K4	.....	=DH5+DI4
6	='Data Input'!H6	='Data Input'!I6	='Data Input'!J6	='Data Input'!K6	.....	='Data Input'!DI6
7	=H6	=H7+I6	=I7+J6	=J7+K6	.....	=DH7+DI6
8	='Data Input'!H7	='Data Input'!I7	='Data Input'!J7	='Data Input'!K7	.....	='Data Input'!DI7
9	=H8	=H9+I8	=I9+J8	=J9+K8	.....	=DH9+DI8
10	='Data Input'!H10	='Data Input'!I10	='Data Input'!J10	='Data Input'!K10	.....	='Data Input'!DI10
11	=H10	=H11+I10	=I11+J10	=J11+K10	.....	=DH11+DI10

What am I doing wrong? Is there a more simple way to do this instead of hard-coding formulas into each cell?

I hope that makes sense.
Thanks in advance!

 

[Marcelo Branco]

Not sure i understand what you need. See if this example with a small data sample helps

Sheet Data Input

Pasta1
	H	I	J	K
1	Header1	Header2	Header3	Header4
2	10	30	70	120
3	11	31	71	121
4	12	32	72	122
5	13	33	73	123
6	14	34	74	124
Data Input

Other Sheet

Pasta1
	H	I	J	K
1	Header1	Header2	Header3	Header4
2	10	30	70	120
3	10	40	110	230
4	11	31	71	121
5	11	42	113	234
6	12	32	72	122
7	12	44	116	238
8	13	33	73	123
9	13	46	119	242
10	14	34	74	124
11	14	48	122	246
Plan3

Cell Formulas
Range	Formula
H2:H11	H2	=IF(MOD(ROWS(H$2:H2),2)=1, INDEX('Data Input'!H$2:H$8,INT((ROWS(H$2:H2)-1)/2)+1),H1)
I2:K11	I2	=IF(MOD(ROWS(I$2:I2),2)=1, INDEX('Data Input'!I$2:I$8,INT((ROWS(I$2:I2)-1)/2)+1),SUM(I1,H2))

Observe that the formula in column H (first column) is slightly different.
To others columns simply type the formula in I2, copy down and drag to the right

M.

 

[default_name]

Perfect! Thanks Marcelo!

 

[Marcelo Branco]

You're welcome. Glad to help

M.