I’m hoping somebody has a quick solution for me. I have a fantasy sport league setup on a google sheet. One of the sheets allows other people to search a database of players. Its broken down into four different search boxs offering the following search options: By Player, Position, Owner or Player designation. I’m using the filter function:
=filter(Sheet242!A2:E,search(B6,Sheet242!A2:A),search(E6,Sheet242!E2:E),search(B8,Sheet242!C2:C),search(E8,Sheet242!B2:B))
It draws from a table on a separate sheet.
This works great with one exception. It returns all the values in my table when all 4 search boxes have no value. I want it to filter no results if all 4 boxes are empty.
Here is a link to my google sheet so you can have a better visual of what im trying to do:
https://docs.google.com/spreadsheets/d/1XlfWyDOi-cEynVCcnWEpbOF7qIm_HlAKk2yG31rYvJw/edit?usp=sharing
The two sheets in question are "Player Database" and "Sheet 242".
Any suggestions would be greatly appreciated!
>Solution :
try:
=IF(LEN(B6&B8&E6&E8)=0,, FILTER(Sheet242!A2:E,
SEARCH(B6, Sheet242!A2:A), SEARCH(E6, Sheet242!E2:E),
SEARCH(B8, Sheet242!C2:C), SEARCH(E8, Sheet242!B2:B)))
