Using Excel 2010
I need to create a table (report) that will vary in length depending on the number of months determined by the user.
In separate cells I have a "Start Month" (e.g. January 2016) and an "End Month" (e.g. March 2016); these are both selectable from a drop down list sourced from a range of dates elsewhere in the spread sheet.
I have a cell (A13) which calculates the number of months between the two dates (=(YEAR(EndDateMthly)-YEAR(StartDateMthly))*12+MONTH(EndDateMthly)-MONTH(StartDateMthly)+1) so if the user was to select the Start Month and End Month as in example above this would give the result of 3 in cell A13. If both dates were January 2016, this would return a value of 1.
So if I have a "Start Month" (e.g. January 2016) and an "End Month" (e.g. March 2016) then I would need the report to be 3 rows high, with a one row for each of the months. See example below.
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]30 Days[/TD]
[TD]60 Days[/TD]
[TD]90 Days[/TD]
[TD]180 Days[/TD]
[/TR]
[TR]
[TD]January 2016[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]February 2016[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]March 2016[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
If both Start Month and End Month were to be January 2016 then the table would look like
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]30 Days[/TD]
[TD]60 Days[/TD]
[TD]90 Days[/TD]
[TD]180 Days[/TD]
[/TR]
[TR]
[TD]January 2016[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]
I am using VLOOKUP's to find the data in other columns (30, 60, 90, 180 Days), but I cannot find a way to make the number of rows presented in the table variable based on the number of months input parameter.
I obviously also need to be able to ensure my VLOOKUP's cover the same range of dates selected by the user. I don't expect the user to select a date range of greater than 24 months.
Lastly, I need to have several of these variable months reports on the same tab in the spread sheet one above the other, so would need to be able to move the subsequent reports up and down depending on the number of months selected to avoid the reports overlapping each other.
Any suggestions would be much appreciated.
FYI - I'm an intermediate user of excel so I hope I'll be able to understand any replies to this post.
I need to create a table (report) that will vary in length depending on the number of months determined by the user.
In separate cells I have a "Start Month" (e.g. January 2016) and an "End Month" (e.g. March 2016); these are both selectable from a drop down list sourced from a range of dates elsewhere in the spread sheet.
I have a cell (A13) which calculates the number of months between the two dates (=(YEAR(EndDateMthly)-YEAR(StartDateMthly))*12+MONTH(EndDateMthly)-MONTH(StartDateMthly)+1) so if the user was to select the Start Month and End Month as in example above this would give the result of 3 in cell A13. If both dates were January 2016, this would return a value of 1.
So if I have a "Start Month" (e.g. January 2016) and an "End Month" (e.g. March 2016) then I would need the report to be 3 rows high, with a one row for each of the months. See example below.
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]30 Days[/TD]
[TD]60 Days[/TD]
[TD]90 Days[/TD]
[TD]180 Days[/TD]
[/TR]
[TR]
[TD]January 2016[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]February 2016[/TD]
[TD]15[/TD]
[TD]25[/TD]
[TD]35[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]March 2016[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[TD]13[/TD]
[/TR]
</tbody>[/TABLE]
If both Start Month and End Month were to be January 2016 then the table would look like
[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD]MONTH[/TD]
[TD]30 Days[/TD]
[TD]60 Days[/TD]
[TD]90 Days[/TD]
[TD]180 Days[/TD]
[/TR]
[TR]
[TD]January 2016[/TD]
[TD]10[/TD]
[TD]20[/TD]
[TD]30[/TD]
[TD]40[/TD]
[/TR]
</tbody>[/TABLE]
I am using VLOOKUP's to find the data in other columns (30, 60, 90, 180 Days), but I cannot find a way to make the number of rows presented in the table variable based on the number of months input parameter.
I obviously also need to be able to ensure my VLOOKUP's cover the same range of dates selected by the user. I don't expect the user to select a date range of greater than 24 months.
Lastly, I need to have several of these variable months reports on the same tab in the spread sheet one above the other, so would need to be able to move the subsequent reports up and down depending on the number of months selected to avoid the reports overlapping each other.
Any suggestions would be much appreciated.
FYI - I'm an intermediate user of excel so I hope I'll be able to understand any replies to this post.
