Sorting doesn't sort

[theDrew]

Hi all,

I have a sheet that lists revenues by country, as such:

M7 GMD EMIS Reports.xls
	A	B	C	D
1	Country	Revenue
2	ABUDHABI	$0
3	ARGENTINA	$190,825
4	AUSTRALIA	$436,781
5	BAHAMAS	$0
6	BAHRAIN	$862
7	BANGLADESH	$4,487
8	BARBADOS	$0
9	BOSNIA-HERZEGOVINA	$0
10	BRAZIL	$1,696,189
Country-Region (2)

The data in the "Revenue" column is calculated via the following formula:

=SUMIF('Sold Cases'!$D:$D,'Country-Region (2)'!$A2,'Sold Cases'!$N:$N)

I want to sort the data in descending order by Revenue. I run the Sort function and select Sort by Revenue - Descending. This sorts the Country column, but does not sort the Revenue column, as follows:

M7 GMD EMIS Reports.xls
	A	B	C	D
1	Country	Revenue
2	BRAZIL	$0
3	AUSTRALIA	$190,825
4	ARGENTINA	$436,781
5	BANGLADESH	$0
6	BAHRAIN	$862
7	ABUDHABI	$1,696,189
8	BAHAMAS	$4,487
9	BARBADOS	$0
10	BOSNIA-HERZEGOVINA	$0
Country-Region (2)

If I eliminate the formula by copying the Revenue column and then pasting the results via Paste Special - Values Only, then the data sorts correctly. I don't want to have to do this, though.

What could be the cause of this? It is driving me crazy!

 

[theDrew]

Hi,

The problem is in referencing to the current sheet cell in a formula.

Instead of:

=SUMIF('Sold Cases'!$D:$D,'Country-Region (2)'!$A2,'Sold Cases'!$N:$N)

use this one:

=SUMIF('Sold Cases'!$D:$D,$A2,'Sold Cases'!$N:$N)

Then sort & enjoy

Vladimir

Ding ding ding ding we have ourselves a winner. Thanks ZVI!

 

[Refugee]

Dear Vladimir,

I just logged in to thank you for this solution. Works great!

Hi,

The problem is in referencing to the current sheet cell in a formula.

Instead of:

=SUMIF('Sold Cases'!$D:$D,'Country-Region (2)'!$A2,'Sold Cases'!$N:$N)

use this one:

=SUMIF('Sold Cases'!$D:$D,$A2,'Sold Cases'!$N:$N)

Then sort & enjoy

Vladimir

 

[ZVI]

Welcome aboard Refugee and thank you for the response!

 

[Refugee]

Dear Vladimir,

I just logged in to thank you for this solution. Works great!

Unfortunately, I have ran into a problem nonetheless . All the references to the original sheet are gone, but still the data keeps getting messed up. The issue is the following:
I have a (quite elaborate) sheet, with many links to different tabs. However, there are a few fields which are empty and should be filled out.

Imagine:
Brand - Product - Price - Comment

Here the last 2 columns would be empty until filled out. I know that Excel has issues with empty values and sorting. It might be smarter to avoid it, but there is really not a whole lot of different options available as I need others to add comments and prices, but only in some fields.
Strangely enough, another tab which also has empty values which need 'comment' fields are perfectly sorting in any way possible. What is different between this tab and the other than?

Any thoughts would be greatly appreciated!

 

[Refugee]

Unfortunately, I have ran into a problem nonetheless . All the references to the original sheet are gone, but still the data keeps getting messed up. The issue is the following:
I have a (quite elaborate) sheet, with many links to different tabs. However, there are a few fields which are empty and should be filled out.

Imagine:
Brand - Product - Price - Comment

Here the last 2 columns would be empty until filled out. I know that Excel has issues with empty values and sorting. It might be smarter to avoid it, but there is really not a whole lot of different options available as I need others to add comments and prices, but only in some fields.
Strangely enough, another tab which also has empty values which need 'comment' fields are perfectly sorting in any way possible. What is different between this tab and the other than?

Any thoughts would be greatly appreciated!

PROBLEM SOLVED!
Apparently, some of the VLOOKUP functions were messing things up. Because the first item ('value') referred to entire columns, instead of specific cells. Once this was corrected AND I corrected for the references to the original sheet that include the name of the sheet (i.e. index!a:b,2,0 >> a:b,2,0) all was fine. Fine being: I can sort even empty values!

For me this is essential as I need to add 'comment fields' to existing data pulled from various sources. This means there will always be empty cells, which I don't want to fill with values every time.

Hope this helps for others as well. Have a great day!