Table sorting with sheet names causes problems. Is this a bug?


New Member
Mar 12, 2015

In this example I have simulated sales data on the left, Fruit and Quantity sold. One row per order. This is how I receive similar data from Amazon.

So, I want to use a sumif to get a total of each fruit sold.

Generally I put the raw sales data from Amazon into one sheet and my results in another sheet. So, when I am building my sumif function, I am clicking between sheets to add references.
I start off on my results sheet, then have to click on the column in my sales data sheet, then click back to my results sheet to click on the criteria cell, then back to the sales sheet to click on the sum column.

By doing this, Excel puts the sheet name in front of my cell references. Even when I am back on the results sheet clicking on the criteria cell, it still puts the sheet name in. This isn't necessary as it is on the same sheet as the result.
but this causes my problem.

To be able to show my problem I have all this on one single sheet, but the problem still occurs.
This is the data to start with. Both tables show correct results. (Ignore the fact I spelt grapes with a S in the right table, it makes no difference to the problem)
Screenshot 2020-04-27 at 12.03.43.png

If I sort my left table of results by Fruit, ascending, the results in the sold column no longer tie up with the fruit name in the same row as you can see here.

Screenshot 2020-04-27 at 12.04.01.png
If I double click on the formula in cell E4, you can see the reference to the fruit in column D is is no longer in row 4. It's looking at the cell where Apple USED to be.

Screenshot 2020-04-27 at 12.04.19.png
If I do the same on the right hand table, you can see that it is looking as you would expect. Notice thought that the formula does NOT have the sheet reference in. I can sort the right hand table until the cows come home and it stays showing the correct results.

Screenshot 2020-04-27 at 12.04.32.png
Why should having the sheet reference in a formula cause this? Is it a bug or am I missing something and this would be a desired effect for some reason?

I check my sheets twice before I send them to my boss, but it makes me look a fool when he spots errors. It was fine until i sorted the table before i saved it out.

What is going on?

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Watch MrExcel Video

Forum statistics

Latest member