Auto populate cells from data in a 6 month range starting with the current month

Hi,

I am looking to auto populate cells within a given range of 6 months. I have a workbook with two different worksheets. One worksheet can be referred to "Data History Dump" while the other is the "6 Month Report". What I am trying to create is a forecasting tool to where the "Data History Dump" will pull data that will forecast future demand of a product. Because I need only the current month plus 5 additional months, I need to find a way to pull data from only those specified months (based on current month). An example would be that on the "Data History Dump" tab, I have columns B1, C1, D1, E1, F1, G1, H1, I1 that are named after months July 2012, August 2012, September 2012, October 2012, November 2012, December 2012, January 2013, February 2013 respectivly. On the rows I have department numbers and the demand associated with those department numbers according to the month. This spreadsheet will be updated every month. I need a macro that will be able to pick out the current month and return the demand associated with the month as well as the 5 consecutive months after that. I will need it to autopopulate at the end of every month as well as fill in the appropriate dates on the first row of "6 month repor". Thanks so much I am looking forward to your response! Also, I am pretty good with VBA but by no means excellent if this helps.

Hi

Are your headers for future months already in the data tab, or do they get inserted as the new data arrives?

rylo

They need to be inserted with the data and be able to roll over once a new month has begun.

Hi

You can do this with formulas, and the output will be based on the data in the data sheet. If your data sheet is called Data History Dump, then in B1 of the report sheet enter the formula =OFFSET('Data History Dump'!$A1,0,COUNT('Data History Dump'!1:1)+COLUMN()-7) and copy across to G1. Now when new data is added to the data sheet, the output sheet will automatically recalculate.

HTH

rylo

That worked perfectly! Thank you so much you made my day!


emmy

@ ecarnley349

Welcome to the forum.

Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
Thanks.

Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.