One other possible VBA approach:

Again in the ThisWorkbook module code:

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)

Then test whether Sh is the summary sheet. If not, then exit sub. If it
is, then refresh the autofilter.

That sounds workable with less overhead than the other VBA methods I came up
with earlier.


--
Return email address is not as DEEP as it appears
"Jack Schitt" wrote in message
...
"Dennis Allen" wrote in message
...

"Jack Schitt" wrote in message
...
It is probably not quite what you are looking for, but a "simple"
solution
might be to reserve a column in your existing summary sheet to return on
each row either TRUE or FALSE depending on whether the count of "Y" in
column A of the respective sheet dedictated to that row in the summary
is
greater than 0. Then autofilter on that column.


Hi. I tried autofilter and it works, but when someone adds a "Y" entry
it does not show up on the summary sheet. To get it to show you have to
rerun autofilter. Isn't there something we could use that would be more
dynamic?


I expect that someone else will be able to produce something better. A
kludgy, less than optimal answer would be to use conditional formatting in
the summary sheet that colour-codes the entire row depending on whether
TRUE or FALSE appears in the "count of Y" column in the summary sheet.
This doesn't hide the row, unfortunately, but it gets you nearly there.

It will be possible by way of VBA, I am su

In the code behind ThisWorkbook module create a routine:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)

then test whether Sh is one of the 400 Sheets, whether Target is in Column
A, whether Y has been entered and then set the rowheight of the
corresponding row in the summary sheet to zero.

Or even, use that routine to refresh the autofilter on the summary sheet,
without even bothering to test Sh and Target.

Unless someone else comes up with an improvement I think I would probably
live with manually refreshing the autofilter, possibly combined with
conditional formatting the row colour so that at least it is obvious to
refresh.

Sorry, but I am really only into the simple stuff :-(

--
Return email address is not as DEEP as it appears