sorting by "difference from"

[miconian]

I used Pivot Table Options -> Field Settings -> Show Values As -> Difference From.

I did this to show the difference in dollars from one month to another. My report now has two columns: month1 (which is blank, as it should be, since it's the base), and month2 (which shows the difference between the months).

I then formatted the month2 column like so:

"increase by" #,##0_);[Red]"decrease by" (#,##0);"no change"

So, my "difference from" column now looks kind of like this:

no change
no change
increase by $1
no change
decrease by ($3)
no change


I want it to look like this:

no change
no change
no change
increase by $1
increase by $2
increase by $6
decrease by ($7)
decrease by ($10)

For the life of me, I cannot figure out how to do this sort. Alphabetical doesn't do it, in either direction. The pivot table will not let me do a manual sort because it's a custom calculation.

Maybe I could use a custom list, but I don't know what that list would consist of, since the numbers in question will change constantly.

Any thoughts?

 

[GlennUK]

Number formatting doesn't alter the actual values. So, you are wanting to sort in this order
zeroes
positive numbers
negative numbers
... which is not a valid sort order.

Do you have to do the sorting within the pivottable? Otherwise you could copy the pivottable results as values, and create a sort field via formulas, and sort that.

 

[miconian]

I don't have to do the sorting within the pivot table. The problem is that the raw data is going to be updated frequently. I need a solution that does not require the worksheet to be reformatted every time that happens.

Perhaps there is some way for the a non-pivot table (but still a table) to reflect the difference column's values, and that could be sorted, and it would also grow dynamically with updates? I have no idea how to do that, or if it's really possible.

 

[GlennUK]

I don't have to do the sorting within the pivot table. The problem is that the raw data is going to be updated frequently. I need a solution that does not require the worksheet to be reformatted every time that happens.

Perhaps there is some way for the a non-pivot table (but still a table) to reflect the difference column's values, and that could be sorted, and it would also grow dynamically with updates? I have no idea how to do that, or if it's really possible.

I don't see how you'd get an automatic process without using VBA.