Prevent formulas changing when moving value

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dave Parker - 29 Oct 2007 10:51 GMT

Since my place of work moved to Excel 2007 I regularly run into this kind of
problem.

I am filling in a timesheet with columns for monday-friday (columns D to H).
There is a total column at the end (I). The formula in the total column is
"=SUM(D__:H__)". Often I accidently type a value into, say, D, then cut and
paste it into E and then Excel changes the formula in I to no longer include
monday in the total (it becomes "=SUM(E__:H__)").

I seem to run into this problem all the time whenever I am using Excel now,
is there any way to switch it off? I've also tried using references like
=SUM($D__:$H__) but Excel still keeps changing them.

Reply to this Message

Pete_UK - 29 Oct 2007 11:47 GMT

Perhaps you should use <copy> and <paste> then <delete> the entry in
column D - saves you having to re-adjust the formula each time.

Hope this helps.

Pete

> Since my place of work moved to Excel 2007 I regularly run into this kind of
> problem.
[quoted text clipped - 8 lines]
> is there any way to switch it off? I've also tried using references like
> =SUM($D__:$H__) but Excel still keeps changing them.

Reply to this Message

Dave Parker - 29 Oct 2007 11:54 GMT

Thanks, that works. Guess I'll just need to adjust my habits to
copy-paste-delete rather than cut-paste.

> Perhaps you should use <copy> and <paste> then <delete> the entry in
> column D - saves you having to re-adjust the formula each time.
[quoted text clipped - 21 lines]
>> is there any way to switch it off? I've also tried using references like
>> =SUM($D__:$H__) but Excel still keeps changing them.

Reply to this Message

Pete_UK - 29 Oct 2007 12:06 GMT

Glad to help - thanks for the feedback.

Pete

> Thanks, that works. Guess I'll just need to adjust my habits to
> copy-paste-delete rather than cut-paste.
[quoted text clipped - 26 lines]
>
> - Show quoted text -

Reply to this Message