[kamal1964]

I am trying to sort data in Excel 2003. Problem is the data range I am sorting contains sumif formulae and you cannot sort data properly

Any help?

[JBeaucaire]

You are correct, you cannot sort data based on cells with formulas in them and get very consistent results. But you can try.

Click GO ADVANCED and use the paperclip icon to post up your workbook. Perhaps make a second copy of the sheet in question and demonstrate manually the sort you'd like to achieve. Maybe we can figure it out.

[kamal1964]

Thx JB
The data is sensitive so cannot post workbook here.
I appreciate your help.
I just want to be able to sort the column that has the sumif formulae in descending order (resulting data is in % i.e 100% , 95% etc)
I appreciate I can to a paste-special of the data and then sort but when I send the file out I want the links to be retained to source data.

[JBeaucaire]

Creating a non-sensitive version of a workbook takes about 2 minutes... replace names the NAME1, NAME2 then copy down, Phone1,Phone2 copy down... etc.

Very easy to create. I really can't suggest anything helpful until I see the data layout and formulas you're trying to manage.

In a sample workbook, you COULD do a pastespecial>values and sort on a copy of the original sheet so I could see a DESIRED RESULTS page along with your Original.

[kamal1964]

Hi JB - thanks

Due to the rules at my work I unfortunately cannot attach any files.

But thanks for your help

[JBeaucaire]

For this to work simply, you'll keep the existing Fill Rates Summary sheet as is. It does all the heavy lifting is and worth using as a prestage.

Create a permanent second sheet with a name like "Sorted Summary" or whatever.

1) Right-click on the new sheet tab
2) Select VIEW CODE
3) Paste in this activation macro:

Option Explicit

Private Sub Worksheet_Activate()
Dim LR As Long
Application.ScreenUpdating = False
Range("A1:O" & Rows.Count).Clear

LR = Sheets("Fill Rates Summary").Range("A" & Rows.Count).End(xlUp).Row + 2
Sheets("Fill Rates Summary").Range("A1:O" & LR).Copy
Range("A1").PasteSpecial xlPasteValues
Range("A1").PasteSpecial xlPasteFormats

Range("B35:O" & LR).Sort Key1:=[O34], Order1:=xlDescending, Key2:=[B34], Order1:=xlAscending, _
    Header:=xlNo, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

Application.ScreenUpdating = True
Beep
End Sub

4) Close the editor
5) Save your workbook as a macro-enabled workbook.

Now, any time you bring this new worksheet up onscreen, you will hear a "beep" indicating the sheet has been updated.

[kamal1964]

Thx JB
Fab Macro routine, however I was anticipating whether it was possible to sort sumif cells.
I assume this is not the case?
However thanks for the Macro which is an excellent workaround

[JBeaucaire]

Sorry, I thought we covered that in post #2. No, you can't sort cells with things like SUMIF() and get reliable results. Best to leave your reliable layout alone and create flat "reports" from it.

[kamal1964]

Cheers

You have been great help, much appreciated.
Regards
Kam