Help needed making portions of a drag down formula skip cells

[vmack]

I have set up a formula to sum data from specific columns in multiple sheets in the same file. The simple formula had worked perfectly until I needed the data from the last sheet. In all of the other sheets the data that I need to pull is all in consecutive rows but in the last sheet the data that I need pulled and added is in every 4th row. The sheet looks like this:

All of the columns I've filled in work fine. The ones blank are the ones I am running into issues with. When I drag down the formulas excel pulls the next consecutive rows, which is perfect for the first four sheets I am summing, but for the fifth sheet (I'll ref the Test1 column) I need it to pull 'Wayne 2014'!C6 and then 'Wayne 2014'!C10 in the next row and so forth.

[TABLE="width: 400"]
<tbody>[TR]
[TD]Date[/TD]
[TD]All Test[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[TD]Test4[/TD]
[TD]$ Amt.[/TD]
[/TR]
[TR]
[TD]Jan.16[/TD]
[TD]=SUM(C2+D2+E2+F2)[/TD]
[TD]=SUM('Gennessee 2014'!C2+'Kalamazoo 2014'!C2+'Kent 2014'!C2+'Oakland 2014'!C2+'Wayne 2014'!C2)[/TD]
[TD]=SUM('Gennessee 2014'!D2+'Kalamazoo 2014'!D2+'Kent 2014'!D2+'Oakland 2014'!D2+'Wayne 2014'!D2)[/TD]
[TD]=SUM('Kalamazoo 2014'!E2)[/TD]
[TD]=SUM('Gennessee 2014'!E2+'Kalamazoo 2014'!F2+'Kent 2014'!E2+'Oakland 2014'!E2+'Wayne 2014'!E2)[/TD]
[TD]=SUM('Gennessee 2014'!F2+'Kalamazoo 2014'!G2+'Kent 2014'!F2+'Oakland 2014'!F2+'Wayne 2014'!F2)[/TD]
[/TR]
[TR]
[TD]Jan.31[/TD]
[TD]=SUM(C3+D3+E3+F3)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Feb.16[/TD]
[TD]=SUM(C4+D4+E4+F4)[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

The first four sheets look like this, which is why they work fine:

[TABLE="width: 400"]
<tbody>[TR]
[TD]Date[/TD]
[TD]All Test[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[TD]$ Amt.[/TD]
[/TR]
[TR]
[TD]Jan. 16[/TD]
[TD]=SUM(C2:E2)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Jan. 31[/TD]
[TD]=SUM(C3:E3)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Feb. 1[/TD]
[TD]=SUM(C4:E4)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
</tbody>[/TABLE]

The fifth sheet looks like this:
I only want the formulas from the first sheet to pull the data from the dated rows in this sheet.

[TABLE="width: 400"]
<tbody>[TR]
[TD]Date[/TD]
[TD]All Test[/TD]
[TD]Test1[/TD]
[TD]Test2[/TD]
[TD]Test3[/TD]
[TD]$ Amt.[/TD]
[/TR]
[TR]
[TD]Jan. 16[/TD]
[TD]=SUM(C2:E2)[/TD]
[TD][TABLE="width: 35"]
<tbody>[TR]
[TD="class: xl65, width: 35, align: right"]=SUM(C3:C5)[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]=SUM(D3:D5)[/TD]
[TD]=SUM(E3:E5)[/TD]
[TD]=SUM(F3:F5)[/TD]
[/TR]
[TR]
[TD]Div.1[/TD]
[TD]=SUM(C3:E3)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.2[/TD]
[TD]=SUM(C4:E4)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.3[/TD]
[TD]=SUM(C5:E5)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Jan.31[/TD]
[TD]=SUM(C6:E6)[/TD]
[TD]=SUM(C7:C9)[/TD]
[TD]=SUM(D7:D9)[/TD]
[TD]=SUM(E7:E9)[/TD]
[TD]=SUM(F7:F9)[/TD]
[/TR]
[TR]
[TD]Div.1[/TD]
[TD]=SUM(C7:E7)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.2[/TD]
[TD]=SUM(C8:E8)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.3[/TD]
[TD]=SUM(C9:E9)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Feb.1[/TD]
[TD]=SUM(C10:E10)[/TD]
[TD]=SUM(C11:C13)[/TD]
[TD]=SUM(D11:D13)[/TD]
[TD]=SUM(E11:E13)[/TD]
[TD]=SUM(F11:F13)[/TD]
[/TR]
[TR]
[TD]Div.1[/TD]
[TD]=SUM(C11:E11)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.2[/TD]
[TD]=SUM(C12:E12)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
[TR]
[TD]Div.3[/TD]
[TD]=SUM(C13:E13)[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD]$1[/TD]
[/TR]
</tbody>[/TABLE]

Is there any way in Excel 2010 to get it do what I want?

I apologize if I have given unnecessary information. I am horrible at explaining things and am new to the forum but I wanted to make sure everything was clear.

Any help would be appreciated.

Thank you!

Vi

(I am not sure how that one cell became outlined and I do not know how to fix it. Sorry if that confused anything.)

 

[vmack]

Also, I believe the computer I am using for this runs on Windows 7.

 

[vmack]

Bump.

 

[vmack]

Ok. So I've been trial and erroring variations of function similar to what I need for like an hour. I finally got to this:
=SUM('Gennessee 2014'!F2+'Kalamazoo 2014'!G2+'Kent 2014'!F2+'Oakland 2014'!F2+'Wayne 2014'!F2*(MOD(ROW('Wayne 2014'!F2)-1,4)))
It works perfectly when I enter it in but when I drag it down...not so much. I can't figure out how to fix it. If someone could please please help me with this I would greatly appreciate it.

Vi

 

[vmack]

Bump