Moving data makes unwanted change to formula

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

papajock1950@googlemail.com - 13 Dec 2007 09:23 GMT

I have a spreadsheet for recording room usage. Column A has StartTime,
Column B has EndTime and Column C has formula EndTime - Startime.
Each day is split into 6 periods.
If I move (cut & paste) a booking from row 2 to row 4, the formula in
C2 adjusts to the new location of the data (even when absolute
reference is used) and the forumula in C4 becomes =#REF-#REF. I can't
cut & paste the whole line of data as Column C is protected and I'd be
unable to re-insert the formula.

Reply to this Message

JLGWhiz - 13 Dec 2007 13:31 GMT

Sounds like a copy and paste special operation would work. Then delete
contents of the old location. Cut takes formulas and links from the old
location which causes the errors in column C. Copy without the paste special
operation will put the formula and links of the old location in the new
location and leave the same thing in the old location which can also cause
error flags. The copy with paste special for values only, will leave all
formulas and links as they were.

> I have a spreadsheet for recording room usage. Column A has StartTime,
> Column B has EndTime and Column C has formula EndTime - Startime.
[quoted text clipped - 4 lines]
> cut & paste the whole line of data as Column C is protected and I'd be
> unable to re-insert the formula.

Reply to this Message

papajock1950@googlemail.com - 13 Dec 2007 14:40 GMT

> Sounds like a copy and paste special operation would work. Then delete
> contents of the old location. Cut takes formulas and links from the old
[quoted text clipped - 15 lines]
>
> - Show quoted text -

Copy & paste works fine but why should I need to do two operations,
Copy & Paste followed by Delete Original? I've been using Excel for
over 10 years and I'm surprised I've never come across this "feature"
before.
Mike

Reply to this Message