Autofilling Array Formulas Across Cells

[jr4ss]

I am in the process of putting together a spreadsheet that tracks tasks assigned to various team members. The issue that I am having is that the formula I am using is not populating as it should from cell to cell.

For example: I'm using the formula {=SUM(JANUARY!B$19:H$19)} to give me a total of the daily tasks assigned to each member for the first week in January. What I am wanting to do is be able to drag that formula across the next 3 cells and have the formula recognize that I need the next 3 sets of array data (I$19:O$19; then P$19:V$19; etc...) to make up the remaining 3 weeks in the month.

The issue I am running into however, is that when I attempt to populate the formula across the succeeding cells, the formula assumes that I want C$19:I$19 then D$19:J$19 instead of picking up where the previous array left off.

Any thoughts or suggestions would be greatly appreciated!

 

[JoeMo]

Here's one possible solution that doesn't require an array formula, but does use a volatile function. Copy the formula in B21 across.

Excel Workbook
	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V
19	2	3	4	5	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22
20
21	35	84	133
Sheet6

 

[jr4ss]

Thanks! I'll give that a shot.