total sheet messes up when moving data

[John Kramer]

I have a workbook with 5 sheets. sheet 1-4 have weekly
data and sheet 5 total's up the data in sheets 1-4. The
problem is that when a new weeks data comes in, I delete
the data in sheet 1, cut & paste the data from sheet 2
into sheet 1 and so on so. Then I paste the new data in
sheet 4. But when I go to sheet 5 I have #ref error. I
have to redo the fields again to add sheet 1-4. I have
it: =IF('1st'!E9+'2nd'!E9+'3rd'!E9+'4th'!E9=0,"",'1st'!
E9+'2nd'!E9+'3rd'!E9+'4th'!E9). Is there away to make it
so it doesn't change when I move the data?

John

 

[keyt]

When you are moving form 2 to 1, and 3 to 2, use Edit -
Paste Special, Paste Values. That way the cell references
don't move.

Also, on Sheet 5, use Absolute Referencing.
IE sheet1!A1 + sheet2!A1 should read

sheet1!$A$1 + sheet2!$A$1

 

[John Kramer]

I'll give it a try. I won't be able to do that until
next week. I'll have to redo the macro. Unless you know
what you put to paste special value in the macro. Right
now it just pastes. And making all those cells absolute
will be a pain unless you have a quick way to change
about 100 to 200 cells. I'm using Excel XP.

John