Cell references going wrong when I sort

[StuartR]

I have fixed this problem now, but thought I would share it here in case someone else has the same error in their code and I save them from making my mistake.

I had some data with a column that used SUMIFS to calculate totals. When I sorted the data the results all went wrong, and it took me a while to find my error.

The data was on a sheet called Projects and the incorrect formula in cell G3 looked like this:
=SUMIFS(Expenditure!I:I,Expenditure!J:J,'Projects'!B3,Expenditure!A:A,Summary!$F$3)
When I sorted the data, the reference to 'Projects'!B3 remained unchanged, even though this was now on row 8 instead of row 3.

I corrected the formula to
=SUMIFS(Expenditure!I:I,Expenditure!J:J,B3,Expenditure!A:A,Summary!$F$3)
and everything now works correctly.

[Rudi]

TX for the share Stuart.

If I remember correctly I can recall a similar error while processing a file for analysis. I cannot remember the actions I took that caused the error but I do recall it being due to the referencing of the current sheet name in the formula. It's almost like a sheet circular reference?!

[rory]

Excel treats it like a reference to another sheet so it doesn't get treated as being relative to the formula cell when you sort.

[StuartR]

Excel treats it like a reference to another sheet so it doesn't get treated as being relative to the formula cell when you sort.

I wish I'd known that earlier Rory.

[rory]

I always figure you're more likely to remember it when you learn it the hard way. ;)

[StuartR]

I always figure you're more likely to remember it when you learn it the hard way. ;)

You're not wrong there. I always explained to my kids that they could avoid all those stupid mistakes by paying attention to my wise advice. Might as well have saved my breath.