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]

I should probably say that I am sure your code when adjusted will enable me to totally leave out the helper tables, all i am trying to do is make sure that when a payment is input into a cell in column T4 : T56 it is then added to the correct cell in table 1 column G20 :31 depending on the dates.

If a payment is made in the weekly table and the dates in column P and Q cross a date range in column A and B in table one the amount is split according to the number of days covered in each range (similar to the original problem).

One thing I need to point out is that the cell in column G for each date range also adds if a payment is made in the cell next to it in column F.

Does this make more sense or confuse you even more?

It would be so much easier if I could show the whole spreadsheet I have no issues what so ever you would understand the problem immediately if you could see the whole sheet.

 

[AlphaFrog]

Read your recent posts several times and still having a hard time understanding what the question is.

You want to do something similar to what was done before except you want to do it for weekly date ranges instead of a monthly date ranges. (this is called a statement of purpose often presented in the form of a question). Is that correct?

If yes, I don't understand why the previous solution wouldn't work. It calculates daily amounts from all the payments and then adds each daily amount for a month date range. It should do the same for a week date range. What is the tweak you want?

If you could describe the tweak you want based on the original solution and data set, that may be the simplest way to approach this.

 

[computaplanet]

Read your recent posts several times and still having a hard time understanding what the question is.

You want to do something similar to what was done before except you want to do it for weekly date ranges instead of a monthly date ranges. (this is called a statement of purpose often presented in the form of a question). Is that correct?

If yes, I don't understand why the previous solution wouldn't work. It calculates daily amounts from all the payments and then adds each daily amount for a month date range. It should do the same for a week date range. What is the tweak you want?

If you could describe the tweak you want based on the original solution and data set, that may be the simplest way to approach this.

Im sorry Alphafrog, Im not the best at explaining myself lol, which is a bit of a hinderance when I am on forums asking for help, so I thank you for your patience.

Basically I think your solution from my original query will work on the weekly payment table. I want to do the same thing on the weekly table that I did on the four weekly table (by ensuring that any payments made in the weekly table which cross over the date ranges in table 1 are split up correctly...) I am unsure of what I need to change to get it to work on the new weekly table, which is why I contacted you.

From your explaination of the solution from the first problem then now I am positive that this should be achieved... but how I dont know...

Do I just need to copy the formula from the first solution (the formula I put into cell C20:C31) and change it slightly so it refers to the new weekly table so the VBA code that you wrote for the original problem works on the new weekly table aswell or do I have to change the VBA code in any way?

or does it need a second piece of VBA code and a new formula?

or .... does it need an entire new solution lol

Again thankyou for your patience...Im a little thick I know lol

 

[computaplanet]

Thankyou alphafrog from your explaination of what the vba code was actually doing I figured out that by copying the original formula and changing the referenced cells to the new weekly table it now works perfectly on that too! I apologise for confusing you totally and utterly but it now works on the second table also

I didnt know whether the VBA code you wrote was doing anything which i would need to change to get it to work properly on a weekly table as the original problem was on a four weekly basis, as soon as you said the VBA code was splitting the payments into a daily rate I was able to figure out the rest myself...

Thankyou once again for your time and for helping me! if there was anyway I could possibly repay you I would! You have really been a life saver! and without your help on it I would still be here litterally pulling my hair out...

 

[AlphaFrog]

If i understand your question, it should work on a weekly date range just like it works on a monthly date range. The code should be the exactly same. The formula would be similar except for the specific cell references.

This is the syntax of the custom function;
=PCMTotal(Start_Date, End_Date, Periods_range, Payments_range)

Do I just need to copy the formula from the first solution (the formula I put into cell C20:C31) and change it slightly so it refers to the new weekly table so the VBA code that you wrote for the original problem works on the new weekly table as well ?

Yes.

or does it need a second piece of VBA code

No


EDIT: Posted before reading your most recent post that you got it working.