Formula Question

[bullionbuks]

I am trying to create a formula where a range i.e. ($B$2:B8) that has an absolute reference for the first cell and a relative reference i.e. ($B$2:B8) for the other end of the range, but as the formula passes over 1 year (12 months) the absolute value changes to be the first month of the next year. Any help on this would be greatly appreciated. Also if any addition clarification is needed please let me know.

 

[HalfAce]

as the formula passes over 1 year (12 months) the absolute value changes to be the first month of the next year.

I take it you've got a formula in B2 that is updating?
What formula do you have in there?

 

[bullionbuks]

B2 is the beginning of the percent values of a sales curve. Each month as we get actual sales the model is forecasting the remaining sales based on the previous year's sales curve. It dose it by taking the SUM(YTD Monthly Sales)/SUM(Prior YTD Sales %)*Monthly Sales Curve. The problem is the formula needs to be reset at the end of the year to start with month 1's sales.

 

[HalfAce]

So... what actions do you take to reset the formula - and what's the formula look like?

 

[bullionbuks]

(SUM($B22:F22)/SUM($B15:F15))*G15*((1+Sales_Growth_Rate)^G$19) this what the formula looks like. I would like to add something that changes both B22 and B15 to new absolute references when the forecast passes December 31. I might not be explaining it well so please let me know if this makes sense. Thank you in advance.

 

[HalfAce]

Well, We can't write a formula that will change the value (or formula) of another cell, so I suppose we could write a little vba routine that would get run once a year (presumably right after New Years) to change the formulas in those cells (B15 & B22).
Knowing what the formula looks like now, what would you change it to if you were changing (resetting) it manually?
And, if we were to make this automated so anyone could reset it by running this code, how would we determine what the formula should get changed to in years 2, 3, 4, etc.?