Circular reference problem

[northender]

I have a large workbook with 23 sheets in total all working towards the last sheet, which tells me what to order. The problem I have is as laid out below. Row 31 obtains a weekly forecast of equipment to be issued. Row 32 obtains a weekly quantity that is being returned refurbished. Row 33 returns a quantity being returned as good to use. Row 34 is any other influence for that weeks issue. Row 35 is where all the calculation is carried out. It looks at the columns week number (row 30) and checks it against the actual week. If this is equal it then returns the value for the on hand stock level, - the forecast + refurb + gtu + other + any quantity in the PO line ( row 37) but from two weeks previous (the lead-time). The next column takes that on hand qty and carries out the same calculation. I currently have to put a formula in row 27 to subtract the finishing stock level form a maximum stock level to create the amount required to be ordered that week and then copy this manually down to the PO line to avoid the circular reference. This has to be done for each week across the sheet and then 21 times for different regions and then for 8 different items. Is there a way to write this into the formula without causing the circular reference?

edit: table code removed as messing with post

 

[Joe4]

You problem is a little hard to visualize from all the text in your question. Maybe if you post all the formulas involved, we can see exactly what is going on, and may be able to help.

 

[northender]

I hope this helps you understand better

CPE Forecast V2.2.xls
	B	C	D	E	F	G	H	I
27		Max - Onhand two weeks ahead	29,890	28,336	27,805	34,895	33,238	21,103
28		Year	2005
29	National	Month	Sep				Oct
30		Week	36	37	38	39	40	41
31		Forecast	17058	18694	19530	19706	17249	16844
32		Refurb	10484	9120	12007	10762	10893	9233
33		GTU	0	0	0	0	0	0
34		Other	0	0	0	0	0	0
35		Onhand	14378	15837	20110	21664	22195	15105
36		Max level	50000	50000	50000	50000	50000	50000
37		PO						8880
38		PO Month				0
STB

 

[northender]

This is the forumla from H35 ( onhand week 40 )

=IF(H30<=$B$1,(HLOOKUP(H30&"GTU",'Good Onhand actuals'!$B$4:$EI$26,23,FALSE)+HLOOKUP(H30&"Intran",'Good Onhand actuals'!$B$4:$EI$26,23,FALSE))+F37-H31+H32+H33+H34,G35-H31+H32+H33+F37+H34)