I have a workbook with 2 worksheets, SUM and Weekly Forecast. Based on the
weekly forecast we hold excess stock of product for a predetermined number of
weeks. The number of weeks can change with each quarter of the year. I am
trying modify the file so that when a new forecast is loaded Excel will
create updated excess stock values.

From the Excel side:

I have my cursor in cell N5, the date in cell N4 is 06-FEB-06, cell N3 has
the value 1 from the formula =1+INT((MONTH(N$4)-1)/3) {this tells which
quarter to use}. Column B contains the value for each item for the first
quarter, Col E is Q2, Col H is Q3 and Col K is Q4. I have a formula which I
manually enter to calculate the excess based on that quarters value. That
formula is: =IF($B5=0,0,SUM('Weekly Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1))).

This works fine for the first quarter, but when I get into the second
quarter I have to manually change $B5 to $E5 and so on. This gets very
cumbersome.

What I would like is some method to read the month value from row 3 and use
this to determine which col to use to get the weeks value.

I have been banging my head on this for a few weeks to any help would be
appreciated.

--
Thanks in advance,
JC

This should be a simple fix.
It sounds like you should be able to add four nested IF statements to
account for each of the four quarter possibilities... this would be a
megaformula... not usually a good option. But if you are feeling
ambitious then try...
=IF(N3=1,(IF($B5=0,0,SUM('Weekly Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1)))),(IF(N3=2,(IF($E5=0,0,SUM('Weekly
Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1)))),(IF(N3=3,(IF($H5=0,0,SUM('Weekly
Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1)))),(IF(N3=4,(IF($K5=0,0,SUM('Weekly
Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1)))),XXX)))))))

I wrote this freehand so it may need to be reformatted, but this should
get you started.

Feel free to drop me a line should you need more help!

JC,
Any chance you could send a copy of your workbook to
if it is not too large?

Otherwise, could you please answer the following questions?
1. Where exactly and on what sheet is the formula =IF($B5=0,0,SUM('Weekly
Forecast'!O5:OFFSET('Weekly Forecast'!O5,0,SUM!$B5-1)))?
2. The other data/formulae you have given: are they on the "SUM" sheet or
the "Weekly Forecast" sheet?

Any further detail would be helpful.

Regards,

Ian


--
Work performed in the spirit of service is worship...Baha''''i Writings


"JC" wrote:

I have a workbook with 2 worksheets, SUM and Weekly Forecast. Based on the
weekly forecast we hold excess stock of product for a predetermined number of
weeks. The number of weeks can change with each quarter of the year. I am
trying modify the file so that when a new forecast is loaded Excel will
create updated excess stock values.

From the Excel side:

I have my cursor in cell N5, the date in cell N4 is 06-FEB-06, cell N3 has
the value 1 from the formula =1+INT((MONTH(N$4)-1)/3) {this tells which
quarter to use}. Column B contains the value for each item for the first
quarter, Col E is Q2, Col H is Q3 and Col K is Q4. I have a formula which I
manually enter to calculate the excess based on that quarters value. That
formula is: =IF($B5=0,0,SUM('Weekly Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1))).

This works fine for the first quarter, but when I get into the second
quarter I have to manually change $B5 to $E5 and so on. This gets very
cumbersome.

What I would like is some method to read the month value from row 3 and use
this to determine which col to use to get the weeks value.

I have been banging my head on this for a few weeks to any help would be
appreciated.

--
Thanks in advance,
JC

Jim,

Thanks for sending your workbook.
It looks as though you were 90% of the way there already. Try using
"OFFSET($A5,0,N$3*3-2)" in place of "$B5" in your formula. This should
reference the correct column (B,E,H etc.).

I hope this works for you.

Regards,

Ian
--
Work performed in the spirit of service is worship...Baha''''i Writings


"JC" wrote:

I have a workbook with 2 worksheets, SUM and Weekly Forecast. Based on the
weekly forecast we hold excess stock of product for a predetermined number of
weeks. The number of weeks can change with each quarter of the year. I am
trying modify the file so that when a new forecast is loaded Excel will
create updated excess stock values.

From the Excel side:

I have my cursor in cell N5, the date in cell N4 is 06-FEB-06, cell N3 has
the value 1 from the formula =1+INT((MONTH(N$4)-1)/3) {this tells which
quarter to use}. Column B contains the value for each item for the first
quarter, Col E is Q2, Col H is Q3 and Col K is Q4. I have a formula which I
manually enter to calculate the excess based on that quarters value. That
formula is: =IF($B5=0,0,SUM('Weekly Forecast'!O5:OFFSET('Weekly
Forecast'!O5,0,SUM!$B5-1))).

This works fine for the first quarter, but when I get into the second
quarter I have to manually change $B5 to $E5 and so on. This gets very
cumbersome.

What I would like is some method to read the month value from row 3 and use
this to determine which col to use to get the weeks value.

I have been banging my head on this for a few weeks to any help would be
appreciated.

--
Thanks in advance,
JC