Forum Discussion
Find cells hidden by filter
Not top clear what you mean with Filter: FILTER function, Filter functionnality on the Home tab, Filter functionnality on a Table??? Assuming Table...
Unfiltered:
Filtered:
in G2:
=XMATCH("*" & E2 & "*", Table1[Name], 2)
You assumed right (though I grant you the language is becoming increasingly ambiguous given the multitude of similarly named features). I did not write Table because you don't need a table to use Data/Filter 😉 (Perhaps Data/Filter helps to disambiguate.) And I am not trying to use any Excel functions; I am just trying to Home/Find an item of data (which I know exists but is hidden by the current settings of the Data/Filter). But Find won't find it (until I clear the Data/Filter, when suddenly - hey presto)...
PS: I am doing this all from VBA and a work-around I use until I hear of anything better is to instruct "aSheet.ShowAllData" (which is the equivalent of Data/Filter/Clear). It cures the problem with Find at the expense of resetting all Filters.
You assumed right... On the other hand I wrongly assumed re. the FIND function
Given that the row where JONES exists - with my example - is hidden it seems logical to me that Excel tells you it doesn't find it. However, if you do Find All you get the info:
I am puzzled. How do you even get your dialogue box to look like this? Which Excel is that? Mine looks like this:
The 500 exist in the line filtered away but clicking Find All does not make the slightest difference. (My Excel is trying to show hits by putting the cursor there, not by listing them in the pop-up.)
I run 365
I'm puzzeled too but for another reason...
Formatted as Table:
If I filter the table to show a only and do a Find All jones, no problem I get:
Formatted as Table:
If I filter the table to show a only and do Find All 500, no problem too
However, with the same setup and the table filtered to show a only, if I select the whole column A and do a Find All 500 (or Jones with 1st table) ==> NOT FOUND
Also, if instead of formatting as Table I activate the Home/Filter functionnality, filter the column to show a only and do a Find All (w/o selecting the column) ==> NOT FOUND too