Filter a table with wildcards based on a different cell not within the table

[keltzjd]

Good morning! I'm trying to create a scenario where I can filter my table based on a data-validated cell that is not within the table. Is there a way to do this? See attached for sample dataset. Thank you all!

[#2]

@keltzjd 

In the attached file you can select the name which should be excluded and then click the button in cell E2 to start the macro. With Office365 or 2021 you can use FILTER function as well.

[#3]

How do you achieve it with the filter function? I'd like to stay away from macros just because this will be uploaded to a SharePoint and SP doesn't like macro-enabled workbooks. Thank you!

[#4 · Accepted solution]

@keltzjd 

=FILTER($A$7:$F$13,NOT(ISNUMBER(SEARCH($A$3,$F$7:$F$13,1))))

This formula works in my Excel online spreadsheet.

[#5]

Ahh, it creates a new table, I see. Is there any way to have the cell referenced filter the current table? I have a gantt chart connected to the data so the end goal is to have the name filter the table which shows a gantt for that person.

[#6]

@keltzjd 

Unfortunately i can't suggest a solution to filter the current table according to the selection of the dropdown cell (without creating a new table). But i wonder if it's possible to link the gantt chart to the new filtered table.