Macro Programming Syntax

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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
(e-mail address removed) 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
 
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
 
Back
Top