Mark O'Brien
MrExcel MVP
- Joined
- Feb 15, 2002
- Messages
- 3,530
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
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
