Formulas for shifting targets.

This post is related to another in the Programming section, http://www.excelforum.com/excel-prog...h-a-twist.html if you would like more background.

I'm trying to create an automated method of creating a forecast (I've got twelve users around the state who will be using it) and the staffing piece is only the first step. That first step is a doozy to try to automate. I've provided an example of my desired output. I have everything including the column titles up to my "Adjusted", and that's where I'm stuck. I know the formulas I need to place in my columns if I can see my columns, but the number of columns is variable, depending on how many months my user has chosen for his history to project from, and the number of months remaining in our fiscal year to project for (FY is September through August).

For instance, in my "Adjusted" columns, the number needs to be the "Base Projected" if that equals or is less than the "Authorized". However, if "Base Projected" is greater then the "Authorized" the value needs to be the last amount amount reported in our History months. I've got the formula I need, but don't know how to adapt it to this situation. The formula (which works in the example attached) is

Please Login or Register  to view this content.

My problem is that I, K, and G are all shifting targets, so I don't know how to structure my formulas to catch them.

To further complicate the matter, after this Staffing projection I'll be doing a Salary projection, which will build on the values in the various columns of the Staffing projection, so I'll need formulas to reference back to these columns. My head is already reeling!

BTW, I also have a word document I created for my users to do all the steps. They're not the most Excel literate (kind of like me!) so the doc is full of pictures and explanations. If anyone wants to take a look at it and help me automate it all I'd be most overjoyed. Let me know and I'll post it or send it to you.

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

Please Login or Register  to view this content.

"). 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

I'm finally back to paying attention to this post, and find that no one else has offered a suggestion. I'd sure appreciate some help on this one.