Filling In An M by N Matrix Based On Row And Column Constraints

[acemanhattan]

Here's the scenario: You have 1,2,3,...N tasks to do sequentially, all which take a range of time to complete (column constraint), but you're only allowed to set aside a certain amount of time on each of M days (row constraint) to complete these tasks.

Example: You have a 5 chapter book to read, but you can only set enough time aside to read 12 pages a day during the week and, oddly, 6 pages a day during the weekend.

In our example the matrix acts as a "schedule" that shows how many pages of each chapter you'll read on a given day, and projects when you'll finish reading the book.

I envision a matrix that looks something like the following

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD]Chapter 1[/TD]
[TD]Chapter 2[/TD]
[TD]Chapter 3[/TD]
[TD]Chapter 4[/TD]
[TD]Chapter 5[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]29[/TD]
[TD]8[/TD]
[TD]14[/TD]
[/TR]
[TR]
[TD]Day 1[/TD]
[TD]12[/TD]
[TD]9[/TD]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 2[/TD]
[TD]12[/TD]
[TD][/TD]
[TD]8[/TD]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 3[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 4[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Day 5[/TD]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]7[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]Day 6[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]Day 7[/TD]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]4[/TD]
[/TR]
</tbody>[/TABLE]

I spent a few hours yesterday trying to do this with a combination of if(sum()) type statements, but couldn't figure out anything the would work, so I appreciate any solutions you might have (though I prefer Non VBA solutions).

 

[mikerickson]

This formula should work for you. The 0's can be removed via conditional formatting, or by a very ugly IF formula.
Note that the blank row and column are needed, but can be hidden if desired.

Unknown
	A	B	C	D	E	F	G	H
1				Chapter 1	Chapter 2	Chapter 3	Chapter 4	Chapter 5
2				9	11	29	8	14
3
4	Day 1	12		9	3	0	0	0
5	Day 2	12		0	8	4	0	0
6	Day 3	12		0	0	12	0	0
7	Day 4	12		0	0	12	0	0
8	Day 5	12		0	0	1	8	3
9	Day 6	6		0	0	0	0	6
10	Day 7	6		0	0	0	0	5
Sheet1

Cell Formulas
Range	Formula
D4	=MIN(D$2-SUM(D$3:D3),$B4-SUM($C4:C4))

 

[acemanhattan]

That works great. Thanks for taking the time.