workbook before close distorts formula result

[andrew21]

i have a workbook before close macro which inserts(copies) Row2 to Row
each time the workbook is closed(and also shifts the present data in Ro
4 down to Row 5 and so on)...in some of the cells of Row2 ar
formulas...for example =(SUM(OFFSET(F1,1,0,6,1))/5) which is a
period MA of the cells F2:F7(forgot to mention that Row3 is completel
blank)...i have this formula in H2 and it computes correctly...howeve
when i close the workbook and the result is shifted down to H4 th
result is changed(i.e. it fails to copy correctly even tho all th
other cells in Row2 which just contain regular data are inserte
correctly into Row4)...not sure if it's relevant but F2 is
paste-specialed link from another workbook...would anyone have an
ideas how to fix this?thank

 

[andrew21]

okay i found a quirky work-around....changing the formula by adding an
extra row so that =(SUM(OFFSET(F1,1,0,7,1))/5) gives the correct answer
in H4 when the workbook is closed..then i formatted H2 to appear
invisible and placed the original formula in H3 so that it will
correctly display the answer while the workbook is open...don't quite
understand why the formula is re-calculating prior to insertion into H4
when the workbook closes...another excel mystery i guess