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

		Chapter 1	Chapter 2	Chapter 3	Chapter 4	Chapter 5
		9	11	29	8	14
Day 1	12	9	3
Day 2	12		8	4
Day 3	12			12
Day 4	12			12
Day 5	12			1	7	4
Day 6	6					6
Day 7	6					4

<tbody>
</tbody>

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.