Auto Count for Auto Filter Doesn't Function under certain circumst

[Guest]

If you use Auto Filter on a data table and then select a point of data, in
the lower left hand corner it will return "X of Y records found"

However, if on another sheet in the same workbook (or another workbook) you
have references to the data in that table, Excel will no longer return the "X
of Y records Found" when you select a point of data.

Is there a way around this?

For example on Sheet1 you have the following simple table:

Person type age
Jon a 23
Jon a 12
Bill a 23
Bill a 34
Tom d 45
Jon d 34
Tom d 23
Bill d 12
Jon s 18
Jon s 23
Tom s 34
Tom e 45
Tom s 56
Bill e 45
Dave e 34
Dave e 23
Bill d 23
Dave d 13

If you use auto filter and then select, for example, Type D the lower left
hand corner of the Excel window will show "6 of 18 records found".

If I then create a duplicate table on another sheet by saying, in cell A1
"=Sheet1!a1"... then on Sheet1 if I select Type D, it will just say "ready".

As some background, the reason I have to create Sheet2 is because I'm using
a spreadsheet that gets created from a download of a web database that has
several megs of data in columns A through CZ (a lot of columns) with many of
those columns combined under a single header (i.e. "Comment on why it is
open" will cover Columns U-W. You cannot make a pivot table when your header
rows are joined across multiple columns like that...

I know the short answer is get a better web database tool, but unfortnately
I don't have that option!

Thanks!

Jonathan

 

[Guest]

going to answer my own question...

for a workaround check out:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q213886

I found it after posting the question.

Jonathan

 

[Peo Sjoblom]

You can also use a function

=SUBTOTAL(3,A3:A500)&" of "&COUNTA(A3:A500)&" Records Found"