SOLVED

Restrict spill range when TextBox is empty using FILTER, ISNUMBER, SEARCH?

%3CLINGO-SUB%20id%3D%22%5C%26quot%3Blingo-sub-3171964%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3ERestrict%20spill%20range%20when%20TextBox%20is%20empty%20using%20FILTER%2C%20ISNUMBER%2C%20SEARCH%3F%26lt%3B%5C%2Flingo-sub%26gt%3B%3CLINGO-BODY%20id%3D%22%5C%26quot%3Blingo-body-3171964%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CP%3EI%20am%20using%20a%20FILTER%2C%20ISNUMBER%2C%20SEARCH%20formula%20that%20is%20linked%20to%20a%20TextBox%20I%20am%20using%20to%20search%20the%20advTable%20of%20data.%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22%5C%26quot%3Blia-inline-image-display-wrapper%22%20lia-image-align-inline%3D%22%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Fgxcuf89792%2F%5C%26quot%3Bhttps%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F348628i27AD321AB203D526%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%5C%26quot%3B%22%20role%3D%22%5C%26quot%3Bbutton%5C%26quot%3B%22%20title%3D%22exceltw_0-1644997597626.png%22%20alt%3D%22%5C%26quot%3Bexceltw_0-1644997597626.png%5C%26quot%3B%22%20%2F%3E%26lt%3B%5C%2Fspan%26gt%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EWhen%20the%20search%20box%20is%20empty%20the%20entire%20data%20table%20spills%20out%20of%20Cell%20B6%20(which%20is%20where%20the%20formula%20is%20placed).%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3EIs%20it%20possible%20to%20hide%20the%20data%20that%20is%20spilling%20out%20of%20cell%20B6%20while%20the%20search%20box%20is%20blank%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3ESo%20that%20no%20data%20begins%20spilling%20into%20the%20search%20columns%20until%20the%20user%20begins%20typing%20into%20the%20search%20box%3F%20Any%20advice%3F%20Tips%20and%20Tricks%3F%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%26lt%3B%5C%2FP%26gt%3B%26lt%3B%5C%2Flingo-body%26gt%3B%3CLINGO-LABS%20id%3D%22%5C%26quot%3Blingo-labs-3171964%5C%26quot%3B%22%20slang%3D%22%5C%26quot%3Ben-US%5C%26quot%3B%22%3E%3CLINGO-LABEL%3EDeveloper%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EExcel%26lt%3B%5C%2Flingo-label%26gt%3B%3CLINGO-LABEL%3EFormulas%20and%20Functions%26lt%3B%5C%2Flingo-label%26gt%3B%26lt%3B%5C%2Flingo-labs%26gt%3B%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3C%2FLINGO-SUB%3E
Occasional Contributor

I am using a FILTER, ISNUMBER, SEARCH formula that is linked to a TextBox I am using to search the advTable of data.

 

exceltw_0-1644997597626.png

 

When the search box is empty the entire data table spills out of Cell B6 (which is where the formula is placed).

 

Is it possible to hide the data that is spilling out of cell B6 while the search box is blank?

So that no data begins spilling into the search columns until the user begins typing into the search box? Any advice? Tips and Tricks?

 

 

 

7 Replies

@exceltw Try this:

=IF(ISBLANK(C2),"",<formula>)

 

where <formula> is what you currently have in B6 excluding the =-sign.

 

Thanks @Riny_van_Eekelen,

 

I tried your formula and the data from the formula in B6 continues to display/spill out.

 

I think this may be because the SearchBox hovering over C2 is linked to cell C2 so the logic of the ISBLANK(C2) formula is returning FALSE when nothing is in the SearchBox.

 

This is what has left me stumped.

 

exceltw_0-1645030528853.png

 

 

best response confirmed by exceltw (Occasional Contributor)
Solution

@exceltw What do you get when you just enter =ISBLANK(C2) is some other cell? And I wonder why you have a text box to begin with. Can't you just enter the search word in C2 directly?

Hey @Riny_van_Eekelen 

 

=ISBLANK(C2) returns FALSE

 

I will try this without a text box and report back with a screenshot.

 

@Riny_van_Eekelen 

 

IT WORKED WITHOUT THE TEXT BOX! I switched the cell from C2 to E2 (no text box) and it worked! THANK YOU!

 

exceltw_0-1645031571733.png

 

@exceltw Great that you could resolve it!