Autofilter Displays Sums

[boborta]

I use Excel 2000 to display data extracted from DB2 via SAS.

Worksheets use the Autofilter feature on all 45 columns and about 20 of
the columns are summed at the bottom. There are also 4 hidden rows
below the 'main' data, which is used in the QA process.

How the data is updated (replaced) once every three months:
Data is extracted from DB2 and a delimited text file is generated - the
# of rows is noted.
The target Excel file is opened and rows are counted. If the row
counts are the same, the rows in the target file are selected and the
'Del' key is pressed - this retains all the formatting of the columns.
If there are more, rows are added in the 'middle' of the data. If
there are fewer rows to add, we delete the rows. I failed to mention
that I am locked into this process...
We take up at the post 'Del' keystroke now: The text file is opened
w/Excel . Then, the data from the open text file is pasted into the
target Excel file. Normally, no problems are encountered with the
autofilter feature at this point - meaning that if a column is
filtered, considers neither the summary row nor the hidden rows - this
is a good thing. However, I have one Excel file that is misbehaving.
It is 'considering' the summary rows and hidden rows when a filter is
applied. It is obvious by listing 'blank' as one of the sorting
options.

Thanks for looking.
Bob

 

[KC Rippstein]

Bob,
I had a similar situation and found that just inserting a blank row directly
above the subtotals did the trick. I made it 4 points high to keep it of
minimal size, then I turned the autofilter off and back on again. It
correctly went to the blank line and stopped, leaving my totals row alone.
KC

 

[boborta]

After consulting with others I found another answer that will always
work. Rather than using =SUM(S14:S8696), use =SUBTOTAL(9,S14:S8696).
The sum cell will reflect the sum of the filtered rows.

Thank you for your input.

Bob