MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Status bar changes upon filtering


Posted by Mike on October 03, 2001 10:28 AM

In creating reports I quite often use the autofilter function. Sometimes when I filter a list, the status bar shows the number of items found, out of the total number searched (14 of 23 found), but sometimes it just say, Filter Mode.
Can anyone tell me how to change this from one to the other?


Posted by Mark W. on October 03, 2001 11:10 AM

It's been my experience that when your list contains
formulas (especially volatile ones) that you can
lose the count. I find that Excel's AutoCalculate
feature can be used quite effectively as a
"work around" for this problem. Just right
mouse click on the Status Bar, choose "Count" and
select the cells in one your list columns (excluding
the header row) prior to the application of an
AutoFilter.

Posted by Mike on October 03, 2001 12:06 PM

Thanks, that's what I've been doing for a workaround. I was hoping that someone had found a way to change it back. When you have a lot of items to filter and report, it's rather time consuming to continually go back and hilight columns.

Posted by Mark W. on October 03, 2001 12:46 PM

Yeah, it can be a hassle... fortunately, a Defined
Name can be created to minimize the hassle.
Just select a cell in your list (I'll assume that
it's A2), choose the Insert | Name | Define menu
command, enter a name (I'll use "Data_Range") into
the "Names in workbook" field, and the reference,
=Sheet1!A$2:A$65536,in the "Refers to" field, and
press [ OK ]. Now with any cell in your list
selected you can choose "Data_Range" from the Name
Box located on the left-hand side of the Formula
Bar. Or, if you prefer, you can choose "Data_Range"
from the Go To list using Control+G.