Continue series after a break

[jomili]

I'm writing a program to make projections. Part 1 looks at history, part 2 looks at future.

In part 1, Cells D2,E2, and F2 say "June, July, August" this month, next month they'll say "June, July, August, September". Following the months is the Total column. Then we come to part 2. To the right of the total column I'd like the continuation of the series for my projection (3 months only). So, for this month, G2 is Total, H2, I2, and J2 should be "September, October, November". Next month they all shift one to the right.

I know how to do the shift to the right, but how do I continue the series?

Any help is greatly appreciated.

[davegugg]

Could you include the portion of your code that labels the cells with the months, including the Total column?

Even better would be a sample workbook.

[jomili]

Sorry, I should have been more descriptive.

In Part1, I create a pivot from historical data, which includes the Month. No trick to labeling there. I copy and paste the pivot as values and formats. I could provide a sample of my code, but don't know that it would serve any good purpose. However, I have attached a sample of what I'm working with.

[davegugg]

If you are just looking to extend the labels, this will do it:

Please Login or Register  to view this content.

It searches through each column in ROW 4 searching for the text "Grand Total". Then it finds the month name right before that column, and adds the next 3 months. Is that what you are looking for?

[jomili]

Wow!! That's exactly what I'm looking for, and even better! What you've provided is easily adapted for other uses, and you can bet I'll use it.

I've tested it out, and other than having to provide a Dim statement for J it worked perfectly.

Thank you so much!

[davegugg]

Oh, yeah, I forgot about declaring the j. After thinking about this a bit more, it is going to need a slight modification for the end of year. I'll post an update soon.

[jomili]

Thanks for thinking ahead. I tried it using Ocotober, November, and December as my history, to see what it would do. It didn't error out, it just didn't put anything after total.

[davegugg]

Well, this fix is ugly, but it'll work. For some reason I can't wrap my mind around how to get the Mod operator to work for this situation, even though it practically screams for it.

Please Login or Register  to view this content.

Thanks for your patience with my original oversight.

[jomili]

No need to thank me; you're the one who's doing the heavy lifting. I'm very content to use your ugly work, because it DOES work. That's what I appreciate.

Thanks for all your help.

[jomili]

Turns out I need more help on this one. The complication factor has gone up.

The number of months before grand total (history) varies from 1 to 12, and the number of months AFTER grand total (forecast) also varies from 1 to 12. It's dependent on our fiscal year, which begins in September and goes through August. We might have history for February and March, which means the forecast months are April, May, June, July, and August. Or we could have history of September through July, and only be projecting for August. So, history months (prior to Grand Total) + Forecast months (after Grand Total) may not add up to 12, but the Forecast months should always follow the latest history month and end in August.

Also, in the Forecast months, instead of June, July, August, it should say "Base Projected-June", etc.

Any help would be greatly appreciated.

[davegugg]

Here is a slight modification that should do what you are asking for:

Please Login or Register  to view this content.

[jomili]

Dave,

You're a miracle worker. Thank you so much!