Create variable length table determined by input value

Bruzah

New Member
Joined
Aug 22, 2016
Messages
1
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.

MONTH30 Days60 Days90 Days180 Days
January 201610203040
February 201615253545
March 201610111213

<tbody>
</tbody>

If both Start Month and End Month were to be January 2016 then the table would look like
MONTH30 Days60 Days90 Days180 Days
January 201610203040

<tbody>
</tbody>


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.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Forum statistics

Threads
1,136,642
Messages
5,676,960
Members
419,665
Latest member
allsmilees2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top