Stopping Auto Updating Of Formulae

    Everytime that I insert a row into a worksheet which a number of formulae refer to, those formulae get updated automatically in such a way that it is not in my favour.


    The formulae refer to rows 2:2000 on a number of different columns. Adding a new row shifts the references to 3:2001. I presume that I can stop this from happening - please help.


    Example:


    =SUMPRODUCT(('List of DD Donors'!E2:E2000)*('List of DD Donors'!F2:F2000='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G2:G2000)-1,DATE(2006,3,31),"m")))


    changes to...


    =SUMPRODUCT(('List of DD Donors'!E3:E2001)*('List of DD Donors'!F3:F2001='DD Tally'!E6)*IF(DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")>12,12,DATEDIF(('List of DD Donors'!G3:G2001)-1,DATE(2006,3,31),"m")))

    Re: Stopping Auto Updating Of Formulae


    Hi .broken


    You could also use absolute references to force the issue. Changing the formula is not too difficult using the F4 key.


    John

    Re: Stopping Auto Updating Of Formulae


    but even if you use absolute references then the range will increase if you insert a row in-between

    Re: Stopping Auto Updating Of Formulae


    Changing to absolute had no impact... other than the error checker didn't pick it up as an error!

    Looking into the INDIRECT function.

    Cheers so far guys!

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login