Ben,
Wow! You did a lot of work here! Thank you.
Going to the example you posted:
Columns C through H (Including Grand total) come from my original pivot table, so there's no need for a formula for the Grand total. I'm thinking the history range you provided could be useful, but I'm not sure just how. For instance, right now the sample shows my history is in E:G, which means Grand Total is in H, and my Base Projected starts in I. However, I only selected 3 history months; I could have chosen 10, which would have changed where Base Projected starts. The number of Base Projected months is dependent not on how many history months are used, but on how many months need to be projected (if latest history month is Feb, then March through August need to be projected. If latest history month is August, then September through August need to be projected.)
The key to the # of columns isn't the History months, but the Base Projected months. Whereever they exist, the next column is my "Allocated", for which I can plug in the formula. However, beginning in "Adjusted", I need to first identify how many columns I need (same as "Base Projected) then populate them with my formula
"). My macro creates ALL of the column headings, just no formulas. If I use the existing formulas I need to having a macro to identify/change I,K, and G to whatever pertinent columns exist, so I either need flexible formulas or an identifier macro. Your idea of naming the columns may come in handy there; I hadn't thought of that angle.
I'll be pulled away on other tasks today but will check back whenever I'm able. I appreciate any help or insight you can provide.
Thanks,
John
Bookmarks