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!

 

[JWiser]

Unfortunately you can't sort results driven by formulas.

 

[Norie]

theDrew

What do you actually have selected when you try the sort?

I would recommend you select both columns and rather than just hitting the sort button on the toolbar goto Data>Sort...

If that doesn't work then there's probably something else going on.

 

[theDrew]

Unfortunately you can't sort results driven by formulas.

Hell, in all my years of using Excel, I have never stumbled upon this unfortunate issue. Blargh.

 

[Norie]

Hell, in all my years of using Excel, I have never stumbled upon this unfortunate issue. Blargh.

Perhaps because it's not actually true.

 

[theDrew]

theDrew

What do you actually have selected when you try the sort?

I would recommend you select both columns and rather than just hitting the sort button on the toolbar goto Data>Sort...

If that doesn't work then there's probably something else going on.

That's what I've been doing (I haven't been using the toolbar).

 

[JWiser]

Hey...

sorry for the misinformation. I was pulling from memory (not always the smart thing to do in my case!)

So I did a little testing. I thought it might have been that you referenced a full column, but that seemed to work just fine (in xl2007 at least).

I'm curious... if you have an autofilter on the data, will it sort?

 

[theDrew]

Hey...

sorry for the misinformation. I was pulling from memory (not always the smart thing to do in my case!)

So I did a little testing. I thought it might have been that you referenced a full column, but that seemed to work just fine (in xl2007 at least).

I'm curious... if you have an autofilter on the data, will it sort?

No, it doesn't work right if I use an autofilter either. This is a spreadsheet that is generated by an application (think "Click here to download in MSExcel format"), so I suspect that something screwy happens during the automated creation of the sheet.

 

[ZVI]

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

 

[JWiser]

Thanks SV1! Good to know.