Moving data makes unwanted change to formula

[papajock1950]

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.

 

[JLGWhiz]

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.

 

[papajock1950]

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.





- 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