Filters do not work correctly for table linked to an Access query

[DanL]

I have imported Access data (a linked query) into Excel as a table. The
filters do not work correctly for the table. I apply a filter to one of the
columns so that it should only display one of the (text) items in the list.
The first part of the table filters correctly, but at the end of the table
there are still several rows of data that do not match the filter. The row
numbers are blue in the section that filtered correctly but they are the
normal color (black) in the section that did not. Each time I refresh the
table, the number of rows that does not filter correctly (and the row label
is black) increases by one. Very strange, and very anti-productive!

Is there anything I can do to fix this behavior? When I copy and paste the
data (paste special --> values) into a new workbook and turn on filtering, it
filters correctly. So it must not like the Access query for some reason.

 

[DanL]

More information:

I am adding a column to the table with the following formula:
=SUBTOTAL(103,TableName[[#This Row],[ColumnName]]). This formula will
display a 0 if the row is filtered and a 1 if it is not. The bug only occurs
after I add this column to the table (filtering works fine beforehand).

I need this formula because I am linking to this Excel table in Access. The
entire table is shown in Access, but I only want to use the displayed
(non-filtered) rows. So in Access I am adding a criteria that this column be
equal to 1.