Macro Help - Amount Distributions and Dates

[KarenMichael1]

Hi! I am working on a spreadsheet and I could really use some assistance with developing a macro.

I need to take the period in column A, and match it to the corresponding column starting at column D.

For example,

Period 2016-06, corresponds to June-16.

I need this macro to do this indefinitely, with multiple lines going to the same period.

Alongside this, I also require a macro that will take the amount in column B, distribute it over the number of months that you see in column C, all of which originates at the corresponding month that matches with the period you see in column A.

I have attached an excel spreadsheet that provides a better understanding of what I am looking for.

Thank you very much!

A B C D E F G
[TABLE="width: 529"]
<colgroup><col><col><col><col><col span="3"></colgroup><tbody>[TR]
[TD]Period[/TD]
[TD]Amount[/TD]
[TD]Months[/TD]
[TD="align: center"]Apr-16[/TD]
[TD="align: center"]May-16[/TD]
[TD="align: center"]Jun-16[/TD]
[TD="align: center"]Jul-16[/TD]
[/TR]
[TR]
[TD]2016-06[/TD]
[TD]30000[/TD]
[TD]36[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-06[/TD]
[TD]40000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-06[/TD]
[TD]50000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-06[/TD]
[TD]60000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-06[/TD]
[TD]70000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]80000[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]90000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]100000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]110000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]120000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-05[/TD]
[TD]130000[/TD]
[TD]1[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-04[/TD]
[TD]140000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-04[/TD]
[TD]150000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2016-04[/TD]
[TD]160000[/TD]
[TD]0[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[excelginge]

Hi Karen,

Can you please post a sample of what you wish the result to look like.

In addition can you confirm that column 1 is a date formatted cells and not a text field, likewise for that dates shown on Row 1

Do you have any objection if this is done by formula instead of VBA. Both are options?

Regards

Excelginge

 

[excelginge]

Hi Karen,

Is this what you are looking for?

	A	B	C	D	E	F	G	H	I	J	K	L
Period	Amount	Months	End Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/Apr/2016[/TD]
[TD="align: right"]31/May/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2016-04[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]2016-07[/TD]
[TD="align: right"]2016-08[/TD]
[TD="align: right"]2016-09[/TD]
[TD="align: right"]2016-10[/TD]
[TD="align: right"]2016-11[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]31/May/2019[/TD]

[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]40,000.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]50,000.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]60,000.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]70,000.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30/Apr/2017[/TD]

[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]110,000.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]130000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]130,000.00[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

</tbody>

Excelginge

 

[KarenMichael1]

Hi Excelginge,

First off thank you very much for your assistance. Yes, this is something that I am looking for, however, if there is a zero in the months column, rather than it being entirely blank, I would like the value to be posted to the next date.

For example, if we take a look at row 10. Instead of it being blank, I would like it to be posted to 2016-06. The only question I have is the "end date" column, is that just the Period + the Months? If so that's fine I could hide that no?

Also, I have no real preference as to whether this is done using VBA or via formula. Whichever one works the easiest is fine with me. The only thing is that I would like to be able to continuously add data to this and have it populate the same way that you have demonstrated.

Once again thank you very much for your assistance!

 

[KarenMichael1]

Hi Karen,

Is this what you are looking for?

	A	B	C	D	E	F	G	H	I	J	K	L
Period	Amount	Months	End Date

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]
[TD="align: right"]01/Apr/2016[/TD]
[TD="align: right"]31/May/2019[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2016-04[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]2016-07[/TD]
[TD="align: right"]2016-08[/TD]
[TD="align: right"]2016-09[/TD]
[TD="align: right"]2016-10[/TD]
[TD="align: right"]2016-11[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]31/May/2019[/TD]

[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]40,000.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]50,000.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]60,000.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]70,000.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30/Apr/2017[/TD]

[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]110,000.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]130000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]130,000.00[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

</tbody>

Excelginge

If you would like to get a better understanding, I have provided a screenshot: Imgur: The most awesome images on the Internet


This one has a little more logic to it. Some of these are things that have started in months past, and I want to be able to input the initial value (Column C) in the first cell matching to the corresponding period, followed by the remainder divided by the number of months (the same way that you have it).

Thank you again!

 

[excelginge]

Hi Karen,

Here is a formula way to achieve the same thing. I will leave it up to you to how you format your sheet as to the row your data starts.

Excel 2012

	A	B	C	D	E	F	G	H
Min Date	Max Date
Period	Amount	Months	End Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]2019-05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2016-04[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]2016-07[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]31/May/2019[/TD]

[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]40,000.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]50,000.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]60,000.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]

[TD="align: right"]70,000.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30/Apr/2017[/TD]

[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]110,000.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]30/Apr/2016[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]130000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]

[TD="align: right"]130,000.00[/TD]

[TD="align: center"]15[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]140000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]16[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]150000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

[TD="align: center"]17[/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]160000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/Mar/2016[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"]=MIN($A:$A) [/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"]=MAX($D:$D)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=$B$2[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"]=IF($C$2>=EDATE($B$2,COLUMNS($E$3:E$3)),EDATE($B$2,COLUMNS($E$3:E$3)),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"]Drag the formula in Cell F3 across to the right suggest as a starter all the way to column BQ extra as you think fit[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(ISNUMBER($A4),EDATE($A4,$C4)-1,"") Drag this formula down to bottom row + say approx extra 500 rows extra as you think fit[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,"",$B4/$C4),"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"]Drag the formula in Cell E4 across to the right suggest as a starter all the way to column BQ extra as you think fit then drag the formula down to bottom row + say approx extra 500 rows extra as you think fit[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

This should give you the answer you want.

Don't forget that the sheet is now dynamic and will add column data say if the end date changes.


Excelginge

 

[excelginge]

Hi Excelginge,

First off thank you very much for your assistance. Yes, this is something that I am looking for, however, if there is a zero in the months column, rather than it being entirely blank, I would like the value to be posted to the next date.

For example, if we take a look at row 10. Instead of it being blank, I would like it to be posted to 2016-06. The only question I have is the "end date" column, is that just the Period + the Months? If so that's fine I could hide that no?

Also, I have no real preference as to whether this is done using VBA or via formula. Whichever one works the easiest is fine with me. The only thing is that I would like to be able to continuously add data to this and have it populate the same way that you have demonstrated.

Once again thank you very much for your assistance!

This email only just appeared.

Change formula in following cells as follows

Excel 2012

	A	B	C	D	E	F	G	H	I
Min Date	Max Date
Period	Amount	Months	End Date

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]2019-05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2016-04[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]2016-07[/TD]
[TD="align: right"]2016-08[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]31/May/2019[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]40,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]50,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]60,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]70,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30/Apr/2017[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]90,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]100,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]110,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]120,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]130000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]130,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(ISNUMBER($A4),EDATE($A4,IF($C4>0,$C4,1))-1,"")[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,0,IF($C4>0,$B4/$C4,$B4/1)),0)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes it would be updated every time you amend it, all you have to do is make sure that the formulas extend far enough for the extra data.


Good luck





Excelginge

 

[KarenMichael1]

This email only just appeared.

Change formula in following cells as follows

Excel 2012

	A	B	C	D	E	F	G	H	I
Min Date	Max Date
Period	Amount	Months	End Date

<tbody>
[TD="align: center"]1[/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"]2016-04[/TD]
[TD="align: right"]2019-05[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]2016-04[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]2016-07[/TD]
[TD="align: right"]2016-08[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]30000[/TD]
[TD="align: right"]36[/TD]
[TD="align: right"]31/May/2019[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]
[TD="align: right"]833.33[/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]40000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]40,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]50000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]50,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]60000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]60,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]8[/TD]
[TD="align: right"]2016-06[/TD]
[TD="align: right"]70000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]30/Jun/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]70,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]9[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]80000[/TD]
[TD="align: right"]12[/TD]
[TD="align: right"]30/Apr/2017[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]
[TD="align: right"]6,666.67[/TD]

[TD="align: center"]10[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]90000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]90,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]11[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]100000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]100,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]12[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]110000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]110,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]13[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]120000[/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]120,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

[TD="align: center"]14[/TD]
[TD="align: right"]2016-05[/TD]
[TD="align: right"]130000[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]31/May/2016[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]130,000.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]
[TD="align: right"]0.00[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]B2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]C2[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I3[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]D4[/TH]
[TD="align: left"]=IF(ISNUMBER($A4),EDATE($A4,IF($C4>0,$C4,1))-1,"")[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]E4[/TH]
[TD="align: left"]=IFERROR(IF(AND(ISNUMBER(E$3),ISNUMBER($A4),E$3<$D4,E$3>=$A4)=FALSE,0,IF($C4>0,$B4/$C4,$B4/1)),0)[/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]F4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]G4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]H4[/TH]
[TD="align: left"][/TD]
[/TR]
[TR]
[TH="bgcolor: #DAE7F5"]I4[/TH]
[TD="align: left"][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Yes it would be updated every time you amend it, all you have to do is make sure that the formulas extend far enough for the extra data.


Good luck





Excelginge

Hi Exelginge,

Apologies for the late response. Thank's again for all of the assistance, you've saved me hundreds of hours over the course of the year with this!