You can see the formula for column I in the screenshot below.
PointsSpent is a macro I wrote for this, though it may not be necessary. I don't know worksheet functions very well, so I often go straight for code. Complete code is down below. The macro takes three paramaters:
The first parameter is a range object. This range contains the "Tree" column from the first row to the current row. The $ means absolute reference, not relative, so it always starts from row 2. When you propagate this formula to fill the whole column, it becomes D$2:D3, D$2:D4, D$2:D5, etc.. Always row 2 to the current row.
Similar to the previous parameter, this range object contains the actual AP spent. (AP spent is toggled on/off using the TRUE column.)
This is simply the current row's "Tree." This parameter is how the code knows which AP from the ranges to add up; if the "Tree" column is equal to this parameter for a row, that row's AP is added. Otherwise it's skipped.
So now looking at the image below, consider the enhancements at the start of level 5. Row 12 shows we've spent 6 AP in the Pale Master tree. This total is inclusive, meaning we'd already spent 5 AP in tree before taking this 1 AP enhancement. This satisfies the "points in tree" prereq for a tier 2 core ability, so we're good to go. Row 13 shows we've spent a whopping 12 AP in Archmage, so that should be safe for a while. Time to go heavy on PM!
You can see from the screenshot in the OP that my order does not satisfy all points in tree prereqs; School Mastery taken at level 10.2 fails, as it's a tier 4 but I haven't yet spent 20 AP in the AM tree. As I said in the OP, this order was a rough draft.
Uploaded with ImageShack.us
Here's the complete code:
I'm using a very old XP box with Excel 2002, so I can't say for sure how to work with macros in newer versions of excel. If you have no idea how to add that code, the easiest way is typically:
Private Function PointsSpent(prngTree As Range, prngAP As Range, pstrTree) As Long
Dim lngAP As Long
Dim i As Long
For i = 1 To prngTree.Rows.Count
If prngTree(i, 1) = pstrTree Then lngAP = lngAP + prngAP(i, 1)
PointsSpent = lngAP
Record a Macro (type in random letters) and Save Macro.
When you choose Edit Macro the code window will (hopefully) open up. You can then copy the above 9 lines of code to the bottom of that window and then save the spreadsheet. With any luck, that's all you need to do.