Thx, it works! On the origin table I’m adding some hyperlink, which are not being transposed to the search part once I search for a line. Is there a a workaround for this?
This works great. Couple of things to note 1. Spilling doesn't work in tables, so if you're using a template from excel that has tables in it and you're trying to return more than one column, it will give a #SPIILL error 2. Adding logic to the end to convert the boolean to 0 instead of 1 when the search field is empty ensures that no results are shown when the search field is empty 3. Enclosing the entire thing in an IF statement allows you to show text in the cell with the formula different than what is shown if no results are returned, such as "Please enter search criteria" Just a couple of ideas incase anyone else comes across this while trying to create a search box. Had no idea about the filter function. Good stuff.
Thanks for the nice instruction. I have a question that when I tried to create the search box in different sheet, it does not work. Could you please kindly advise
Hi, great video. I have 1 search bar working against my table, however, my table consists of 21 columns. I want to be able to search from more than one column (happy to add a few additional search bars). Is this possible?
Very Nice! Is there a way to search across multiple columns? In your example, you're searching Representative (B5:B10004) - Is there a way to use the same search bar to look at other columns, like region for example? My goal is to search multiple columns in one search bar.
i have lots of info on my spreadsheets , i struggle to find the searched highlighted box as its just outlined with green lines is there a way to make the whole box green like on google sheets?
I dont understand. Is this for some older excel? I do everything exactly as in the video but mine is giving "theres a problem with this formula" and gives me higlighted text (SEARCH(find_text; within_text; (start_num)) Cant get around this. What I am possible doing wrong?
I dont know what kind of wizardry you do, but for me it gives me an error that says I have "too few arguments for this function". Guess I need to stick to CTRL + F for now as this is too advanced.
not working for me. I have the formula =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:K300)),"None") and it is returning #VALUE. Could you tell me my mistake please?
Found out the formula is incorrect. The second array should just be G5:G300 and not G5:K300. Change yours to =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:G300)),"None")
why doesnt it work for me? just stays on #VALUE! all the time. is it cos the formula only works for 1 column or row for me? ye i have to add every column with a plus and change the array/range part. i add them with a + after )) before ,"none"
It is not working for my version of excel. =ZOEKEN (search in dutch) returns only the exact name when typed completely in the search. Not a numer. Also not able to type in the searchbox. Just in the cel linked to it. The text does end up in the textbox after typing in the linked cel.
Not sure if you are still struggling with the formula, but in Dutch the SEARCH formula is not ZOEKEN, but VIND.SPEC. So the formula should be something like this: =FILTER(A6:J290;ISGETAL(VIND.SPEC(B2;I6:I290));"Geen gegevens"). Bij mij werkt dit wel, ook met gedeeltelijke overeenkomsten in het overzicht.
Thx, it works! On the origin table I’m adding some hyperlink, which are not being transposed to the search part once I search for a line. Is there a a workaround for this?
This works great. Couple of things to note
1. Spilling doesn't work in tables, so if you're using a template from excel that has tables in it and you're trying to return more than one column, it will give a #SPIILL error
2. Adding logic to the end to convert the boolean to 0 instead of 1 when the search field is empty ensures that no results are shown when the search field is empty
3. Enclosing the entire thing in an IF statement allows you to show text in the cell with the formula different than what is shown if no results are returned, such as "Please enter search criteria"
Just a couple of ideas incase anyone else comes across this while trying to create a search box. Had no idea about the filter function. Good stuff.
Presented briefly and full of info. I don't know how you people figure all this out, but THANK YOU! 🤗
Thanks for the nice instruction. I have a question that when I tried to create the search box in different sheet, it does not work. Could you please kindly advise
Hi, great video. I have 1 search bar working against my table, however, my table consists of 21 columns. I want to be able to search from more than one column (happy to add a few additional search bars). Is this possible?
hello, when done creating the search box, can or will you be able to edit the information you have search for ?
please how did you get the search near the textbox you drew out.
This is the formula I needed, thanks! I've tried exactly the same and it worked! So happy with this.
Love your all tutorials! ❤
Very Nice! Is there a way to search across multiple columns? In your example, you're searching Representative (B5:B10004) - Is there a way to use the same search bar to look at other columns, like region for example? My goal is to search multiple columns in one search bar.
ah - I did figure out a way to 'cheat' - I created a column in my source table that concatenates multiple columns. ..works for my purpose anyway :)
May you explain how you were able to. My issue is i have 22 columns with 10,000 rows each and my excel keeps freezing @ramseygr
Copy paste the formula from the isnumber part then add a + and paste it with a new range at the end of ur formula but before the ,”none”)
Came here from thread. Thank you.
Can this be set to look for values on another tab in the same spreadsheet?
I would like to know this as well
But the filter function is not available in non-office365 excel. Is there any other alternative?
i have lots of info on my spreadsheets , i struggle to find the searched highlighted box as its just outlined with green lines is there a way to make the whole box green like on google sheets?
I have 365 and It doesn't work.. I always get a value error.. works without being an insider member? Thanks
I dont understand. Is this for some older excel? I do everything exactly as in the video but mine is giving "theres a problem with this formula" and gives me higlighted text (SEARCH(find_text; within_text; (start_num)) Cant get around this. What I am possible doing wrong?
I dont know what kind of wizardry you do, but for me it gives me an error that says I have "too few arguments for this function". Guess I need to stick to CTRL + F for now as this is too advanced.
thank you my angel 🌹
Would this still work if the original data was on Sheet 2 and the Search was on Sheet 1?
Does this formula work in excel 2010 and 2016?
not working for me. I have the formula =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:K300)),"None") and it is returning #VALUE. Could you tell me my mistake please?
Same
Found out the formula is incorrect. The second array should just be G5:G300 and not G5:K300. Change yours to =FILTER(G5:K300,ISNUMBER(SEARCH(C2,G5:G300)),"None")
@@teachcaldschannel9819 bro thank you so much its been a week trying to figure it out and i just saw this
@@teachcaldschannel9819 Lol, I had exactly the same problem with exactly the same reason. Thank you hahaha
Can you show how to do a regular search box without the extra box to the right. Just a search box on top of a single chart.
tried it and working as magic, thanks for sharing
this is what i want and finaly found tq
Do you have a template I could get hold of.
why doesnt it work for me? just stays on #VALUE! all the time. is it cos the formula only works for 1 column or row for me? ye i have to add every column with a plus and change the array/range part. i add them with a + after )) before ,"none"
It is not working for my version of excel. =ZOEKEN (search in dutch) returns only the exact name when typed completely in the search. Not a numer. Also not able to type in the searchbox. Just in the cel linked to it. The text does end up in the textbox after typing in the linked cel.
Not sure if you are still struggling with the formula, but in Dutch the SEARCH formula is not ZOEKEN, but VIND.SPEC. So the formula should be something like this: =FILTER(A6:J290;ISGETAL(VIND.SPEC(B2;I6:I290));"Geen gegevens"). Bij mij werkt dit wel, ook met gedeeltelijke overeenkomsten in het overzicht.
Anyone know how it working on Excel 2003 ? It prompt me a result #Name?
2:03 H4 became "Search" when it was "representative", what happened there?
Can not write commas in the formulas, only allows semicolons.
how to filter by numbers?
I get spill. Moving to another video, maybe they will think to mention any possible errors and how to tackle them.
Great
Works if you have 365
2:00
Not working for me🥺
Lady you are way to fast...
Crappy tutorial. Doesn’t work.
NOTWORKING
Nope