Excel 2007 Autofilter Misbehaving 2

[Gruuuu]

I've created a report for my management staff who have a mixed bag of Excel technical aptitude. In the workbook I have a few recap pages which show a number of our profit centers alongside several metrics. Simple stuff. I've added an autofilter on the metrics to help them identify problem areas. Some of these people have 2003, some have 2007, so I developed everything in 2003 and have saved as a 2003 .xls file. When testing the autofilter in 2007, I get some odd behavior.

Data Layout:
Store Metric1 ...
1 215 ...
2 343 ...
... ... ...
Total 47182 ...

To prevent the total from being included in the autosort, I inserted a blank row between the constituent data and the total row. Of course it is still included in the filtering, but I'm ok with this.

In 2003, the sort ascending/sort descending feature of the autofilter works beautifully.

In 2007, it... doesn't. It rearranges the formatting and data, copies some formatting from the cells above(text color??? But not highlighting???). Also it does not move adjacent cell data when it sorts (Column A).

If I used the Sort Ascending/Sort Descending buttons on the Data Ribbon (in the Sort & Filter section: A>Z, Z>A) it works exactly as it does in 2003.

I need the Total row to be not included in the sort. I'm ok with it being included in the filter. I NEED the adjacent rows to move with the sort/filter.

I'm completely open to suggestions... I'm half-tempted to create controls to call the sorts, but I'm hoping that isn't necessary. Is there a way I can rearrange my data or set something up so that it is not behaving like this?

File attached.

 

[SkipVought]

Hi,

To prevent the total from being included in the autosort, I inserted a blank row between the constituent data and the total row.

I have made it a practice for the past 15 years with Excel, to put almost all my aggregations, certainly the grand columnar aggregations, at the TOP of my table (separated from the table by an empty row) This solves so many issues, that can plague reports with grand aggragations BELOW the report. My users seem to appreciate NOT having to SEARCH FOR THE BOTTOM LINE. Its ALWAYS in the SAME PLACE! There comfort in that! ;-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Gruuuu]

Augh! That's so stupidly simple. Of course that solves all of these problems. Thank you!
Star for you sir.

 

[SkipVought]

Thanks,

I just think that grand aggregations below the table is a throwback to the days of paper ledgers, adding machine and pencil.

We can really put these ANYWHERE that makes 'cents'. ;0-)

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[lionelhill]

why did I never think of that?

 

[Gavona]

Skip: Perhaps you could update your faq to include that tip?
faq68-5184

Gavin

 

[SkipVought]

Thanks Gavin, I will!

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}