marcel_911
New Member
- Joined
- Mar 12, 2015
- Messages
- 13
Hello,
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)
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.
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.
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.
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?
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)
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.
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.
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.
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?
