Dynamic ranges do not work as they should

[computaplanet]

Hi all,

I would dearly love someone to help me with this, I am beginning to think that it is impossible to achieve and am on the verge of giving up, I have even done some creative thinking but I still come up short.... anyway...


Basically I have 2 tables, table one:

A21 - A32 start date
B21 - B32 End date
C21 - C32 (Blank for other reasons)
D21 - D32 Amount

Table 2

B27 - B39 Start Date
C27 - C39 End Date
D27 - D39 Number of days between start and end dates - (C-B = no.days)
E27 - E39 Amount in payment
F27 - E39 Daily amount - amount in payment / Number of days

Basically I have a formula in the Cells D21 to D32 which works exactly as it should with the exception that if my date range in table 2 expands across 2 date ranges in table one then the "remainder amount" doesnt get posted to the next correct cell ... so it just gets lost in cyber space example:

if the date range is 61 days which therefore spans 2 date ranges in table one. The formula splits the amount in table 2 and puts the first correct amount (31 days * £10 = £310 (worked out using the second table) within the correct cell in table 1, however the amount paid was £610 and so the remaining £300 should also be posted within the correct cell in table 1 that the date range from table 2 corresponds (usually the next one down).

I have kind of figured out how to get this to happen (in a roundabout fashion) however it still doesnt work:

I think if I add a helper table to get the formula to work out one column (as it does now) then work out the remaining amount place that in a second column and then user another Dynamic range to place them in the correct cells within the original table 1 (Column D21 - D32) of course you may see something which is much simpler which can achieve the same result - however this is the only way i can see that will actually allow me to

1. Figure out the remainder and place in the correct cell
2. Actually be able to add 2 or more amounts into the cells in table 1 (a seperate issue - the formula will at some point need to add 2 or maybe 3 amounts together in the same cell because the dates correspond

My problem is I cant figure out these dynamic ranges in the slightest, I can get simple forumlas but ones as long as the one I am using in D21-D32 I am totally lost with...

The formula I have which splits the amounts but loses the second amount is:

=SUMPRODUCT(($A21<=$B$37:$B$49)*($B21>=$B$37:$B$49)*($B21>=$C$37:$C$49)*$E$37:$E$49)+SUMPRODUCT(($A21<=$B$37:$B$49)*($B21>=$B$37:$B$49)*($B21<$C$37:$C$49)*(($B21-$B$37:$B$49)*$F$37:$F$49+$E$37:$E$49/$D$37:$D$49))+SUMPRODUCT($B21<$B$37:$B$49*$D22)

I hope I have made sense, it would be much easier to show you if I could place an attachment of the spreadsheet but this forum will not allow it for some reason? Please feel free to ask me any questions and thanks in advance for any help you can give to me...

 

[computaplanet]

Hi again alphafrog can you tell me where I am supposed to put the function code? I have posted into the vba window and also pasted the formula provided into the table however I am getting a "name" error in the cell and cant see where i run the vba code from

sorry if I seem thick, I have only ever used the vba screen once before and that was with direction too

 

[AlphaFrog]

• Alt+F11 to open the VBA editor
• Select from the VBA menu Insert\ Module
• Paste the code in the VBA window

Back in Excel, put the formula in a cell just like any other formula. It should calculate automatically like any other formula.

Select cell D21 and enter this forlmula...
=PCMTotal(A21,B21,$B$37:$C$47,$E$37:$E$47)
...and copy it down column D

If you entered in the formula in a cell before you had pasted the code, you may get that NAME error. Hit F9 on the keyboard to recalculate the formulas.

 

[computaplanet]

Hi again alphafrog, Thanks for that, I had done it right but I didnt know about the F9 to recalculate.

Thanks for having a look, I really do appreciate it but I cant get the solution to work properly, it works for the details i have entered as an example but when I change the examples it doesnt work as it should - it doesnt add amounts up correctly (i.e. if I put an additional payment of £1000 into the second table for 01/03/11 - 20/03/2011 - using the current example sheet it returns the value of 1110 - when it should be 1310). Do you have any ideas as to why this would be happening?

Cheers again!

 

[AlphaFrog]

In the VBA code, change this one line...

vDays(j) = sDaily

To this...

vDays(j) = [COLOR="Red"]vDays(j) + [/COLOR]sDaily

Then back in Excel, hit the F9 key to recalculate.

 

[computaplanet]

Superb AlphaFrog, Superb, Superb, Superb, Superb!!!

Thankyou thankyou Thankyou thankyou thankyou

And thankyou once again!!

You have totally and utterly surpassed all my expectations with providing me with this!! I had given up all hope of ever finding a solution!

I hope you dont mind but I would like to add you in my profile and if I get any other bits and bobs I cant figure out in the future I would like to ask you if you would look at it for me, if you have the time to of course! ( I promise that it wouldnt be anything trivial lol)

I am so happy right now you would not believe!!

 

[AlphaFrog]

You're welcome.

 

[computaplanet]

Hi alpha frog, I have a very similar issue with another part of the spreadsheet and as much as I look at your superb piece of code I cant work it out to adjust it anyway if I explain you should be able to quickly either tell me what i need to do or tell me it is impossible lol

anyway...

I have a table of 52 weeks of the year (again the start date is the one determined by the cell in d15.

The week start date determined by Cell D14 is in P4 and then down the page to Cell P56. The end date for the week is in Q4:Q56 Basically if a week falls within the date ranges in A21 and B21 (and so on down the tables) then the amount paid in cell T4 : T56 is put into Cell G20 (and so on down table 1 p dependant on which cell has the payment).

Now if the date range in table with weekly payments cross date ranges in table 1 it is to split the amount appropriately.

I am positive it would just be a little tweak of your code, I have a very complicated version using helper tables which makes it very messy and the amounts dont always end up being put in the correct cells in table 1, and I think that your code is ideal if it can be tweaked to suit...do you think this is possible?

 

[computaplanet]

This is my weekly table:

[RANGE=cls:xl2bb-100][XR][XH=cs:10]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]P[/XH][XH]Q[/XH][XH]R[/XH][XH]S[/XH][XH]T[/XH][XH]U[/XH][XH]V[/XH][XH]W[/XH][XH]X[/XH][/XR][XR][XH]2[/XH][XD=h:l|fw:b|bc:92d050]Weekly Payment Table[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]3[/XH][XD=h:c|fw:b|bc:c2d69a]Start Date[/XD][XD=h:c|fw:b|bc:c2d69a]End Date[/XD][XD=h:c|fw:b|bc:c2d69a]Covering Month(s)[/XD][XD=h:c|fw:b|bc:c2d69a]Amount Due[/XD][XD=h:c|fw:b|bc:c2d69a]Amount Paid[/XD][XD=h:c|fw:b|bc:c2d69a]Date Paid[/XD][XD=h:c|fw:b|bc:c2d69a]Bank[/XD][XD=h:c|fw:b|bc:c2d69a]BACS / Chq / Cash / SO[/XD][XD=h:c|fw:b|bc:c2d69a]Reference (if applicable)[/XD][/XR][XR][XH]4[/XH][XD=h:c]30/06/2011[/XD][XD=h:c]06/07/2011[/XD][XD=h:c|fw:b]Jun - Jul[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]5[/XH][XD=h:c]07/07/2011[/XD][XD=h:c]13/07/2011[/XD][XD=h:c|fw:b]Jul - Jul[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]6[/XH][XD=h:c]14/07/2011[/XD][XD=h:c]20/07/2011[/XD][XD=h:c|fw:b]Jul - Jul[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]7[/XH][XD=h:c]21/07/2011[/XD][XD=h:c]27/07/2011[/XD][XD=h:c|fw:b]Jul - Jul[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]8[/XH][XD=h:c]28/07/2011[/XD][XD=h:c]03/08/2011[/XD][XD=h:c|fw:b]Jul - Aug[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]9[/XH][XD=h:c]04/08/2011[/XD][XD=h:c]10/08/2011[/XD][XD=h:c|fw:b]Aug - Aug[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]10[/XH][XD=h:c]11/08/2011[/XD][XD=h:c]17/08/2011[/XD][XD=h:c|fw:b]Aug - Aug[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]11[/XH][XD=h:c]18/08/2011[/XD][XD=h:c]24/08/2011[/XD][XD=h:c|fw:b]Aug - Aug[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]12[/XH][XD=h:c]25/08/2011[/XD][XD=h:c]31/08/2011[/XD][XD=h:c|fw:b]Aug - Aug[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]13[/XH][XD=h:c]01/09/2011[/XD][XD=h:c]07/09/2011[/XD][XD=h:c|fw:b]Sep - Sep[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]14[/XH][XD=h:c]08/09/2011[/XD][XD=h:c]14/09/2011[/XD][XD=h:c|fw:b]Sep - Sep[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]15[/XH][XD=h:c]15/09/2011[/XD][XD=h:c]21/09/2011[/XD][XD=h:c|fw:b]Sep - Sep[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]16[/XH][XD=h:c]22/09/2011[/XD][XD=h:c]28/09/2011[/XD][XD=h:c|fw:b]Sep - Sep[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]17[/XH][XD=h:c]29/09/2011[/XD][XD=h:c]05/10/2011[/XD][XD=h:c|fw:b]Sep - Oct[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]18[/XH][XD=h:c]06/10/2011[/XD][XD=h:c]12/10/2011[/XD][XD=h:c|fw:b]Oct - Oct[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]19[/XH][XD=h:c]13/10/2011[/XD][XD=h:c]19/10/2011[/XD][XD=h:c|fw:b]Oct - Oct[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]20[/XH][XD=h:c]20/10/2011[/XD][XD=h:c]26/10/2011[/XD][XD=h:c|fw:b]Oct - Oct[/XD][XD=h:r] 100.00[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]21[/XH][XD=h:c]27/10/2011[/XD][XD=h:c]02/11/2011[/XD][XD=h:c|fw:b]Oct - Nov[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]22[/XH][XD=h:c]03/11/2011[/XD][XD=h:c]09/11/2011[/XD][XD=h:c|fw:b]Nov - Nov[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]23[/XH][XD=h:c]10/11/2011[/XD][XD=h:c]16/11/2011[/XD][XD=h:c|fw:b]Nov - Nov[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]24[/XH][XD=h:c]17/11/2011[/XD][XD=h:c]23/11/2011[/XD][XD=h:c|fw:b]Nov - Nov[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]25[/XH][XD=h:c]24/11/2011[/XD][XD=h:c]30/11/2011[/XD][XD=h:c|fw:b]Nov - Nov[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]26[/XH][XD=h:c]01/12/2011[/XD][XD=h:c]07/12/2011[/XD][XD=h:c|fw:b]Dec - Dec[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]27[/XH][XD=h:c]08/12/2011[/XD][XD=h:c]14/12/2011[/XD][XD=h:c|fw:b]Dec - Dec[/XD][XD=h:r]0[/XD][XD=h:r] -[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH=cs:10][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]P4[/XD][XD]=D17[/XD][/XR][XR][XD]P5[/XD][XD]=Q4+1[/XD][/XR][XR][XD]P6[/XD][XD]=Q5+1[/XD][/XR][XR][XD]P7[/XD][XD]=Q6+1[/XD][/XR][XR][XD]P8[/XD][XD]=Q7+1[/XD][/XR][XR][XD]P9[/XD][XD]=Q8+1[/XD][/XR][XR][XD]P10[/XD][XD]=Q9+1[/XD][/XR][XR][XD]P11[/XD][XD]=Q10+1[/XD][/XR][XR][XD]P12[/XD][XD]=Q11+1[/XD][/XR][XR][XD]P13[/XD][XD]=Q12+1[/XD][/XR][XR][XD]P14[/XD][XD]=Q13+1[/XD][/XR][XR][XD]P15[/XD][XD]=Q14+1[/XD][/XR][XR][XD]P16[/XD][XD]=Q15+1[/XD][/XR][XR][XD]P17[/XD][XD]=Q16+1[/XD][/XR][XR][XD]P18[/XD][XD]=Q17+1[/XD][/XR][XR][XD]P19[/XD][XD]=Q18+1[/XD][/XR][XR][XD]P20[/XD][XD]=Q19+1[/XD][/XR][XR][XD]P21[/XD][XD]=Q20+1[/XD][/XR][XR][XD]P22[/XD][XD]=Q21+1[/XD][/XR][XR][XD]P23[/XD][XD]=Q22+1[/XD][/XR][XR][XD]P24[/XD][XD]=Q23+1[/XD][/XR][XR][XD]P25[/XD][XD]=Q24+1[/XD][/XR][XR][XD]P26[/XD][XD]=Q25+1[/XD][/XR][XR][XD]P27[/XD][XD]=Q26+1[/XD][/XR][XR][XD]Q4[/XD][XD]=P4+6[/XD][/XR][XR][XD]Q5[/XD][XD]=P5+6[/XD][/XR][XR][XD]Q6[/XD][XD]=P6+6[/XD][/XR][XR][XD]Q7[/XD][XD]=P7+6[/XD][/XR][XR][XD]Q8[/XD][XD]=P8+6[/XD][/XR][XR][XD]Q9[/XD][XD]=P9+6[/XD][/XR][XR][XD]Q10[/XD][XD]=P10+6[/XD][/XR][XR][XD]Q11[/XD][XD]=P11+6[/XD][/XR][XR][XD]Q12[/XD][XD]=P12+6[/XD][/XR][XR][XD]Q13[/XD][XD]=P13+6[/XD][/XR][XR][XD]Q14[/XD][XD]=P14+6[/XD][/XR][XR][XD]Q15[/XD][XD]=P15+6[/XD][/XR][XR][XD]Q16[/XD][XD]=P16+6[/XD][/XR][XR][XD]Q17[/XD][XD]=P17+6[/XD][/XR][XR][XD]Q18[/XD][XD]=P18+6[/XD][/XR][XR][XD]Q19[/XD][XD]=P19+6[/XD][/XR][XR][XD]Q20[/XD][XD]=P20+6[/XD][/XR][XR][XD]Q21[/XD][XD]=P21+6[/XD][/XR][XR][XD]Q22[/XD][XD]=P22+6[/XD][/XR][XR][XD]Q23[/XD][XD]=P23+6[/XD][/XR][XR][XD]Q24[/XD][XD]=P24+6[/XD][/XR][XR][XD]Q25[/XD][XD]=P25+6[/XD][/XR][XR][XD]Q26[/XD][XD]=P26+6[/XD][/XR][XR][XD]Q27[/XD][XD]=P27+6[/XD][/XR][XR][XD]R4[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P4)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q4)&"/2011"),"mmm")[/XD][/XR][XR][XD]R5[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P5)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q5)&"/2011"),"mmm")[/XD][/XR][XR][XD]R6[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P6)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q6)&"/2011"),"mmm")[/XD][/XR][XR][XD]R7[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P7)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q7)&"/2011"),"mmm")[/XD][/XR][XR][XD]R8[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P8)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q8)&"/2011"),"mmm")[/XD][/XR][XR][XD]R9[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P9)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q9)&"/2011"),"mmm")[/XD][/XR][XR][XD]R10[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P10)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q10)&"/2011"),"mmm")[/XD][/XR][XR][XD]R11[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P11)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q11)&"/2011"),"mmm")[/XD][/XR][XR][XD]R12[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P12)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q12)&"/2011"),"mmm")[/XD][/XR][XR][XD]R13[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P13)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q13)&"/2011"),"mmm")[/XD][/XR][XR][XD]R14[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P14)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q14)&"/2011"),"mmm")[/XD][/XR][XR][XD]R15[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P15)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q15)&"/2011"),"mmm")[/XD][/XR][XR][XD]R16[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P16)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q16)&"/2011"),"mmm")[/XD][/XR][XR][XD]R17[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P17)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q17)&"/2011"),"mmm")[/XD][/XR][XR][XD]R18[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P18)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q18)&"/2011"),"mmm")[/XD][/XR][XR][XD]R19[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P19)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q19)&"/2011"),"mmm")[/XD][/XR][XR][XD]R20[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P20)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q20)&"/2011"),"mmm")[/XD][/XR][XR][XD]R21[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P21)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q21)&"/2011"),"mmm")[/XD][/XR][XR][XD]R22[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P22)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q22)&"/2011"),"mmm")[/XD][/XR][XR][XD]R23[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P23)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q23)&"/2011"),"mmm")[/XD][/XR][XR][XD]R24[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P24)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q24)&"/2011"),"mmm")[/XD][/XR][XR][XD]R25[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P25)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q25)&"/2011"),"mmm")[/XD][/XR][XR][XD]R26[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P26)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q26)&"/2011"),"mmm")[/XD][/XR][XR][XD]R27[/XD][XD]=TEXT(DATEVALUE("1/"&MONTH(P27)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q27)&"/2011"),"mmm")[/XD][/XR][XR][XD]S4[/XD][XD]=IF(AND( $AZ$1 >= P4, $D$13<= P4), $H$13,"0")[/XD][/XR][XR][XD]S5[/XD][XD]=IF(AND( $AZ$1 >= P5, $D$13<= P5), $H$13,"0")[/XD][/XR][XR][XD]S6[/XD][XD]=IF(AND( $AZ$1 >= P6, $D$13<= P6), $H$13,"0")[/XD][/XR][XR][XD]S7[/XD][XD]=IF(AND( $AZ$1 >= P7, $D$13<= P7), $H$13,"0")[/XD][/XR][XR][XD]S8[/XD][XD]=IF(AND( $AZ$1 >= P8, $D$13<= P8), $H$13,"0")[/XD][/XR][XR][XD]S9[/XD][XD]=IF(AND( $AZ$1 >= P9, $D$13<= P9), $H$13,"0")[/XD][/XR][XR][XD]S10[/XD][XD]=IF(AND( $AZ$1 >= P10, $D$13<= P10), $H$13,"0")[/XD][/XR][XR][XD]S11[/XD][XD]=IF(AND( $AZ$1 >= P11, $D$13<= P11), $H$13,"0")[/XD][/XR][XR][XD]S12[/XD][XD]=IF(AND( $AZ$1 >= P12, $D$13<= P12), $H$13,"0")[/XD][/XR][XR][XD]S13[/XD][XD]=IF(AND( $AZ$1 >= P13, $D$13<= P13), $H$13,"0")[/XD][/XR][XR][XD]S14[/XD][XD]=IF(AND( $AZ$1 >= P14, $D$13<= P14), $H$13,"0")[/XD][/XR][XR][XD]S15[/XD][XD]=IF(AND( $AZ$1 >= P15, $D$13<= P15), $H$13,"0")[/XD][/XR][XR][XD]S16[/XD][XD]=IF(AND( $AZ$1 >= P16, $D$13<= P16), $H$13,"0")[/XD][/XR][XR][XD]S17[/XD][XD]=IF(AND( $AZ$1 >= P17, $D$13<= P17), $H$13,"0")[/XD][/XR][XR][XD]S18[/XD][XD]=IF(AND( $AZ$1 >= P18, $D$13<= P18), $H$13,"0")[/XD][/XR][XR][XD]S19[/XD][XD]=IF(AND( $AZ$1 >= P19, $D$13<= P19), $H$13,"0")[/XD][/XR][XR][XD]S20[/XD][XD]=IF(AND( $AZ$1 >= P20, $D$13<= P20), $H$13,"0")[/XD][/XR][XR][XD]S21[/XD][XD]=IF(AND( $AZ$1 >= P21, $D$13<= P21), $H$13,"0")[/XD][/XR][XR][XD]S22[/XD][XD]=IF(AND( $AZ$1 >= P22, $D$13<= P22), $H$13,"0")[/XD][/XR][XR][XD]S23[/XD][XD]=IF(AND( $AZ$1 >= P23, $D$13<= P23), $H$13,"0")[/XD][/XR][XR][XD]S24[/XD][XD]=IF(AND( $AZ$1 >= P24, $D$13<= P24), $H$13,"0")[/XD][/XR][XR][XD]S25[/XD][XD]=IF(AND( $AZ$1 >= P25, $D$13<= P25), $H$13,"0")[/XD][/XR][XR][XD]S26[/XD][XD]=IF(AND( $AZ$1 >= P26, $D$13<= P26), $H$13,"0")[/XD][/XR][XR][XD]S27[/XD][XD]=IF(AND( $AZ$1 >= P27, $D$13<= P27), $H$13,"0")[/XD][/XR][/RANGE]

(it goes from P4 to P56)

Basically I put payments in the "payment made" column, when the date a payment is made falls within the first two columns

Ok this payment is then supposed to be added to the column in the original post's "table one" in the column "total payments made to account" (column G)

[RANGE=cls:xl2bb-100][XR][XH=cs:8]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]A[/XH][XH]B[/XH][XH]C[/XH][XH]D[/XH][XH]E[/XH][XH]F[/XH][XH]G[/XH][/XR][XR][XH]19[/XH][XD=h:c|fw:b|bc:c2d69a]PCM START DATE (DUE)[/XD][XD=h:c|fw:b|bc:c2d69a]PCM END DATE[/XD][XD=h:c|fw:b|bc:c2d69a]Rent Due (PCM)[/XD][XD=h:c|fw:b|bc:c2d69a]HB Payment[/XD][XD=h:c|fw:b|bc:c2d69a]Top up[/XD][XD=h:c|fw:b|bc:c2d69a]Actually Paid by Self[/XD][XD=h:c|fw:b|bc:c2d69a]Total Payments made to account[/XD][/XR][XR][XH]20[/XH][XD=h:c]30/06/2011[/XD][XD=h:c]29/07/2011[/XD][XD=h:c] 433.33[/XD][XD=h:c] 407.14[/XD][XD=h:r] 26.19[/XD][XD=h:r] 22.00[/XD][XD=h:r] 22.00[/XD][/XR][XR][XH]21[/XH][XD=h:c]30/07/2011[/XD][XD=h:c]29/08/2011[/XD][XD=h:c] 433.33[/XD][XD=h:c] 420.71[/XD][XD=h:r] 12.62[/XD][XD=h:r] 22.00[/XD][XD=h:r] 22.00[/XD][/XR][XR][XH]22[/XH][XD=h:c]30/08/2011[/XD][XD=h:c]29/09/2011[/XD][XD=h:c] 433.33[/XD][XD=h:c] 420.71[/XD][XD=h:r] 12.62[/XD][XD=h:r] 22.00[/XD][XD=h:r] 22.00[/XD][/XR][XR][XH]23[/XH][XD=h:c]30/09/2011[/XD][XD=h:c]29/10/2011[/XD][XD=h:c] 433.33[/XD][XD=h:c] 40.71[/XD][XD=h:r] 392.62[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]24[/XH][XD=h:c]30/10/2011[/XD][XD=h:c]29/11/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]25[/XH][XD=h:c]30/11/2011[/XD][XD=h:c]29/12/2011[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]26[/XH][XD=h:c]30/12/2011[/XD][XD=h:c]29/01/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]27[/XH][XD=h:c]30/01/2012[/XD][XD=h:c]29/02/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]28[/XH][XD=h:c]01/03/2012[/XD][XD=h:c]31/03/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]29[/XH][XD=h:c]01/04/2012[/XD][XD=h:c]30/04/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]30[/XH][XD=h:c]01/05/2012[/XD][XD=h:c]31/05/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]31[/XH][XD=h:c]01/06/2012[/XD][XD=h:c]30/06/2012[/XD][XD=h:c]0[/XD][XD=h:c] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH=cs:8][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]A20[/XD][XD]=D17[/XD][/XR][XR][XD]A21[/XD][XD]=DATE(YEAR(A20),MONTH(A20)+1,DAY(A20))[/XD][/XR][XR][XD]A22[/XD][XD]=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21))[/XD][/XR][XR][XD]A23[/XD][XD]=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22))[/XD][/XR][XR][XD]A24[/XD][XD]=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23))[/XD][/XR][XR][XD]A25[/XD][XD]=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24))[/XD][/XR][XR][XD]A26[/XD][XD]=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25))[/XD][/XR][XR][XD]A27[/XD][XD]=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26))[/XD][/XR][XR][XD]A28[/XD][XD]=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27))[/XD][/XR][XR][XD]A29[/XD][XD]=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28))[/XD][/XR][XR][XD]A30[/XD][XD]=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29))[/XD][/XR][XR][XD]A31[/XD][XD]=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30))[/XD][/XR][XR][XD]B20[/XD][XD]=DATE(YEAR(A20),MONTH(A20)+1,DAY(A20)-1)[/XD][/XR][XR][XD]B21[/XD][XD]=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)-1)[/XD][/XR][XR][XD]B22[/XD][XD]=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22)-1)[/XD][/XR][XR][XD]B23[/XD][XD]=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23)-1)[/XD][/XR][XR][XD]B24[/XD][XD]=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24)-1)[/XD][/XR][XR][XD]B25[/XD][XD]=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25)-1)[/XD][/XR][XR][XD]B26[/XD][XD]=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26)-1)[/XD][/XR][XR][XD]B27[/XD][XD]=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27)-1)[/XD][/XR][XR][XD]B28[/XD][XD]=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28)-1)[/XD][/XR][XR][XD]B29[/XD][XD]=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29)-1)[/XD][/XR][XR][XD]B30[/XD][XD]=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30)-1)[/XD][/XR][XR][XD]B31[/XD][XD]=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31)-1)[/XD][/XR][XR][XD]C20[/XD][XD]=IF(AND( $AZ$1 >= A20, $D$17<= A20), $B$17,"0")[/XD][/XR][XR][XD]C21[/XD][XD]=IF(AND( $AZ$1 >= A21, $D$17<= A21), $B$17,"0")[/XD][/XR][XR][XD]C22[/XD][XD]=IF(AND( $AZ$1 >= A22, $D$17<= A22), $B$17,"0")[/XD][/XR][XR][XD]C23[/XD][XD]=IF(AND( $AZ$1 >= A23, $D$17<= A23), $B$17,"0")[/XD][/XR][XR][XD]C24[/XD][XD]=IF(AND( $AZ$1 >= A24, $D$17<= A24), $B$17,"0")[/XD][/XR][XR][XD]C25[/XD][XD]=IF(AND( $AZ$1 >= A25, $D$17<= A25), $B$17,"0")[/XD][/XR][XR][XD]C26[/XD][XD]=IF(AND( $AZ$1 >= A26, $D$17<= A26), $B$17,"0")[/XD][/XR][XR][XD]C27[/XD][XD]=IF(AND( $AZ$1 >= A27, $D$17<= A27), $B$17,"0")[/XD][/XR][XR][XD]C28[/XD][XD]=IF(AND( $AZ$1 >= A28, $D$17<= A28), $B$17,"0")[/XD][/XR][XR][XD]C29[/XD][XD]=IF(AND( $AZ$1 >= A29, $D$17<= A29), $B$17,"0")[/XD][/XR][XR][XD]C30[/XD][XD]=IF(AND( $AZ$1 >= A30, $D$17<= A30), $B$17,"0")[/XD][/XR][XR][XD]C31[/XD][XD]=IF(AND( $AZ$1 >= A31, $D$17<= A31), $B$17,"0")[/XD][/XR][XR][XD]D20[/XD][XD]=PCMTotal(A20,B20,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D21[/XD][XD]=PCMTotal(A21,B21,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D22[/XD][XD]=PCMTotal(A22,B22,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D23[/XD][XD]=PCMTotal(A23,B23,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D24[/XD][XD]=PCMTotal(A24,B24,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D25[/XD][XD]=PCMTotal(A25,B25,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D26[/XD][XD]=PCMTotal(A26,B26,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D27[/XD][XD]=PCMTotal(A27,B27,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D28[/XD][XD]=PCMTotal(A28,B28,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D29[/XD][XD]=PCMTotal(A29,B29,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D30[/XD][XD]=PCMTotal(A30,B30,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]D31[/XD][XD]=PCMTotal(A31,B31,$B$37:$C$49,$E$37:$E$49)[/XD][/XR][XR][XD]E20[/XD][XD]=C20-BV20[/XD][/XR][XR][XD]E21[/XD][XD]=C21-BV21[/XD][/XR][XR][XD]E22[/XD][XD]=C22-BV22[/XD][/XR][XR][XD]E23[/XD][XD]=C23-BV23[/XD][/XR][XR][XD]E24[/XD][XD]=C24-BV24[/XD][/XR][XR][XD]E25[/XD][XD]=C25-BV25[/XD][/XR][XR][XD]E26[/XD][XD]=C26-BV26[/XD][/XR][XR][XD]E27[/XD][XD]=C27-BV27[/XD][/XR][XR][XD]E28[/XD][XD]=C28-BV28[/XD][/XR][XR][XD]E29[/XD][XD]=C29-BV29[/XD][/XR][XR][XD]E30[/XD][XD]=C30-BV30[/XD][/XR][XR][XD]E31[/XD][XD]=C31-BV31[/XD][/XR][XR][XD]G20[/XD][XD]=F20+BY20[/XD][/XR][XR][XD]G21[/XD][XD]=F21+BY21[/XD][/XR][XR][XD]G22[/XD][XD]=F22+BY22[/XD][/XR][XR][XD]G23[/XD][XD]=F23+BY23[/XD][/XR][XR][XD]G24[/XD][XD]=F24+BY24[/XD][/XR][XR][XD]G25[/XD][XD]=F25+BY25[/XD][/XR][XR][XD]G26[/XD][XD]=F26+BY26[/XD][/XR][XR][XD]G27[/XD][XD]=F27+BY27[/XD][/XR][XR][XD]G28[/XD][XD]=F28+BY28[/XD][/XR][XR][XD]G29[/XD][XD]=F29+BY29[/XD][/XR][XR][XD]G30[/XD][XD]=F30+BY30[/XD][/XR][XR][XD]G31[/XD][XD]=F31+BY31[/XD][/XR][/RANGE]

The way my tables work at the moment I have a helper tablet adds all the payments made in weekly table. the helper tables add the amounts together depending on the month they were paid. and if a week crosses into another month (for example 27/10/2011 - 02/11/2011) and a payment is made in this week then the helper table splits this payment so the 5 days of october are paid for and the 2 days of november is paid for. The helper tables then should help to put the amounts paid weekly into the correct cells in table 1 when the weekly range falls within the correct monthly date range. It does work to a fashion but quite often it puts the amounts in the wrong cell in table one...

I am going to include the helper tables below also....

[RANGE=cls:xl2bb-100][XR][XH=cs:7]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]BJ[/XH][XH]BK[/XH][XH]BL[/XH][XH]BM[/XH][XH]BN[/XH][XH]BO[/XH][/XR][XR][XH]1[/XH][XD=h:c|fw:b|bc:92d050]Helper Table (weekly Payment Table1)[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]2[/XH][XD=h:c|fw:b|bc:c2d69a]Year1[/XD][XD=h:c|fw:b|bc:c2d69a]Month1[/XD][XD=h:c|fw:b|bc:c2d69a]Amount1[/XD][XD=h:c|fw:b|bc:c2d69a]Year2[/XD][XD=h:c|fw:b|bc:c2d69a]Month2[/XD][XD=h:c|fw:b|bc:c2d69a]Amount2[/XD][/XR][XR][XH]3[/XH][XD=h:c]2011[/XD][XD=h:c]6[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][/XR][XR][XH]4[/XH][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][/XR][XR][XH]5[/XH][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][/XR][XR][XH]6[/XH][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][/XR][XR][XH]7[/XH][XD=h:c]2011[/XD][XD=h:c]7[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][/XR][XR][XH]8[/XH][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][/XR][XR][XH]9[/XH][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][/XR][XR][XH]10[/XH][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][/XR][XR][XH]11[/XH][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]8[/XD][XD=h:r] -[/XD][/XR][XR][XH]12[/XH][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][/XR][XR][XH]13[/XH][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][/XR][XR][XH]14[/XH][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][/XR][XR][XH]15[/XH][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][/XR][XR][XH]16[/XH][XD=h:c]2011[/XD][XD=h:c]9[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][/XR][XR][XH]17[/XH][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][/XR][XR][XH]18[/XH][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][/XR][XR][XH]19[/XH][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][/XR][XR][XH]20[/XH][XD=h:c]2011[/XD][XD=h:c]10[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][/XR][XR][XH]21[/XH][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][/XR][XR][XH]22[/XH][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][/XR][XR][XH]23[/XH][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][/XR][XR][XH]24[/XH][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]11[/XD][XD=h:r] -[/XD][/XR][XR][XH]25[/XH][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][/XR][XR][XH]26[/XH][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][/XR][XR][XH]27[/XH][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][/XR][XR][XH]28[/XH][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][/XR][XR][XH]29[/XH][XD=h:c]2011[/XD][XD=h:c]12[/XD][XD=h:r] -[/XD][XD=h:c]2012[/XD][XD=h:c]1[/XD][XD=h:r] -[/XD][/XR][XR][XH=cs:7][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]BJ3[/XD][XD]=YEAR(P4)[/XD][/XR][XR][XD]BJ4[/XD][XD]=YEAR(P5)[/XD][/XR][XR][XD]BJ5[/XD][XD]=YEAR(P6)[/XD][/XR][XR][XD]BJ6[/XD][XD]=YEAR(P7)[/XD][/XR][XR][XD]BJ7[/XD][XD]=YEAR(P8)[/XD][/XR][XR][XD]BJ8[/XD][XD]=YEAR(P9)[/XD][/XR][XR][XD]BJ9[/XD][XD]=YEAR(P10)[/XD][/XR][XR][XD]BJ10[/XD][XD]=YEAR(P11)[/XD][/XR][XR][XD]BJ11[/XD][XD]=YEAR(P12)[/XD][/XR][XR][XD]BJ12[/XD][XD]=YEAR(P13)[/XD][/XR][XR][XD]BJ13[/XD][XD]=YEAR(P14)[/XD][/XR][XR][XD]BJ14[/XD][XD]=YEAR(P15)[/XD][/XR][XR][XD]BJ15[/XD][XD]=YEAR(P16)[/XD][/XR][XR][XD]BJ16[/XD][XD]=YEAR(P17)[/XD][/XR][XR][XD]BJ17[/XD][XD]=YEAR(P18)[/XD][/XR][XR][XD]BJ18[/XD][XD]=YEAR(P19)[/XD][/XR][XR][XD]BJ19[/XD][XD]=YEAR(P20)[/XD][/XR][XR][XD]BJ20[/XD][XD]=YEAR(P21)[/XD][/XR][XR][XD]BJ21[/XD][XD]=YEAR(P22)[/XD][/XR][XR][XD]BJ22[/XD][XD]=YEAR(P23)[/XD][/XR][XR][XD]BJ23[/XD][XD]=YEAR(P24)[/XD][/XR][XR][XD]BJ24[/XD][XD]=YEAR(P25)[/XD][/XR][XR][XD]BJ25[/XD][XD]=YEAR(P26)[/XD][/XR][XR][XD]BJ26[/XD][XD]=YEAR(P27)[/XD][/XR][XR][XD]BJ27[/XD][XD]=YEAR(P28)[/XD][/XR][XR][XD]BJ28[/XD][XD]=YEAR(P29)[/XD][/XR][XR][XD]BJ29[/XD][XD]=YEAR(P30)[/XD][/XR][XR][XD]BK3[/XD][XD]=MONTH(P4)[/XD][/XR][XR][XD]BK4[/XD][XD]=MONTH(P5)[/XD][/XR][XR][XD]BK5[/XD][XD]=MONTH(P6)[/XD][/XR][XR][XD]BK6[/XD][XD]=MONTH(P7)[/XD][/XR][XR][XD]BK7[/XD][XD]=MONTH(P8)[/XD][/XR][XR][XD]BK8[/XD][XD]=MONTH(P9)[/XD][/XR][XR][XD]BK9[/XD][XD]=MONTH(P10)[/XD][/XR][XR][XD]BK10[/XD][XD]=MONTH(P11)[/XD][/XR][XR][XD]BK11[/XD][XD]=MONTH(P12)[/XD][/XR][XR][XD]BK12[/XD][XD]=MONTH(P13)[/XD][/XR][XR][XD]BK13[/XD][XD]=MONTH(P14)[/XD][/XR][XR][XD]BK14[/XD][XD]=MONTH(P15)[/XD][/XR][XR][XD]BK15[/XD][XD]=MONTH(P16)[/XD][/XR][XR][XD]BK16[/XD][XD]=MONTH(P17)[/XD][/XR][XR][XD]BK17[/XD][XD]=MONTH(P18)[/XD][/XR][XR][XD]BK18[/XD][XD]=MONTH(P19)[/XD][/XR][XR][XD]BK19[/XD][XD]=MONTH(P20)[/XD][/XR][XR][XD]BK20[/XD][XD]=MONTH(P21)[/XD][/XR][XR][XD]BK21[/XD][XD]=MONTH(P22)[/XD][/XR][XR][XD]BK22[/XD][XD]=MONTH(P23)[/XD][/XR][XR][XD]BK23[/XD][XD]=MONTH(P24)[/XD][/XR][XR][XD]BK24[/XD][XD]=MONTH(P25)[/XD][/XR][XR][XD]BK25[/XD][XD]=MONTH(P26)[/XD][/XR][XR][XD]BK26[/XD][XD]=MONTH(P27)[/XD][/XR][XR][XD]BK27[/XD][XD]=MONTH(P28)[/XD][/XR][XR][XD]BK28[/XD][XD]=MONTH(P29)[/XD][/XR][XR][XD]BK29[/XD][XD]=MONTH(P30)[/XD][/XR][XR][XD]BL3[/XD][XD]=IF(((EOMONTH(P4,0)-P4)+1)<8,$T4*((EOMONTH(P4,0)-P4+1)/7),T4)[/XD][/XR][XR][XD]BL4[/XD][XD]=IF(((EOMONTH(P5,0)-P5)+1)<8,$T5*((EOMONTH(P5,0)-P5+1)/7),T5)[/XD][/XR][XR][XD]BL5[/XD][XD]=IF(((EOMONTH(P6,0)-P6)+1)<8,$T6*((EOMONTH(P6,0)-P6+1)/7),T6)[/XD][/XR][XR][XD]BL6[/XD][XD]=IF(((EOMONTH(P7,0)-P7)+1)<8,$T7*((EOMONTH(P7,0)-P7+1)/7),T7)[/XD][/XR][XR][XD]BL7[/XD][XD]=IF(((EOMONTH(P8,0)-P8)+1)<8,$T8*((EOMONTH(P8,0)-P8+1)/7),T8)[/XD][/XR][XR][XD]BL8[/XD][XD]=IF(((EOMONTH(P9,0)-P9)+1)<8,$T9*((EOMONTH(P9,0)-P9+1)/7),T9)[/XD][/XR][XR][XD]BL9[/XD][XD]=IF(((EOMONTH(P10,0)-P10)+1)<8,$T10*((EOMONTH(P10,0)-P10+1)/7),T10)[/XD][/XR][XR][XD]BL10[/XD][XD]=IF(((EOMONTH(P11,0)-P11)+1)<8,$T11*((EOMONTH(P11,0)-P11+1)/7),T11)[/XD][/XR][XR][XD]BL11[/XD][XD]=IF(((EOMONTH(P12,0)-P12)+1)<8,$T12*((EOMONTH(P12,0)-P12+1)/7),T12)[/XD][/XR][XR][XD]BL12[/XD][XD]=IF(((EOMONTH(P13,0)-P13)+1)<8,$T13*((EOMONTH(P13,0)-P13+1)/7),T13)[/XD][/XR][XR][XD]BL13[/XD][XD]=IF(((EOMONTH(P14,0)-P14)+1)<8,$T14*((EOMONTH(P14,0)-P14+1)/7),T14)[/XD][/XR][XR][XD]BL14[/XD][XD]=IF(((EOMONTH(P15,0)-P15)+1)<8,$T15*((EOMONTH(P15,0)-P15+1)/7),T15)[/XD][/XR][XR][XD]BL15[/XD][XD]=IF(((EOMONTH(P16,0)-P16)+1)<8,$T16*((EOMONTH(P16,0)-P16+1)/7),T16)[/XD][/XR][XR][XD]BL16[/XD][XD]=IF(((EOMONTH(P17,0)-P17)+1)<8,$T17*((EOMONTH(P17,0)-P17+1)/7),T17)[/XD][/XR][XR][XD]BL17[/XD][XD]=IF(((EOMONTH(P18,0)-P18)+1)<8,$T18*((EOMONTH(P18,0)-P18+1)/7),T18)[/XD][/XR][XR][XD]BL18[/XD][XD]=IF(((EOMONTH(P19,0)-P19)+1)<8,$T19*((EOMONTH(P19,0)-P19+1)/7),T19)[/XD][/XR][XR][XD]BL19[/XD][XD]=IF(((EOMONTH(P20,0)-P20)+1)<8,$T20*((EOMONTH(P20,0)-P20+1)/7),T20)[/XD][/XR][XR][XD]BL20[/XD][XD]=IF(((EOMONTH(P21,0)-P21)+1)<8,$T21*((EOMONTH(P21,0)-P21+1)/7),T21)[/XD][/XR][XR][XD]BL21[/XD][XD]=IF(((EOMONTH(P22,0)-P22)+1)<8,$T22*((EOMONTH(P22,0)-P22+1)/7),T22)[/XD][/XR][XR][XD]BL22[/XD][XD]=IF(((EOMONTH(P23,0)-P23)+1)<8,$T23*((EOMONTH(P23,0)-P23+1)/7),T23)[/XD][/XR][XR][XD]BL23[/XD][XD]=IF(((EOMONTH(P24,0)-P24)+1)<8,$T24*((EOMONTH(P24,0)-P24+1)/7),T24)[/XD][/XR][XR][XD]BL24[/XD][XD]=IF(((EOMONTH(P25,0)-P25)+1)<8,$T25*((EOMONTH(P25,0)-P25+1)/7),T25)[/XD][/XR][XR][XD]BL25[/XD][XD]=IF(((EOMONTH(P26,0)-P26)+1)<8,$T26*((EOMONTH(P26,0)-P26+1)/7),T26)[/XD][/XR][XR][XD]BL26[/XD][XD]=IF(((EOMONTH(P27,0)-P27)+1)<8,$T27*((EOMONTH(P27,0)-P27+1)/7),T27)[/XD][/XR][XR][XD]BL27[/XD][XD]=IF(((EOMONTH(P28,0)-P28)+1)<8,$T28*((EOMONTH(P28,0)-P28+1)/7),T28)[/XD][/XR][XR][XD]BL28[/XD][XD]=IF(((EOMONTH(P29,0)-P29)+1)<8,$T29*((EOMONTH(P29,0)-P29+1)/7),T29)[/XD][/XR][XR][XD]BL29[/XD][XD]=IF(((EOMONTH(P30,0)-P30)+1)<8,$T30*((EOMONTH(P30,0)-P30+1)/7),T30)[/XD][/XR][XR][XD]BM3[/XD][XD]=YEAR(Q4)[/XD][/XR][XR][XD]BM4[/XD][XD]=YEAR(Q5)[/XD][/XR][XR][XD]BM5[/XD][XD]=YEAR(Q6)[/XD][/XR][XR][XD]BM6[/XD][XD]=YEAR(Q7)[/XD][/XR][XR][XD]BM7[/XD][XD]=YEAR(Q8)[/XD][/XR][XR][XD]BM8[/XD][XD]=YEAR(Q9)[/XD][/XR][XR][XD]BM9[/XD][XD]=YEAR(Q10)[/XD][/XR][XR][XD]BM10[/XD][XD]=YEAR(Q11)[/XD][/XR][XR][XD]BM11[/XD][XD]=YEAR(Q12)[/XD][/XR][XR][XD]BM12[/XD][XD]=YEAR(Q13)[/XD][/XR][XR][XD]BM13[/XD][XD]=YEAR(Q14)[/XD][/XR][XR][XD]BM14[/XD][XD]=YEAR(Q15)[/XD][/XR][XR][XD]BM15[/XD][XD]=YEAR(Q16)[/XD][/XR][XR][XD]BM16[/XD][XD]=YEAR(Q17)[/XD][/XR][XR][XD]BM17[/XD][XD]=YEAR(Q18)[/XD][/XR][XR][XD]BM18[/XD][XD]=YEAR(Q19)[/XD][/XR][XR][XD]BM19[/XD][XD]=YEAR(Q20)[/XD][/XR][XR][XD]BM20[/XD][XD]=YEAR(Q21)[/XD][/XR][XR][XD]BM21[/XD][XD]=YEAR(Q22)[/XD][/XR][XR][XD]BM22[/XD][XD]=YEAR(Q23)[/XD][/XR][XR][XD]BM23[/XD][XD]=YEAR(Q24)[/XD][/XR][XR][XD]BM24[/XD][XD]=YEAR(Q25)[/XD][/XR][XR][XD]BM25[/XD][XD]=YEAR(Q26)[/XD][/XR][XR][XD]BM26[/XD][XD]=YEAR(Q27)[/XD][/XR][XR][XD]BM27[/XD][XD]=YEAR(Q28)[/XD][/XR][XR][XD]BM28[/XD][XD]=YEAR(Q29)[/XD][/XR][XR][XD]BM29[/XD][XD]=YEAR(Q30)[/XD][/XR][XR][XD]BN3[/XD][XD]=MONTH(Q4)[/XD][/XR][XR][XD]BN4[/XD][XD]=MONTH(Q5)[/XD][/XR][XR][XD]BN5[/XD][XD]=MONTH(Q6)[/XD][/XR][XR][XD]BN6[/XD][XD]=MONTH(Q7)[/XD][/XR][XR][XD]BN7[/XD][XD]=MONTH(Q8)[/XD][/XR][XR][XD]BN8[/XD][XD]=MONTH(Q9)[/XD][/XR][XR][XD]BN9[/XD][XD]=MONTH(Q10)[/XD][/XR][XR][XD]BN10[/XD][XD]=MONTH(Q11)[/XD][/XR][XR][XD]BN11[/XD][XD]=MONTH(Q12)[/XD][/XR][XR][XD]BN12[/XD][XD]=MONTH(Q13)[/XD][/XR][XR][XD]BN13[/XD][XD]=MONTH(Q14)[/XD][/XR][XR][XD]BN14[/XD][XD]=MONTH(Q15)[/XD][/XR][XR][XD]BN15[/XD][XD]=MONTH(Q16)[/XD][/XR][XR][XD]BN16[/XD][XD]=MONTH(Q17)[/XD][/XR][XR][XD]BN17[/XD][XD]=MONTH(Q18)[/XD][/XR][XR][XD]BN18[/XD][XD]=MONTH(Q19)[/XD][/XR][XR][XD]BN19[/XD][XD]=MONTH(Q20)[/XD][/XR][XR][XD]BN20[/XD][XD]=MONTH(Q21)[/XD][/XR][XR][XD]BN21[/XD][XD]=MONTH(Q22)[/XD][/XR][XR][XD]BN22[/XD][XD]=MONTH(Q23)[/XD][/XR][XR][XD]BN23[/XD][XD]=MONTH(Q24)[/XD][/XR][XR][XD]BN24[/XD][XD]=MONTH(Q25)[/XD][/XR][XR][XD]BN25[/XD][XD]=MONTH(Q26)[/XD][/XR][XR][XD]BN26[/XD][XD]=MONTH(Q27)[/XD][/XR][XR][XD]BN27[/XD][XD]=MONTH(Q28)[/XD][/XR][XR][XD]BN28[/XD][XD]=MONTH(Q29)[/XD][/XR][XR][XD]BN29[/XD][XD]=MONTH(Q30)[/XD][/XR][XR][XD]BO3[/XD][XD]=$T4*(IF(MONTH(Q4)<>MONTH(P4),(DAY(Q4)/7),0))[/XD][/XR][XR][XD]BO4[/XD][XD]=$T5*(IF(MONTH(Q5)<>MONTH(P5),(DAY(Q5)/7),0))[/XD][/XR][XR][XD]BO5[/XD][XD]=$T6*(IF(MONTH(Q6)<>MONTH(P6),(DAY(Q6)/7),0))[/XD][/XR][XR][XD]BO6[/XD][XD]=$T7*(IF(MONTH(Q7)<>MONTH(P7),(DAY(Q7)/7),0))[/XD][/XR][XR][XD]BO7[/XD][XD]=$T8*(IF(MONTH(Q8)<>MONTH(P8),(DAY(Q8)/7),0))[/XD][/XR][XR][XD]BO8[/XD][XD]=$T9*(IF(MONTH(Q9)<>MONTH(P9),(DAY(Q9)/7),0))[/XD][/XR][XR][XD]BO9[/XD][XD]=$T10*(IF(MONTH(Q10)<>MONTH(P10),(DAY(Q10)/7),0))[/XD][/XR][XR][XD]BO10[/XD][XD]=$T11*(IF(MONTH(Q11)<>MONTH(P11),(DAY(Q11)/7),0))[/XD][/XR][XR][XD]BO11[/XD][XD]=$T12*(IF(MONTH(Q12)<>MONTH(P12),(DAY(Q12)/7),0))[/XD][/XR][XR][XD]BO12[/XD][XD]=$T13*(IF(MONTH(Q13)<>MONTH(P13),(DAY(Q13)/7),0))[/XD][/XR][XR][XD]BO13[/XD][XD]=$T14*(IF(MONTH(Q14)<>MONTH(P14),(DAY(Q14)/7),0))[/XD][/XR][XR][XD]BO14[/XD][XD]=$T15*(IF(MONTH(Q15)<>MONTH(P15),(DAY(Q15)/7),0))[/XD][/XR][XR][XD]BO15[/XD][XD]=$T16*(IF(MONTH(Q16)<>MONTH(P16),(DAY(Q16)/7),0))[/XD][/XR][XR][XD]BO16[/XD][XD]=$T17*(IF(MONTH(Q17)<>MONTH(P17),(DAY(Q17)/7),0))[/XD][/XR][XR][XD]BO17[/XD][XD]=$T18*(IF(MONTH(Q18)<>MONTH(P18),(DAY(Q18)/7),0))[/XD][/XR][XR][XD]BO18[/XD][XD]=$T19*(IF(MONTH(Q19)<>MONTH(P19),(DAY(Q19)/7),0))[/XD][/XR][XR][XD]BO19[/XD][XD]=$T20*(IF(MONTH(Q20)<>MONTH(P20),(DAY(Q20)/7),0))[/XD][/XR][XR][XD]BO20[/XD][XD]=$T21*(IF(MONTH(Q21)<>MONTH(P21),(DAY(Q21)/7),0))[/XD][/XR][XR][XD]BO21[/XD][XD]=$T22*(IF(MONTH(Q22)<>MONTH(P22),(DAY(Q22)/7),0))[/XD][/XR][XR][XD]BO22[/XD][XD]=$T23*(IF(MONTH(Q23)<>MONTH(P23),(DAY(Q23)/7),0))[/XD][/XR][XR][XD]BO23[/XD][XD]=$T24*(IF(MONTH(Q24)<>MONTH(P24),(DAY(Q24)/7),0))[/XD][/XR][XR][XD]BO24[/XD][XD]=$T25*(IF(MONTH(Q25)<>MONTH(P25),(DAY(Q25)/7),0))[/XD][/XR][XR][XD]BO25[/XD][XD]=$T26*(IF(MONTH(Q26)<>MONTH(P26),(DAY(Q26)/7),0))[/XD][/XR][XR][XD]BO26[/XD][XD]=$T27*(IF(MONTH(Q27)<>MONTH(P27),(DAY(Q27)/7),0))[/XD][/XR][XR][XD]BO27[/XD][XD]=$T28*(IF(MONTH(Q28)<>MONTH(P28),(DAY(Q28)/7),0))[/XD][/XR][XR][XD]BO28[/XD][XD]=$T29*(IF(MONTH(Q29)<>MONTH(P29),(DAY(Q29)/7),0))[/XD][/XR][XR][XD]BO29[/XD][XD]=$T30*(IF(MONTH(Q30)<>MONTH(P30),(DAY(Q30)/7),0))[/XD][/XR][/RANGE]

[RANGE=cls:xl2bb-100][XR][XH=cs:12]Excel Workbook[/XH][/XR][XR][XH][/XH][XH]BQ[/XH][XH]BR[/XH][XH]BS[/XH][XH]BT[/XH][XH]BU[/XH][XH]BV[/XH][XH]BW[/XH][XH]BX[/XH][XH]BY[/XH][XH]BZ[/XH][XH]CA[/XH][/XR][XR][XH]1[/XH][XD=h:c|fw:b|bc:92d050]This is table 2 for figuring out weekly amounts paid to PCM[/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][XD][/XD][/XR][XR][XH]2[/XH][XD=h:c|fw:b|bc:c2d69a]Daily Topup Amt[/XD][XD=h:c|fw:b|bc:c2d69a]Year1[/XD][XD=h:c|fw:b|bc:c2d69a]Month1[/XD][XD=h:c|fw:b|bc:c2d69a]Previous[/XD][XD=h:c|fw:b|bc:c2d69a]Previous month Payment[/XD][XD=h:c|fw:b|bc:c2d69a]Year2[/XD][XD=h:c|fw:b|bc:c2d69a]Month2[/XD][XD=h:c|fw:b|bc:c2d69a]Current Mnth[/XD][XD=h:c|fw:b|bc:c2d69a]Current month payment[/XD][XD=h:c|fw:b|bc:c2d69a]Johns Check Totals[/XD][XD=h:c|fw:b|bc:c2d69a]Total Pmt Rcvd[/XD][/XR][XR][XH]3[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]6[/XD][XD=h:c|fw:b]Jun[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]7[/XD][XD=h:c|fw:b]Jul[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]4[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]7[/XD][XD=h:c|fw:b]Jul[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]8[/XD][XD=h:c|fw:b]Aug[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]5[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]8[/XD][XD=h:c|fw:b]Aug[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]9[/XD][XD=h:c|fw:b]Sep[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]6[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]9[/XD][XD=h:c|fw:b]Sep[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]10[/XD][XD=h:c|fw:b]Oct[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]7[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]10[/XD][XD=h:c|fw:b]Oct[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]11[/XD][XD=h:c|fw:b]Nov[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]8[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]11[/XD][XD=h:c|fw:b]Nov[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]12[/XD][XD=h:c|fw:b]Dec[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]9[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2011[/XD][XD=h:c|fw:b]12[/XD][XD=h:c|fw:b]Dec[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]1[/XD][XD=h:c|fw:b]Jan[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]10[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]1[/XD][XD=h:c|fw:b]Jan[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]2[/XD][XD=h:c|fw:b]Feb[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]11[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]2[/XD][XD=h:c|fw:b]Feb[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]3[/XD][XD=h:c|fw:b]Mar[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]12[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]3[/XD][XD=h:c|fw:b]Mar[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]4[/XD][XD=h:c|fw:b]Apr[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]13[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]4[/XD][XD=h:c|fw:b]Apr[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]5[/XD][XD=h:c|fw:b]May[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH]14[/XH][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]5[/XD][XD=h:c|fw:b]May[/XD][XD=h:r] -[/XD][XD=h:c|fw:b]2012[/XD][XD=h:c|fw:b]6[/XD][XD=h:c|fw:b]Jun[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][XD=h:r] -[/XD][/XR][XR][XH=cs:12][RANGE][XR][XD]Sheet1[/XD][XH][/XH][/XR][/RANGE][/XH][/XR][/RANGE][B]Excel 2007[/B][RANGE=cls:xl2bb-extra-100][XR][XH=cs:2]Cell Formulas[/XH][/XR][XR][XD]Cell[/XD][XD]Formula[/XD][/XR][XR][XD]BQ3[/XD][XD]=BG3/7[/XD][/XR][XR][XD]BQ4[/XD][XD]=BG4/7[/XD][/XR][XR][XD]BQ5[/XD][XD]=BG5/7[/XD][/XR][XR][XD]BQ6[/XD][XD]=BG6/7[/XD][/XR][XR][XD]BQ7[/XD][XD]=BG7/7[/XD][/XR][XR][XD]BQ8[/XD][XD]=BG8/7[/XD][/XR][XR][XD]BQ9[/XD][XD]=BG9/7[/XD][/XR][XR][XD]BQ10[/XD][XD]=BG10/7[/XD][/XR][XR][XD]BQ11[/XD][XD]=BG11/7[/XD][/XR][XR][XD]BQ12[/XD][XD]=BG12/7[/XD][/XR][XR][XD]BQ13[/XD][XD]=BG13/7[/XD][/XR][XR][XD]BQ14[/XD][XD]=BG14/7[/XD][/XR][XR][XD]BR3[/XD][XD]=YEAR(P4)[/XD][/XR][XR][XD]BR4[/XD][XD]=BR3+IF(BS3=12,1,0)[/XD][/XR][XR][XD]BR5[/XD][XD]=BR4+IF(BS4=12,1,0)[/XD][/XR][XR][XD]BR6[/XD][XD]=BR5+IF(BS5=12,1,0)[/XD][/XR][XR][XD]BR7[/XD][XD]=BR6+IF(BS6=12,1,0)[/XD][/XR][XR][XD]BR8[/XD][XD]=BR7+IF(BS7=12,1,0)[/XD][/XR][XR][XD]BR9[/XD][XD]=BR8+IF(BS8=12,1,0)[/XD][/XR][XR][XD]BR10[/XD][XD]=BR9+IF(BS9=12,1,0)[/XD][/XR][XR][XD]BR11[/XD][XD]=BR10+IF(BS10=12,1,0)[/XD][/XR][XR][XD]BR12[/XD][XD]=BR11+IF(BS11=12,1,0)[/XD][/XR][XR][XD]BR13[/XD][XD]=BR12+IF(BS12=12,1,0)[/XD][/XR][XR][XD]BR14[/XD][XD]=BR13+IF(BS13=12,1,0)[/XD][/XR][XR][XD]BS3[/XD][XD]=MONTH(P4)[/XD][/XR][XR][XD]BS4[/XD][XD]=IF(BS3=12,1,BS3+1)[/XD][/XR][XR][XD]BS5[/XD][XD]=IF(BS4=12,1,BS4+1)[/XD][/XR][XR][XD]BS6[/XD][XD]=IF(BS5=12,1,BS5+1)[/XD][/XR][XR][XD]BS7[/XD][XD]=IF(BS6=12,1,BS6+1)[/XD][/XR][XR][XD]BS8[/XD][XD]=IF(BS7=12,1,BS7+1)[/XD][/XR][XR][XD]BS9[/XD][XD]=IF(BS8=12,1,BS8+1)[/XD][/XR][XR][XD]BS10[/XD][XD]=IF(BS9=12,1,BS9+1)[/XD][/XR][XR][XD]BS11[/XD][XD]=IF(BS10=12,1,BS10+1)[/XD][/XR][XR][XD]BS12[/XD][XD]=IF(BS11=12,1,BS11+1)[/XD][/XR][XR][XD]BS13[/XD][XD]=IF(BS12=12,1,BS12+1)[/XD][/XR][XR][XD]BS14[/XD][XD]=IF(BS13=12,1,BS13+1)[/XD][/XR][XR][XD]BT3[/XD][XD]=CHOOSE(BS3,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT4[/XD][XD]=CHOOSE(BS4,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT5[/XD][XD]=CHOOSE(BS5,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT6[/XD][XD]=CHOOSE(BS6,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT7[/XD][XD]=CHOOSE(BS7,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT8[/XD][XD]=CHOOSE(BS8,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT9[/XD][XD]=CHOOSE(BS9,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT10[/XD][XD]=CHOOSE(BS10,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT11[/XD][XD]=CHOOSE(BS11,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT12[/XD][XD]=CHOOSE(BS12,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT13[/XD][XD]=CHOOSE(BS13,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BT14[/XD][XD]=CHOOSE(BS14,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BU3[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR3),--($BK$3:$BK$55=BS3),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU4[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR4),--($BK$3:$BK$55=BS4),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU5[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR5),--($BK$3:$BK$55=BS5),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU6[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR6),--($BK$3:$BK$55=BS6),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU7[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR7),--($BK$3:$BK$55=BS7),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU8[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR8),--($BK$3:$BK$55=BS8),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU9[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR9),--($BK$3:$BK$55=BS9),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU10[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR10),--($BK$3:$BK$55=BS10),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU11[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR11),--($BK$3:$BK$55=BS11),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU12[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR12),--($BK$3:$BK$55=BS12),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU13[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR13),--($BK$3:$BK$55=BS13),$BL$3:$BL$55)[/XD][/XR][XR][XD]BU14[/XD][XD]=SUMPRODUCT(--($BJ$3:$BJ$55=BR14),--($BK$3:$BK$55=BS14),$BL$3:$BL$55)[/XD][/XR][XR][XD]BV3[/XD][XD]=BR3+IF(BW3=1,1,0)[/XD][/XR][XR][XD]BV4[/XD][XD]=BV3+IF(BW3=12,1,0)[/XD][/XR][XR][XD]BV5[/XD][XD]=BV4+IF(BW4=12,1,0)[/XD][/XR][XR][XD]BV6[/XD][XD]=BV5+IF(BW5=12,1,0)[/XD][/XR][XR][XD]BV7[/XD][XD]=BV6+IF(BW6=12,1,0)[/XD][/XR][XR][XD]BV8[/XD][XD]=BV7+IF(BW7=12,1,0)[/XD][/XR][XR][XD]BV9[/XD][XD]=BV8+IF(BW8=12,1,0)[/XD][/XR][XR][XD]BV10[/XD][XD]=BV9+IF(BW9=12,1,0)[/XD][/XR][XR][XD]BV11[/XD][XD]=BV10+IF(BW10=12,1,0)[/XD][/XR][XR][XD]BV12[/XD][XD]=BV11+IF(BW11=12,1,0)[/XD][/XR][XR][XD]BV13[/XD][XD]=BV12+IF(BW12=12,1,0)[/XD][/XR][XR][XD]BV14[/XD][XD]=BV13+IF(BW13=12,1,0)[/XD][/XR][XR][XD]BW3[/XD][XD]=BS3+1[/XD][/XR][XR][XD]BW4[/XD][XD]=IF(BW3=12,1,BW3+1)[/XD][/XR][XR][XD]BW5[/XD][XD]=IF(BW4=12,1,BW4+1)[/XD][/XR][XR][XD]BW6[/XD][XD]=IF(BW5=12,1,BW5+1)[/XD][/XR][XR][XD]BW7[/XD][XD]=IF(BW6=12,1,BW6+1)[/XD][/XR][XR][XD]BW8[/XD][XD]=IF(BW7=12,1,BW7+1)[/XD][/XR][XR][XD]BW9[/XD][XD]=IF(BW8=12,1,BW8+1)[/XD][/XR][XR][XD]BW10[/XD][XD]=IF(BW9=12,1,BW9+1)[/XD][/XR][XR][XD]BW11[/XD][XD]=IF(BW10=12,1,BW10+1)[/XD][/XR][XR][XD]BW12[/XD][XD]=IF(BW11=12,1,BW11+1)[/XD][/XR][XR][XD]BW13[/XD][XD]=IF(BW12=12,1,BW12+1)[/XD][/XR][XR][XD]BW14[/XD][XD]=IF(BW13=12,1,BW13+1)[/XD][/XR][XR][XD]BX3[/XD][XD]=CHOOSE(BW3,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX4[/XD][XD]=CHOOSE(BW4,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX5[/XD][XD]=CHOOSE(BW5,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX6[/XD][XD]=CHOOSE(BW6,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX7[/XD][XD]=CHOOSE(BW7,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX8[/XD][XD]=CHOOSE(BW8,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX9[/XD][XD]=CHOOSE(BW9,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX10[/XD][XD]=CHOOSE(BW10,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX11[/XD][XD]=CHOOSE(BW11,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX12[/XD][XD]=CHOOSE(BW12,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX13[/XD][XD]=CHOOSE(BW13,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BX14[/XD][XD]=CHOOSE(BW14,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")[/XD][/XR][XR][XD]BY3[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV3),--($BN$3:$BN$55=BW3),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY4[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV4),--($BN$3:$BN$55=BW4),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY5[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV5),--($BN$3:$BN$55=BW5),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY6[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV6),--($BN$3:$BN$55=BW6),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY7[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV7),--($BN$3:$BN$55=BW7),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY8[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV8),--($BN$3:$BN$55=BW8),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY9[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV9),--($BN$3:$BN$55=BW9),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY10[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV10),--($BN$3:$BN$55=BW10),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY11[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV11),--($BN$3:$BN$55=BW11),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY12[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV12),--($BN$3:$BN$55=BW12),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY13[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV13),--($BN$3:$BN$55=BW13),$BO$3:$BO$55)[/XD][/XR][XR][XD]BY14[/XD][XD]=SUMPRODUCT(--($BM$3:$BM$55=BV14),--($BN$3:$BN$55=BW14),$BO$3:$BO$55)[/XD][/XR][XR][XD]BZ3[/XD][XD]=BU3+BY3[/XD][/XR][XR][XD]BZ4[/XD][XD]=BU4+BY4[/XD][/XR][XR][XD]BZ5[/XD][XD]=BU5+BY5[/XD][/XR][XR][XD]BZ6[/XD][XD]=BU6+BY6[/XD][/XR][XR][XD]BZ7[/XD][XD]=BU7+BY7[/XD][/XR][XR][XD]BZ8[/XD][XD]=BU8+BY8[/XD][/XR][XR][XD]BZ9[/XD][XD]=BU9+BY9[/XD][/XR][XR][XD]BZ10[/XD][XD]=BU10+BY10[/XD][/XR][XR][XD]BZ11[/XD][XD]=BU11+BY11[/XD][/XR][XR][XD]BZ12[/XD][XD]=BU12+BY12[/XD][/XR][XR][XD]BZ13[/XD][XD]=BU13+BY13[/XD][/XR][XR][XD]BZ14[/XD][XD]=BU14+BY14[/XD][/XR][XR][XD]CA3[/XD][XD]=BU3[/XD][/XR][XR][XD]CA4[/XD][XD]=BY3+BU4[/XD][/XR][XR][XD]CA5[/XD][XD]=BY4+BU5[/XD][/XR][XR][XD]CA6[/XD][XD]=BY5+BU6[/XD][/XR][XR][XD]CA7[/XD][XD]=BY6+BU7[/XD][/XR][XR][XD]CA8[/XD][XD]=BY7+BU8[/XD][/XR][XR][XD]CA9[/XD][XD]=BY8+BU9[/XD][/XR][XR][XD]CA10[/XD][XD]=BY9+BU10[/XD][/XR][XR][XD]CA11[/XD][XD]=BY10+BU11[/XD][/XR][XR][XD]CA12[/XD][XD]=BY11+BU12[/XD][/XR][XR][XD]CA13[/XD][XD]=BY12+BU13[/XD][/XR][XR][XD]CA14[/XD][XD]=BY13+BU14[/XD][/XR][/RANGE]

I hope this makes more sense for you...

 

[AlphaFrog]

When posting HTML code that you want the forum editor to process, don't surround the code with HTML tags. Just paste the code in the editor and let it do what it needs to do.

Surrounding the code with HTML code tags prevents the editor from processing the HTML code. You would only surround the code with HTML tags to show the actual literal code in the thread.

 

[computaplanet]

Aplogies aplafrog following your instructions this is the weekly table:

Excel Workbook
	P	Q	R	S	T	U	V	W	X
2	Weekly Payment Table
3	Start Date	End Date	Covering Month(s)	Amount Due	Amount Paid	Date Paid	Bank	BACS / Chq / Cash / SO	Reference (if applicable)
4	30/06/2011	06/07/2011	Jun - Jul	100.00	-
5	07/07/2011	13/07/2011	Jul - Jul	100.00	-
6	14/07/2011	20/07/2011	Jul - Jul	100.00	-
7	21/07/2011	27/07/2011	Jul - Jul	100.00	-
8	28/07/2011	03/08/2011	Jul - Aug	100.00	-
9	04/08/2011	10/08/2011	Aug - Aug	100.00	-
10	11/08/2011	17/08/2011	Aug - Aug	100.00	-
11	18/08/2011	24/08/2011	Aug - Aug	100.00	-
12	25/08/2011	31/08/2011	Aug - Aug	100.00	-
13	01/09/2011	07/09/2011	Sep - Sep	100.00	-
14	08/09/2011	14/09/2011	Sep - Sep	100.00	-
15	15/09/2011	21/09/2011	Sep - Sep	100.00	-
16	22/09/2011	28/09/2011	Sep - Sep	100.00	-
17	29/09/2011	05/10/2011	Sep - Oct	100.00	-
18	06/10/2011	12/10/2011	Oct - Oct	100.00	-
19	13/10/2011	19/10/2011	Oct - Oct	100.00	-
20	20/10/2011	26/10/2011	Oct - Oct	100.00	-
21	27/10/2011	02/11/2011	Oct - Nov	0	-
22	03/11/2011	09/11/2011	Nov - Nov	0	-
23	10/11/2011	16/11/2011	Nov - Nov	0	-
24	17/11/2011	23/11/2011	Nov - Nov	0	-
25	24/11/2011	30/11/2011	Nov - Nov	0	-
26	01/12/2011	07/12/2011	Dec - Dec	0	-
27	08/12/2011	14/12/2011	Dec - Dec	0	-
28	15/12/2011	21/12/2011	Dec - Dec	0	-
29	22/12/2011	28/12/2011	Dec - Dec	0	-
30	29/12/2011	04/01/2012	Dec - Jan	0	-
31	05/01/2012	11/01/2012	Jan - Jan	0	-
Sheet1

Excel 2007

Cell Formulas
Range	Formula
P4	=D17
P5	=Q4+1
P6	=Q5+1
P7	=Q6+1
P8	=Q7+1
P9	=Q8+1
P10	=Q9+1
P11	=Q10+1
P12	=Q11+1
P13	=Q12+1
P14	=Q13+1
P15	=Q14+1
P16	=Q15+1
P17	=Q16+1
P18	=Q17+1
P19	=Q18+1
P20	=Q19+1
P21	=Q20+1
P22	=Q21+1
P23	=Q22+1
P24	=Q23+1
P25	=Q24+1
P26	=Q25+1
P27	=Q26+1
P28	=Q27+1
P29	=Q28+1
P30	=Q29+1
P31	=Q30+1
Q4	=P4+6
Q5	=P5+6
Q6	=P6+6
Q7	=P7+6
Q8	=P8+6
Q9	=P9+6
Q10	=P10+6
Q11	=P11+6
Q12	=P12+6
Q13	=P13+6
Q14	=P14+6
Q15	=P15+6
Q16	=P16+6
Q17	=P17+6
Q18	=P18+6
Q19	=P19+6
Q20	=P20+6
Q21	=P21+6
Q22	=P22+6
Q23	=P23+6
Q24	=P24+6
Q25	=P25+6
Q26	=P26+6
Q27	=P27+6
Q28	=P28+6
Q29	=P29+6
Q30	=P30+6
Q31	=P31+6
R4	=TEXT(DATEVALUE("1/"&MONTH(P4)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q4)&"/2011"),"mmm")
R5	=TEXT(DATEVALUE("1/"&MONTH(P5)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q5)&"/2011"),"mmm")
R6	=TEXT(DATEVALUE("1/"&MONTH(P6)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q6)&"/2011"),"mmm")
R7	=TEXT(DATEVALUE("1/"&MONTH(P7)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q7)&"/2011"),"mmm")
R8	=TEXT(DATEVALUE("1/"&MONTH(P8)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q8)&"/2011"),"mmm")
R9	=TEXT(DATEVALUE("1/"&MONTH(P9)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q9)&"/2011"),"mmm")
R10	=TEXT(DATEVALUE("1/"&MONTH(P10)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q10)&"/2011"),"mmm")
R11	=TEXT(DATEVALUE("1/"&MONTH(P11)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q11)&"/2011"),"mmm")
R12	=TEXT(DATEVALUE("1/"&MONTH(P12)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q12)&"/2011"),"mmm")
R13	=TEXT(DATEVALUE("1/"&MONTH(P13)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q13)&"/2011"),"mmm")
R14	=TEXT(DATEVALUE("1/"&MONTH(P14)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q14)&"/2011"),"mmm")
R15	=TEXT(DATEVALUE("1/"&MONTH(P15)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q15)&"/2011"),"mmm")
R16	=TEXT(DATEVALUE("1/"&MONTH(P16)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q16)&"/2011"),"mmm")
R17	=TEXT(DATEVALUE("1/"&MONTH(P17)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q17)&"/2011"),"mmm")
R18	=TEXT(DATEVALUE("1/"&MONTH(P18)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q18)&"/2011"),"mmm")
R19	=TEXT(DATEVALUE("1/"&MONTH(P19)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q19)&"/2011"),"mmm")
R20	=TEXT(DATEVALUE("1/"&MONTH(P20)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q20)&"/2011"),"mmm")
R21	=TEXT(DATEVALUE("1/"&MONTH(P21)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q21)&"/2011"),"mmm")
R22	=TEXT(DATEVALUE("1/"&MONTH(P22)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q22)&"/2011"),"mmm")
R23	=TEXT(DATEVALUE("1/"&MONTH(P23)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q23)&"/2011"),"mmm")
R24	=TEXT(DATEVALUE("1/"&MONTH(P24)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q24)&"/2011"),"mmm")
R25	=TEXT(DATEVALUE("1/"&MONTH(P25)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q25)&"/2011"),"mmm")
R26	=TEXT(DATEVALUE("1/"&MONTH(P26)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q26)&"/2011"),"mmm")
R27	=TEXT(DATEVALUE("1/"&MONTH(P27)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q27)&"/2011"),"mmm")
R28	=TEXT(DATEVALUE("1/"&MONTH(P28)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q28)&"/2011"),"mmm")
R29	=TEXT(DATEVALUE("1/"&MONTH(P29)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q29)&"/2011"),"mmm")
R30	=TEXT(DATEVALUE("1/"&MONTH(P30)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q30)&"/2011"),"mmm")
R31	=TEXT(DATEVALUE("1/"&MONTH(P31)&"/2011"),"mmm")&" - "&TEXT(DATEVALUE("1/"&MONTH(Q31)&"/2011"),"mmm")
S4	=IF(AND( $AZ$1 >= P4, $D$13<= P4), $H$13,"0")
S5	=IF(AND( $AZ$1 >= P5, $D$13<= P5), $H$13,"0")
S6	=IF(AND( $AZ$1 >= P6, $D$13<= P6), $H$13,"0")
S7	=IF(AND( $AZ$1 >= P7, $D$13<= P7), $H$13,"0")
S8	=IF(AND( $AZ$1 >= P8, $D$13<= P8), $H$13,"0")
S9	=IF(AND( $AZ$1 >= P9, $D$13<= P9), $H$13,"0")
S10	=IF(AND( $AZ$1 >= P10, $D$13<= P10), $H$13,"0")
S11	=IF(AND( $AZ$1 >= P11, $D$13<= P11), $H$13,"0")
S12	=IF(AND( $AZ$1 >= P12, $D$13<= P12), $H$13,"0")
S13	=IF(AND( $AZ$1 >= P13, $D$13<= P13), $H$13,"0")
S14	=IF(AND( $AZ$1 >= P14, $D$13<= P14), $H$13,"0")
S15	=IF(AND( $AZ$1 >= P15, $D$13<= P15), $H$13,"0")
S16	=IF(AND( $AZ$1 >= P16, $D$13<= P16), $H$13,"0")
S17	=IF(AND( $AZ$1 >= P17, $D$13<= P17), $H$13,"0")
S18	=IF(AND( $AZ$1 >= P18, $D$13<= P18), $H$13,"0")
S19	=IF(AND( $AZ$1 >= P19, $D$13<= P19), $H$13,"0")
S20	=IF(AND( $AZ$1 >= P20, $D$13<= P20), $H$13,"0")
S21	=IF(AND( $AZ$1 >= P21, $D$13<= P21), $H$13,"0")
S22	=IF(AND( $AZ$1 >= P22, $D$13<= P22), $H$13,"0")
S23	=IF(AND( $AZ$1 >= P23, $D$13<= P23), $H$13,"0")
S24	=IF(AND( $AZ$1 >= P24, $D$13<= P24), $H$13,"0")
S25	=IF(AND( $AZ$1 >= P25, $D$13<= P25), $H$13,"0")
S26	=IF(AND( $AZ$1 >= P26, $D$13<= P26), $H$13,"0")
S27	=IF(AND( $AZ$1 >= P27, $D$13<= P27), $H$13,"0")
S28	=IF(AND( $AZ$1 >= P28, $D$13<= P28), $H$13,"0")
S29	=IF(AND( $AZ$1 >= P29, $D$13<= P29), $H$13,"0")
S30	=IF(AND( $AZ$1 >= P30, $D$13<= P30), $H$13,"0")
S31	=IF(AND( $AZ$1 >= P31, $D$13<= P31), $H$13,"0")

This is my original table one:

Excel Workbook
	A	B	C	D	E	F	G
19	PCM START DATE (DUE)	PCM END DATE	Rent Due (PCM)	HB Payment	Top up	Actually Paid by Self	Total Payments made to account
20	30/06/2011	29/07/2011	433.33	407.14	26.19	22.00	22.00
21	30/07/2011	29/08/2011	433.33	420.71	12.62	22.00	22.00
22	30/08/2011	29/09/2011	433.33	420.71	12.62	22.00	22.00
23	30/09/2011	29/10/2011	433.33	40.71	392.62	-	-
24	30/10/2011	29/11/2011	0	-	-	-	-
25	30/11/2011	29/12/2011	0	-	-	-	-
26	30/12/2011	29/01/2012	0	-	-	-	-
27	30/01/2012	29/02/2012	0	-	-	-	-
28	01/03/2012	31/03/2012	0	-	-	-	-
29	01/04/2012	30/04/2012	0	-	-	-	-
30	01/05/2012	31/05/2012	0	-	-	-	-
31	01/06/2012	30/06/2012	0	-	-	-	-
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A20	=D17
A21	=DATE(YEAR(A20),MONTH(A20)+1,DAY(A20))
A22	=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21))
A23	=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22))
A24	=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23))
A25	=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24))
A26	=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25))
A27	=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26))
A28	=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27))
A29	=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28))
A30	=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29))
A31	=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30))
B20	=DATE(YEAR(A20),MONTH(A20)+1,DAY(A20)-1)
B21	=DATE(YEAR(A21),MONTH(A21)+1,DAY(A21)-1)
B22	=DATE(YEAR(A22),MONTH(A22)+1,DAY(A22)-1)
B23	=DATE(YEAR(A23),MONTH(A23)+1,DAY(A23)-1)
B24	=DATE(YEAR(A24),MONTH(A24)+1,DAY(A24)-1)
B25	=DATE(YEAR(A25),MONTH(A25)+1,DAY(A25)-1)
B26	=DATE(YEAR(A26),MONTH(A26)+1,DAY(A26)-1)
B27	=DATE(YEAR(A27),MONTH(A27)+1,DAY(A27)-1)
B28	=DATE(YEAR(A28),MONTH(A28)+1,DAY(A28)-1)
B29	=DATE(YEAR(A29),MONTH(A29)+1,DAY(A29)-1)
B30	=DATE(YEAR(A30),MONTH(A30)+1,DAY(A30)-1)
B31	=DATE(YEAR(A31),MONTH(A31)+1,DAY(A31)-1)
C20	=IF(AND( $AZ$1 >= A20, $D$17<= A20), $B$17,"0")
C21	=IF(AND( $AZ$1 >= A21, $D$17<= A21), $B$17,"0")
C22	=IF(AND( $AZ$1 >= A22, $D$17<= A22), $B$17,"0")
C23	=IF(AND( $AZ$1 >= A23, $D$17<= A23), $B$17,"0")
C24	=IF(AND( $AZ$1 >= A24, $D$17<= A24), $B$17,"0")
C25	=IF(AND( $AZ$1 >= A25, $D$17<= A25), $B$17,"0")
C26	=IF(AND( $AZ$1 >= A26, $D$17<= A26), $B$17,"0")
C27	=IF(AND( $AZ$1 >= A27, $D$17<= A27), $B$17,"0")
C28	=IF(AND( $AZ$1 >= A28, $D$17<= A28), $B$17,"0")
C29	=IF(AND( $AZ$1 >= A29, $D$17<= A29), $B$17,"0")
C30	=IF(AND( $AZ$1 >= A30, $D$17<= A30), $B$17,"0")
C31	=IF(AND( $AZ$1 >= A31, $D$17<= A31), $B$17,"0")
D20	=PCMTotal(A20,B20,$B$37:$C$49,$E$37:$E$49)
D21	=PCMTotal(A21,B21,$B$37:$C$49,$E$37:$E$49)
D22	=PCMTotal(A22,B22,$B$37:$C$49,$E$37:$E$49)
D23	=PCMTotal(A23,B23,$B$37:$C$49,$E$37:$E$49)
D24	=PCMTotal(A24,B24,$B$37:$C$49,$E$37:$E$49)
D25	=PCMTotal(A25,B25,$B$37:$C$49,$E$37:$E$49)
D26	=PCMTotal(A26,B26,$B$37:$C$49,$E$37:$E$49)
D27	=PCMTotal(A27,B27,$B$37:$C$49,$E$37:$E$49)
D28	=PCMTotal(A28,B28,$B$37:$C$49,$E$37:$E$49)
D29	=PCMTotal(A29,B29,$B$37:$C$49,$E$37:$E$49)
D30	=PCMTotal(A30,B30,$B$37:$C$49,$E$37:$E$49)
D31	=PCMTotal(A31,B31,$B$37:$C$49,$E$37:$E$49)
E20	=C20-BV20
E21	=C21-BV21
E22	=C22-BV22
E23	=C23-BV23
E24	=C24-BV24
E25	=C25-BV25
E26	=C26-BV26
E27	=C27-BV27
E28	=C28-BV28
E29	=C29-BV29
E30	=C30-BV30
E31	=C31-BV31
G20	=F20+BY20
G21	=F21+BY21
G22	=F22+BY22
G23	=F23+BY23
G24	=F24+BY24
G25	=F25+BY25
G26	=F26+BY26
G27	=F27+BY27
G28	=F28+BY28
G29	=F29+BY29
G30	=F30+BY30
G31	=F31+BY31

this is my helper table 1 to workout the weekly payment amounts:

Excel Workbook
	BJ	BK	BL	BM	BN	BO
1	Helper Table (weekly Payment Table1)
2	Year1	Month1	Amount1	Year2	Month2	Amount2
3	2011	6	-	2011	7	-
4	2011	7	-	2011	7	-
5	2011	7	-	2011	7	-
6	2011	7	-	2011	7	-
7	2011	7	-	2011	8	-
8	2011	8	-	2011	8	-
9	2011	8	-	2011	8	-
10	2011	8	-	2011	8	-
11	2011	8	-	2011	8	-
12	2011	9	-	2011	9	-
13	2011	9	-	2011	9	-
14	2011	9	-	2011	9	-
15	2011	9	-	2011	9	-
16	2011	9	-	2011	10	-
17	2011	10	-	2011	10	-
18	2011	10	-	2011	10	-
19	2011	10	-	2011	10	-
20	2011	10	-	2011	11	-
21	2011	11	-	2011	11	-
22	2011	11	-	2011	11	-
23	2011	11	-	2011	11	-
24	2011	11	-	2011	11	-
25	2011	12	-	2011	12	-
26	2011	12	-	2011	12	-
27	2011	12	-	2011	12	-
28	2011	12	-	2011	12	-
29	2011	12	-	2012	1	-
Sheet1

Excel 2007

Cell Formulas
Range	Formula
BJ3	=YEAR(P4)
BJ4	=YEAR(P5)
BJ5	=YEAR(P6)
BJ6	=YEAR(P7)
BJ7	=YEAR(P8)
BJ8	=YEAR(P9)
BJ9	=YEAR(P10)
BJ10	=YEAR(P11)
BJ11	=YEAR(P12)
BJ12	=YEAR(P13)
BJ13	=YEAR(P14)
BJ14	=YEAR(P15)
BJ15	=YEAR(P16)
BJ16	=YEAR(P17)
BJ17	=YEAR(P18)
BJ18	=YEAR(P19)
BJ19	=YEAR(P20)
BJ20	=YEAR(P21)
BJ21	=YEAR(P22)
BJ22	=YEAR(P23)
BJ23	=YEAR(P24)
BJ24	=YEAR(P25)
BJ25	=YEAR(P26)
BJ26	=YEAR(P27)
BJ27	=YEAR(P28)
BJ28	=YEAR(P29)
BJ29	=YEAR(P30)
BK3	=MONTH(P4)
BK4	=MONTH(P5)
BK5	=MONTH(P6)
BK6	=MONTH(P7)
BK7	=MONTH(P8)
BK8	=MONTH(P9)
BK9	=MONTH(P10)
BK10	=MONTH(P11)
BK11	=MONTH(P12)
BK12	=MONTH(P13)
BK13	=MONTH(P14)
BK14	=MONTH(P15)
BK15	=MONTH(P16)
BK16	=MONTH(P17)
BK17	=MONTH(P18)
BK18	=MONTH(P19)
BK19	=MONTH(P20)
BK20	=MONTH(P21)
BK21	=MONTH(P22)
BK22	=MONTH(P23)
BK23	=MONTH(P24)
BK24	=MONTH(P25)
BK25	=MONTH(P26)
BK26	=MONTH(P27)
BK27	=MONTH(P28)
BK28	=MONTH(P29)
BK29	=MONTH(P30)
BL3	=IF(((EOMONTH(P4,0)-P4)+1)<8,$T4*((EOMONTH(P4,0)-P4+1)/7),T4)
BL4	=IF(((EOMONTH(P5,0)-P5)+1)<8,$T5*((EOMONTH(P5,0)-P5+1)/7),T5)
BL5	=IF(((EOMONTH(P6,0)-P6)+1)<8,$T6*((EOMONTH(P6,0)-P6+1)/7),T6)
BL6	=IF(((EOMONTH(P7,0)-P7)+1)<8,$T7*((EOMONTH(P7,0)-P7+1)/7),T7)
BL7	=IF(((EOMONTH(P8,0)-P8)+1)<8,$T8*((EOMONTH(P8,0)-P8+1)/7),T8)
BL8	=IF(((EOMONTH(P9,0)-P9)+1)<8,$T9*((EOMONTH(P9,0)-P9+1)/7),T9)
BL9	=IF(((EOMONTH(P10,0)-P10)+1)<8,$T10*((EOMONTH(P10,0)-P10+1)/7),T10)
BL10	=IF(((EOMONTH(P11,0)-P11)+1)<8,$T11*((EOMONTH(P11,0)-P11+1)/7),T11)
BL11	=IF(((EOMONTH(P12,0)-P12)+1)<8,$T12*((EOMONTH(P12,0)-P12+1)/7),T12)
BL12	=IF(((EOMONTH(P13,0)-P13)+1)<8,$T13*((EOMONTH(P13,0)-P13+1)/7),T13)
BL13	=IF(((EOMONTH(P14,0)-P14)+1)<8,$T14*((EOMONTH(P14,0)-P14+1)/7),T14)
BL14	=IF(((EOMONTH(P15,0)-P15)+1)<8,$T15*((EOMONTH(P15,0)-P15+1)/7),T15)
BL15	=IF(((EOMONTH(P16,0)-P16)+1)<8,$T16*((EOMONTH(P16,0)-P16+1)/7),T16)
BL16	=IF(((EOMONTH(P17,0)-P17)+1)<8,$T17*((EOMONTH(P17,0)-P17+1)/7),T17)
BL17	=IF(((EOMONTH(P18,0)-P18)+1)<8,$T18*((EOMONTH(P18,0)-P18+1)/7),T18)
BL18	=IF(((EOMONTH(P19,0)-P19)+1)<8,$T19*((EOMONTH(P19,0)-P19+1)/7),T19)
BL19	=IF(((EOMONTH(P20,0)-P20)+1)<8,$T20*((EOMONTH(P20,0)-P20+1)/7),T20)
BL20	=IF(((EOMONTH(P21,0)-P21)+1)<8,$T21*((EOMONTH(P21,0)-P21+1)/7),T21)
BL21	=IF(((EOMONTH(P22,0)-P22)+1)<8,$T22*((EOMONTH(P22,0)-P22+1)/7),T22)
BL22	=IF(((EOMONTH(P23,0)-P23)+1)<8,$T23*((EOMONTH(P23,0)-P23+1)/7),T23)
BL23	=IF(((EOMONTH(P24,0)-P24)+1)<8,$T24*((EOMONTH(P24,0)-P24+1)/7),T24)
BL24	=IF(((EOMONTH(P25,0)-P25)+1)<8,$T25*((EOMONTH(P25,0)-P25+1)/7),T25)
BL25	=IF(((EOMONTH(P26,0)-P26)+1)<8,$T26*((EOMONTH(P26,0)-P26+1)/7),T26)
BL26	=IF(((EOMONTH(P27,0)-P27)+1)<8,$T27*((EOMONTH(P27,0)-P27+1)/7),T27)
BL27	=IF(((EOMONTH(P28,0)-P28)+1)<8,$T28*((EOMONTH(P28,0)-P28+1)/7),T28)
BL28	=IF(((EOMONTH(P29,0)-P29)+1)<8,$T29*((EOMONTH(P29,0)-P29+1)/7),T29)
BL29	=IF(((EOMONTH(P30,0)-P30)+1)<8,$T30*((EOMONTH(P30,0)-P30+1)/7),T30)
BM3	=YEAR(Q4)
BM4	=YEAR(Q5)
BM5	=YEAR(Q6)
BM6	=YEAR(Q7)
BM7	=YEAR(Q8)
BM8	=YEAR(Q9)
BM9	=YEAR(Q10)
BM10	=YEAR(Q11)
BM11	=YEAR(Q12)
BM12	=YEAR(Q13)
BM13	=YEAR(Q14)
BM14	=YEAR(Q15)
BM15	=YEAR(Q16)
BM16	=YEAR(Q17)
BM17	=YEAR(Q18)
BM18	=YEAR(Q19)
BM19	=YEAR(Q20)
BM20	=YEAR(Q21)
BM21	=YEAR(Q22)
BM22	=YEAR(Q23)
BM23	=YEAR(Q24)
BM24	=YEAR(Q25)
BM25	=YEAR(Q26)
BM26	=YEAR(Q27)
BM27	=YEAR(Q28)
BM28	=YEAR(Q29)
BM29	=YEAR(Q30)
BN3	=MONTH(Q4)
BN4	=MONTH(Q5)
BN5	=MONTH(Q6)
BN6	=MONTH(Q7)
BN7	=MONTH(Q8)
BN8	=MONTH(Q9)
BN9	=MONTH(Q10)
BN10	=MONTH(Q11)
BN11	=MONTH(Q12)
BN12	=MONTH(Q13)
BN13	=MONTH(Q14)
BN14	=MONTH(Q15)
BN15	=MONTH(Q16)
BN16	=MONTH(Q17)
BN17	=MONTH(Q18)
BN18	=MONTH(Q19)
BN19	=MONTH(Q20)
BN20	=MONTH(Q21)
BN21	=MONTH(Q22)
BN22	=MONTH(Q23)
BN23	=MONTH(Q24)
BN24	=MONTH(Q25)
BN25	=MONTH(Q26)
BN26	=MONTH(Q27)
BN27	=MONTH(Q28)
BN28	=MONTH(Q29)
BN29	=MONTH(Q30)
BO3	=$T4*(IF(MONTH(Q4)<>MONTH(P4),(DAY(Q4)/7),0))
BO4	=$T5*(IF(MONTH(Q5)<>MONTH(P5),(DAY(Q5)/7),0))
BO5	=$T6*(IF(MONTH(Q6)<>MONTH(P6),(DAY(Q6)/7),0))
BO6	=$T7*(IF(MONTH(Q7)<>MONTH(P7),(DAY(Q7)/7),0))
BO7	=$T8*(IF(MONTH(Q8)<>MONTH(P8),(DAY(Q8)/7),0))
BO8	=$T9*(IF(MONTH(Q9)<>MONTH(P9),(DAY(Q9)/7),0))
BO9	=$T10*(IF(MONTH(Q10)<>MONTH(P10),(DAY(Q10)/7),0))
BO10	=$T11*(IF(MONTH(Q11)<>MONTH(P11),(DAY(Q11)/7),0))
BO11	=$T12*(IF(MONTH(Q12)<>MONTH(P12),(DAY(Q12)/7),0))
BO12	=$T13*(IF(MONTH(Q13)<>MONTH(P13),(DAY(Q13)/7),0))
BO13	=$T14*(IF(MONTH(Q14)<>MONTH(P14),(DAY(Q14)/7),0))
BO14	=$T15*(IF(MONTH(Q15)<>MONTH(P15),(DAY(Q15)/7),0))
BO15	=$T16*(IF(MONTH(Q16)<>MONTH(P16),(DAY(Q16)/7),0))
BO16	=$T17*(IF(MONTH(Q17)<>MONTH(P17),(DAY(Q17)/7),0))
BO17	=$T18*(IF(MONTH(Q18)<>MONTH(P18),(DAY(Q18)/7),0))
BO18	=$T19*(IF(MONTH(Q19)<>MONTH(P19),(DAY(Q19)/7),0))
BO19	=$T20*(IF(MONTH(Q20)<>MONTH(P20),(DAY(Q20)/7),0))
BO20	=$T21*(IF(MONTH(Q21)<>MONTH(P21),(DAY(Q21)/7),0))
BO21	=$T22*(IF(MONTH(Q22)<>MONTH(P22),(DAY(Q22)/7),0))
BO22	=$T23*(IF(MONTH(Q23)<>MONTH(P23),(DAY(Q23)/7),0))
BO23	=$T24*(IF(MONTH(Q24)<>MONTH(P24),(DAY(Q24)/7),0))
BO24	=$T25*(IF(MONTH(Q25)<>MONTH(P25),(DAY(Q25)/7),0))
BO25	=$T26*(IF(MONTH(Q26)<>MONTH(P26),(DAY(Q26)/7),0))
BO26	=$T27*(IF(MONTH(Q27)<>MONTH(P27),(DAY(Q27)/7),0))
BO27	=$T28*(IF(MONTH(Q28)<>MONTH(P28),(DAY(Q28)/7),0))
BO28	=$T29*(IF(MONTH(Q29)<>MONTH(P29),(DAY(Q29)/7),0))
BO29	=$T30*(IF(MONTH(Q30)<>MONTH(P30),(DAY(Q30)/7),0))

Helper table 2:

Excel Workbook
	BQ	BR	BS	BT	BU	BV	BW	BX	BY	BZ	CA
1	This is table 2 for figuring out weekly amounts paid to PCM
2	Daily Topup Amt	Year1	Month1	Previous	Previous month Payment	Year2	Month2	Current Mnth	Current month payment	Johns Check Totals	Total Pmt Rcvd
3	-	2011	6	Jun	-	2011	7	Jul	-	-	-
4	-	2011	7	Jul	-	2011	8	Aug	-	-	-
5	-	2011	8	Aug	-	2011	9	Sep	-	-	-
6	-	2011	9	Sep	-	2011	10	Oct	-	-	-
7	-	2011	10	Oct	-	2011	11	Nov	-	-	-
8	-	2011	11	Nov	-	2011	12	Dec	-	-	-
9	-	2011	12	Dec	-	2012	1	Jan	-	-	-
10	-	2012	1	Jan	-	2012	2	Feb	-	-	-
11	-	2012	2	Feb	-	2012	3	Mar	-	-	-
12	-	2012	3	Mar	-	2012	4	Apr	-	-	-
13	-	2012	4	Apr	-	2012	5	May	-	-	-
14	-	2012	5	May	-	2012	6	Jun	-	-	-
Sheet1

Excel 2007

Cell Formulas
Range	Formula
BQ3	=BG3/7
BQ4	=BG4/7
BQ5	=BG5/7
BQ6	=BG6/7
BQ7	=BG7/7
BQ8	=BG8/7
BQ9	=BG9/7
BQ10	=BG10/7
BQ11	=BG11/7
BQ12	=BG12/7
BQ13	=BG13/7
BQ14	=BG14/7
BR3	=YEAR(P4)
BR4	=BR3+IF(BS3=12,1,0)
BR5	=BR4+IF(BS4=12,1,0)
BR6	=BR5+IF(BS5=12,1,0)
BR7	=BR6+IF(BS6=12,1,0)
BR8	=BR7+IF(BS7=12,1,0)
BR9	=BR8+IF(BS8=12,1,0)
BR10	=BR9+IF(BS9=12,1,0)
BR11	=BR10+IF(BS10=12,1,0)
BR12	=BR11+IF(BS11=12,1,0)
BR13	=BR12+IF(BS12=12,1,0)
BR14	=BR13+IF(BS13=12,1,0)
BS3	=MONTH(P4)
BS4	=IF(BS3=12,1,BS3+1)
BS5	=IF(BS4=12,1,BS4+1)
BS6	=IF(BS5=12,1,BS5+1)
BS7	=IF(BS6=12,1,BS6+1)
BS8	=IF(BS7=12,1,BS7+1)
BS9	=IF(BS8=12,1,BS8+1)
BS10	=IF(BS9=12,1,BS9+1)
BS11	=IF(BS10=12,1,BS10+1)
BS12	=IF(BS11=12,1,BS11+1)
BS13	=IF(BS12=12,1,BS12+1)
BS14	=IF(BS13=12,1,BS13+1)
BT3	=CHOOSE(BS3,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT4	=CHOOSE(BS4,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT5	=CHOOSE(BS5,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT6	=CHOOSE(BS6,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT7	=CHOOSE(BS7,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT8	=CHOOSE(BS8,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT9	=CHOOSE(BS9,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT10	=CHOOSE(BS10,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT11	=CHOOSE(BS11,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT12	=CHOOSE(BS12,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT13	=CHOOSE(BS13,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BT14	=CHOOSE(BS14,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BU3	=SUMPRODUCT(--($BJ$3:$BJ$55=BR3),--($BK$3:$BK$55=BS3),$BL$3:$BL$55)
BU4	=SUMPRODUCT(--($BJ$3:$BJ$55=BR4),--($BK$3:$BK$55=BS4),$BL$3:$BL$55)
BU5	=SUMPRODUCT(--($BJ$3:$BJ$55=BR5),--($BK$3:$BK$55=BS5),$BL$3:$BL$55)
BU6	=SUMPRODUCT(--($BJ$3:$BJ$55=BR6),--($BK$3:$BK$55=BS6),$BL$3:$BL$55)
BU7	=SUMPRODUCT(--($BJ$3:$BJ$55=BR7),--($BK$3:$BK$55=BS7),$BL$3:$BL$55)
BU8	=SUMPRODUCT(--($BJ$3:$BJ$55=BR8),--($BK$3:$BK$55=BS8),$BL$3:$BL$55)
BU9	=SUMPRODUCT(--($BJ$3:$BJ$55=BR9),--($BK$3:$BK$55=BS9),$BL$3:$BL$55)
BU10	=SUMPRODUCT(--($BJ$3:$BJ$55=BR10),--($BK$3:$BK$55=BS10),$BL$3:$BL$55)
BU11	=SUMPRODUCT(--($BJ$3:$BJ$55=BR11),--($BK$3:$BK$55=BS11),$BL$3:$BL$55)
BU12	=SUMPRODUCT(--($BJ$3:$BJ$55=BR12),--($BK$3:$BK$55=BS12),$BL$3:$BL$55)
BU13	=SUMPRODUCT(--($BJ$3:$BJ$55=BR13),--($BK$3:$BK$55=BS13),$BL$3:$BL$55)
BU14	=SUMPRODUCT(--($BJ$3:$BJ$55=BR14),--($BK$3:$BK$55=BS14),$BL$3:$BL$55)
BV3	=BR3+IF(BW3=1,1,0)
BV4	=BV3+IF(BW3=12,1,0)
BV5	=BV4+IF(BW4=12,1,0)
BV6	=BV5+IF(BW5=12,1,0)
BV7	=BV6+IF(BW6=12,1,0)
BV8	=BV7+IF(BW7=12,1,0)
BV9	=BV8+IF(BW8=12,1,0)
BV10	=BV9+IF(BW9=12,1,0)
BV11	=BV10+IF(BW10=12,1,0)
BV12	=BV11+IF(BW11=12,1,0)
BV13	=BV12+IF(BW12=12,1,0)
BV14	=BV13+IF(BW13=12,1,0)
BW3	=BS3+1
BW4	=IF(BW3=12,1,BW3+1)
BW5	=IF(BW4=12,1,BW4+1)
BW6	=IF(BW5=12,1,BW5+1)
BW7	=IF(BW6=12,1,BW6+1)
BW8	=IF(BW7=12,1,BW7+1)
BW9	=IF(BW8=12,1,BW8+1)
BW10	=IF(BW9=12,1,BW9+1)
BW11	=IF(BW10=12,1,BW10+1)
BW12	=IF(BW11=12,1,BW11+1)
BW13	=IF(BW12=12,1,BW12+1)
BW14	=IF(BW13=12,1,BW13+1)
BX3	=CHOOSE(BW3,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX4	=CHOOSE(BW4,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX5	=CHOOSE(BW5,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX6	=CHOOSE(BW6,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX7	=CHOOSE(BW7,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX8	=CHOOSE(BW8,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX9	=CHOOSE(BW9,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX10	=CHOOSE(BW10,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX11	=CHOOSE(BW11,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX12	=CHOOSE(BW12,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX13	=CHOOSE(BW13,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BX14	=CHOOSE(BW14,"Jan","Feb","Mar","Apr","May", "Jun","Jul","Aug","Sep","Oct","Nov","Dec")
BY3	=SUMPRODUCT(--($BM$3:$BM$55=BV3),--($BN$3:$BN$55=BW3),$BO$3:$BO$55)
BY4	=SUMPRODUCT(--($BM$3:$BM$55=BV4),--($BN$3:$BN$55=BW4),$BO$3:$BO$55)
BY5	=SUMPRODUCT(--($BM$3:$BM$55=BV5),--($BN$3:$BN$55=BW5),$BO$3:$BO$55)
BY6	=SUMPRODUCT(--($BM$3:$BM$55=BV6),--($BN$3:$BN$55=BW6),$BO$3:$BO$55)
BY7	=SUMPRODUCT(--($BM$3:$BM$55=BV7),--($BN$3:$BN$55=BW7),$BO$3:$BO$55)
BY8	=SUMPRODUCT(--($BM$3:$BM$55=BV8),--($BN$3:$BN$55=BW8),$BO$3:$BO$55)
BY9	=SUMPRODUCT(--($BM$3:$BM$55=BV9),--($BN$3:$BN$55=BW9),$BO$3:$BO$55)
BY10	=SUMPRODUCT(--($BM$3:$BM$55=BV10),--($BN$3:$BN$55=BW10),$BO$3:$BO$55)
BY11	=SUMPRODUCT(--($BM$3:$BM$55=BV11),--($BN$3:$BN$55=BW11),$BO$3:$BO$55)
BY12	=SUMPRODUCT(--($BM$3:$BM$55=BV12),--($BN$3:$BN$55=BW12),$BO$3:$BO$55)
BY13	=SUMPRODUCT(--($BM$3:$BM$55=BV13),--($BN$3:$BN$55=BW13),$BO$3:$BO$55)
BY14	=SUMPRODUCT(--($BM$3:$BM$55=BV14),--($BN$3:$BN$55=BW14),$BO$3:$BO$55)
BZ3	=BU3+BY3
BZ4	=BU4+BY4
BZ5	=BU5+BY5
BZ6	=BU6+BY6
BZ7	=BU7+BY7
BZ8	=BU8+BY8
BZ9	=BU9+BY9
BZ10	=BU10+BY10
BZ11	=BU11+BY11
BZ12	=BU12+BY12
BZ13	=BU13+BY13
BZ14	=BU14+BY14
CA3	=BU3
CA4	=BY3+BU4
CA5	=BY4+BU5
CA6	=BY5+BU6
CA7	=BY6+BU7
CA8	=BY7+BU8
CA9	=BY8+BU9
CA10	=BY9+BU10
CA11	=BY10+BU11
CA12	=BY11+BU12
CA13	=BY12+BU13
CA14	=BY13+BU14