Hi
I am creating a workbook for supplier order schedule monitored and forecasted on a weekly basis and currently using this formula:
=SUMIF($L$1,"<="&$E$2,N5)+SUMIF($O$1,"<="&$E$2,Q5)+SUMIF($R$1,"<="&$E$2,T5)+SUMIF($U$1,"<="&$E$2,W5)+SUMIF($X$1,"<="&$E$2,Z5)+SUMIF($AA$1,"<="&$E$2,AC5)+SUMIF($AD$1,"<="&$E$2,AF5)+SUMIF($AG$1,"<="&$E$2,AI5)+SUMIF($AJ$1,"<="&$E$2,AL5)+SUMIF($AM$1,"<="&$E$2,AO5)+SUMIF($AP$1,"<="&$E$2,AR5)+SUMIF($AS$1,"<="&$E$2,AU5)+SUMIF($AV$1,"<="&$E$2,AX5)+SUMIF($AY$1,"<="&$E$2,BA5)+SUMIF($BB$1,"<="&$E$2,BD5)+SUMIF($BE$1,"<="&$E$2,BG5)+SUMIF($BH$1,"<="&$E$2,BJ5)+SUMIF($BK$1,"<="&$E$2,BM5)+SUMIF($BN$1,"<="&$E$2,BP5)+SUMIF($BQ$1,"<="&$E$2,BS5)+SUMIF($BT$1,"<="&$E$2,BV5)
I am having to manually add cells to the formula when new data is added.
L1, O1, R1, etc., (i.e. every 4th column) is the WEEKNUM.
E2 is WEEKNUM(TODAY)
N5, Q5, T5 etc., (again every 4th column) is the balance of any outstanding order
I have attached a sample. Would appreciate any help in automating the formula
Bookmarks