# Copy and Paste Formula strange behaviour

#### Mark O'Brien

##### MrExcel MVP
OK, I have a workbook with two sheets. ("Sheet1" and "Sheet2", just for arguments sake)

Now, on sheet1 I have a lot of data, it's monthly data about chemical stuff and is of no consequence to this oddity. Basically, what I've done is created a summary of the totals of chemicals used in the rows above the data using a basic formula like this:

=SUMIF(C\$11:C\$69, \$C2, D\$11:D\$69)

So basically this looks for the name of a unit (e.g. P-1 located in column C and takes the sum of chemicals for this unit from column D). Again, this is just incidental to my query here.

Now, in the interests of tidying up my spreadsheet, I am going to use sheet2 as a summary sheet for the chemicals used and create a report type thing on that page. Here's the curious behaviour:

When I copied all of my cells containing formulae from sheet1 to sheet2, my formulae all works correctly. However, the formulae still looks like this on sheet2:

=SUMIF(C\$11:C\$69, \$C2, D\$11:D\$69)

The values all add up and everything is groovy, but shouldn't this formula look something more like this:

=SUMIF('Sheet1'!C\$11:C\$69, \$C2, 'Sheet1'!D\$11:D\$69)

It doesn't explicitly reference Sheet1 in the formula, but is still using the values on sheet1 because sheet2 is completely blank except for my summary formulae.

1. I hope this makes sense.
2. Can anyone explain this?

TIA

### Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Yes, for the formula to work it should look like you said.

Is calculation set to manual? If so, what happens when you calculate?

Andrew,

Calculation is set to automatic, but I hit F9 a couple of times just for you. The main thing that's worrying me is that I hope my Excel install isn't corrupted or something. It's been acting a bit funny recently, i.e. pitching some fits and then killing itself.

(Oh yeah, XL 2000 to anyone that's interested)

Replies
0
Views
236
Replies
2
Views
377
Replies
3
Views
341
Replies
1
Views
109
Replies
19
Views
2K

1,220,955
Messages
6,157,041
Members
451,394
Latest member
indrajeet_rajput

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back