Great. I'm glad to notice that you are providing helpful and thorough responses to the questions asked by viewers in such a way that all the person asked questions receive the clarity and get information they seek. Indeed, very appreciative approach. ❤
My advice would be to use the VSTACK function to combine the data from the other worksheets into a single source of data. If you need help referencing cells from different worksheets, check out this article. spreadsheetlife.com/how-to-reference-another-sheet/
This is a great tutorial! I'd like to know if there's a way for this search to be used by multiple people at the same time without overwriting each other (viewing each others' results). I.e., I'd like each user to see an instance of the Sheet with only their results showing.
Thank you so much! And what a fascinating question! I don't think there is any easy way to accomplish the task you have described using the method I demonstrated in the video. However, I highly encourage you to check out the Filter Views feature available in Google Sheets. This feature seems like it would fit your situation much better. Check out the link below, and scroll to the section about Filter Views. support.google.com/docs/answer/3540681?hl=en&sjid=218351776827139505-NA#null&zippy= I hope this helps!
@@spreadsheetlife Yes, thanks for the reply. I saw that option, but I was really looking for something that was more like a searchable database (where I wouldn't need to explain the steps to other users). I appreciate the reply! Your videos are awesome because they are so clear (and cover great topics).
Understood. I'm sorry I wasn't able to provide the solution you were looking for. But thank you so much for your kind words, and I'll do my best to continue creating clear and relevant content.
@@spreadsheetlife If people complete a form and only some of the information is for the public to access, I guess I'd have to make a new sheet with only the public information in the cells and create a search button for those cells. Let's say it is a chamber of commerce directory and the public needs access to it. Thanks
Yes, that's one way to do it. If you wanted to make everything a little more automatic, you could write a formula that only captures the public data from the source data. Then setup the search formula on only that data. It's more of an advanced solution, but it is possible.
Hi! This tutorial is really helpful! I am also learning from other people's comments and sample links! I am trying it now and combining it with iferror, ifblank, but I got stuck with a last problem. I want to ask why do spaces show up in between search results? I have multiple sheets (1-4) in my google worksheet with same column headers/values, but different row entry lengths. I used a new sheet (5) as my search page. When I type a name, example Sean, it displays all the Seans from sheets 1-4 but there are spaces in between. What can I do so that the results all show up one after the other, with no spaces? Also, is it possible to search 2 types of data using one search box? Example, I can either type name or id number, then the results will show up
To best answer your questions, send me an email at info@spreadsheetlife.com. And share a copy of your Google Sheets file with me if you can. This will allow me to better answer your questions.
Thank you so much for this video! You are an expert at making complicated things simple for non techy people! Is there any way to not only search multiple columns, but to also make the result be only the date in that row and the one description cell that meets the criteria? Not the entire row.
Thank you so much! That is quite a compliment. You really made my day. So just to clarify your question, you would like to know how to search both the date and description columns, and then return a result that is only the date and the description with no other columns returned. Is that correct?
Ok, got it. This one was tricky, but I have a solution for you. Please see the spreadsheet below. docs.google.com/spreadsheets/d/1lRtIx9BCi3LbyNcyAhaqtSOS8KvfLBl6-csPJsS0RVU/copy The CHOOSECOLS function allows you to select which columns you want from the original data set. Then the second argument of the FILTER function filters by date. And the last argument is setup like I demonstrate in the video to search the description column for the search text. I hope this helps.
Hi! This has been the most comprehensive video I've seen regarding search bar in Google Sheet. I was trying to do the same thing however my data set is on a different tab and I keep getting the mismatched range error. Any idea what could be causing the problem?
This is a great help. Is there anyway of being able to search for a range of values from the same data set? In other words more than one of your descriptions?
Do you mean, is there a way to search for values in multiple columns instead of just one column? If so, see this spreadsheet. The formula looks in both the Description and Notes column. docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
I'm so glad you found this helpful! To answer your question, yes. Take a look at this. It might help. docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
Wrap the formula inside of the IF function. Test if the search bar cell is empty, and return a double quotation mark with no space in between if TRUE. Otherwise, return the rest of the formula. For example, the full formula would look something like this: =IF(C2="","",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18))))
hey! thanks for the explanation (is similar to what i was working on) but i have a question, is it possible to search within the 2 columns? i mean, not just the "description" but also into the "date"? i´ll be really thankful if you can help me with this; you already won a new subscriber! cheers!
Check this out! The formula searches two columns. Let me know if this answers your question! docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
Hello for some reason there is an error in the results saying “filter range must be a single row or a single column” but I followed the same steps that you showed
I see the problem. Your last range B1:H509 is the issue. It can only be one row or column. You can't search multiple columns by writing it this way. So try changing it to H1:H509, or B1:B509 depending on which column you want to search in.
Hi! Thank you for your video. What is the formula if we just want the values to show only when we search it? Because when I follow your instructions, it shows all of the values first then when you search something on the search button that's when the time it will show only the one you typed in.
Take a look at this. I modified the formula to show no results when nothing is typed into the search bar. I hope this helps. docs.google.com/spreadsheets/d/1UBg8LrbM1O8p1MjEam3oLPd3uv69MdarK1zf0eFHHUg/copy
Great video and if you're not busy I would appreciate some help on an issue im having. I am trying to create a search that is position sensitive. What i mean is that if I type into the search "abi" it will only return words that begin with "abi" and not every word that contains those letters. Are you able to show or explain how thats done?
I'm so glad you enjoyed the video! As for your question, check out this spreadsheet right here: docs.google.com/spreadsheets/d/12inF5hkqs3OSKR8CZ9fwl9gcuQLbPARziuqabccW8Oo/copy You can see the differences between the normal search that I teach in the video and a left search where you only get back results that start with the search term. I hope this helps.
Yes, this is possible for sure. Check out this spreadsheet below where I added a conditional formatting rule to highlight the data section. docs.google.com/spreadsheets/d/1_YYutjON1MdqkDCl_dPwUU_6PNpYkaLJUPEVxyS8HZE/copy
Check out this spreadsheet - docs.google.com/spreadsheets/d/11Q9IaXrLm9mDF4_3nkyQcTqOUiOGdWOpYHi1p2RAe3s/copy You will notice that I added a plus sign and then another criteria which in this case checks to see if the date values are equal to a date typed into the search box. Now if you type in a date, the results will be filtered by that date. And if you type in a search term, then you get results with that search term in the description. The plus sign creates an OR search. You either search for a text term in the description, or search for a date in the date column. I hope this helps.
Is there any way to retain the formatting of the results? I'm using this for a listing of videos with links, and it wipes out the links and any text formatting. Thanks!
Unfortunately, using this method will not retain the formatting and links. I'm sorry that is the case. To retain formatting and links, it would be better to select the source data and create a filter by going to Data > Create a Filter. I hope this helps.
Is there a way to hide the source data? I need others to search the file, but not see everyone else's data. (Private) And a big thanks for making this video. Even this noob can follow!
Yes, there are a few things you can do. You can always hide the columns that contain the source data. The formulas will still work. You can also store the source data on a different worksheet tab, and then hide that worksheet. Let me know if either suggestion helps.
@@spreadsheetlife Doesn't work. I can hide the source, but the data still appears under the search bar. I'm trying to hide everything except when someone searches then only their data appears.
Oh, gotcha. I see. So once you hide the data, modify the formula to look similar to this. =IF(ISBLANK(C2),"-",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18)))) This will return a single dash if nothing is entered. You can also see it in action in the spreadsheet linked below. docs.google.com/spreadsheets/d/1HoApRZxgjjO7lPAkEByvnY58ZzA7WVAYBBV_WMhHGes/copy
@@spreadsheetlife You are so kind. I got everything exactly how it should work EXCEPT when I go to publish it by embedding the code, the search function doesn't work. Any ideas? And is there a way I can pay you for your time? I hate taking advantage of you and your obvious expertise.
To count cells with hyperlinks, you will probably need to implement a VBA solution. If you'd like to explore some options, it would be easier for me to help via email. You can message me at info@spreadsheetlife.com.
Unfortunately, it will not work this way. Anything returned by the FILTER function can only be viewed but not edited or deleted. All changes must be made to the source data itself. I'm sorry that's the case.
Hai brother thanks... How to bring more than two spread sheet data into one search bar. And according to updating the data in the sheet, will we get the search result?
See the spreadsheet linked below. The formula searches for the search phrase in the description columns for both datasets. docs.google.com/spreadsheets/d/1lMPwwbI3erYaAQzZk9hb41ZHRr7oqH-pVf9O2GuypeQ/copy
Hi there! To create another search bar, just select a new cell (for your second search bar), and then enter in the same formula as before. The difference this time is that you select the date column (per your example) instead of the name column when specifying the column to search in. Everything else should be the same. I hope this helps to answer your question!
Let me see if I understand your question correctly. Are you asking if you can setup one formula that will search based on the criteria from two different search boxes?
To clarify your question, are you asking how you would pull this off in the case that your source data is scattered across several different worksheets?
Okie dokie! The best way for me to help would be for you to send me an email at info@spreadsheetlife.com. Remind me what your question is, and I can share an example file with you and a brief explanation. 👍
@@spreadsheetlife Hi, thanks for the quick responses! I basically have two columns one with the title of a video and its link, the other column has a bunch of key words. One sheet for the original list, another sheet for the search bar and its results.
Hi i would like to know if i search i get a result, but i will be able to edit the data within the search results i get, because i see you cant edit data within your results you search for. Can you maybe make it edible.
Since the search results are coming from a formula, you cannot edit them directly. The only way to edit them would be via the source data itself. I'm so sorry that's the case.
Is there a way to edit the populated information from your original search and have it be updated back in its original cell? Also, I found your video to be very helpful! Thank you!
I'm so glad you found the video helpful. Unfortunately, you cannot edit the results generated by the FILTER function. You would only be able to make changes to the data via the original source data. I'm sorry that's the case.
@@spreadsheetlife Copy! With your experience do you believe there is a function/command that could be created to enable a feature like that? Also, are you self taught or perhaps took a college course to be so well versed at google sheets?
I'm sure it's a possibility, as spreadsheet technology continues to improve over time. However, I do not thing it would be possible to create a feature like that with what Google Sheets can do right now. Perhaps someone much smarter than me would say differently, but with what I know, I think it's a little out of reach at the moment. As for my learning journey, I am self-taught when it comes to Google Sheets. My skills really started to blossom when I practiced making all sorts of spreadsheets. Task tracker, budget planner, etc. The more I created, the better I got. Practice, practice, practice! Also, my skills continue to improve as I help others learn. After all, as you work hard to make things clear for others, those things become even more clear for you.
Yes. You can use the SORT function to sort data from another worksheet. All Google Sheets formulas and functions can reference and use data from different worksheets.
Can you elaborate the detail please.. it would be really helpfull since i am new to this.. i tried to use the method from countif (used from another sheet) but didnt succeed in this matters
If you'd like, send me an email and I can take a closer look at what you're trying to solve. It would be easier to help via email than through TH-cam comments.
I see. Take a look at this. One search field searches in one column, and the other searches in the other column. I hope this helps. docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
@@spreadsheetlife OK Thanks for update me. This is helpful. But as I want to make a search box like in MS Excel. (Where you can search any thing like *pe And result shows you from table = Pen, Pencil, APE-505, stopped.) It is helpful for if data is big don't know what is exact data.
Ok, gotcha. To reference a range on another sheet, you include the sheet name, an exclamation point, and then the cell or range reference. For instance, look at the reference below. =Sheet1!A1:A10 The above example references A1:A10 on Sheet1. You can also read more about referencing across worksheet here: spreadsheetlife.com/how-to-reference-another-sheet/
Hi there! Could I ask you to please clarify your question? The method shown in this video searches through multiple rows. Are you asking for a method to search through multiple fields. For example, let's say your data had a description, category, and extra notes field. Are you asking for a method to search through all of the fields (columns) instead of just one?
Yes, absolutely. To search in additional fields, you would have to add a plus after the ISNUMBER function, and then repeat the ISNUMBER and SEARCH functions. The difference this time being that you select the new column that you want to search in. I realize that this can be very hard to understand with just words alone. Check out this spreadsheet which demonstrates an example. docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy?usp=sharing
I'm sorry you are getting an error. Sometimes the error is that a semi-colon is used as the argument separator instead of a comma in some regions of the world. Try that, and it may fix it.
Great.
I'm glad to notice that you are providing helpful and thorough responses to the questions asked by viewers in such a way that all the person asked questions receive the clarity and get information they seek.
Indeed, very appreciative approach.
❤
Thank you so much for noticing. I'm not perfect, and it's hard to get to every single comment, but I try my very best to help as much as I can! 😊
Dayummm!! Bro make more of these.... in exactly same simplified way!! too good.
You got it! More videos like this coming in the very near future for sure!!
At last someone explained something. Thank you so much 🙏
Of course! Thank you so much for watching! 😊
Godsend explanation! Thank you for being clear, insightful and objective. Subscribed.
Thank you for the feedback! I'll continue to do my best.
And thank you so much for subscribing! 😊
Thank you for this video clip of yours, this is very helpful, and with the sample link that is very generous of yours
Super happy to help! 😄👍
I love your videos. They are clear and helpful. Thanks for sharing.
Thank you for your kind words. I'm so glad to hear you find my videos clear and helpful! It really makes my day!
How do you connect it from different sheet?
Example the search bar is on 1st sheet and i have 5 sheets that i want to get data from?
My advice would be to use the VSTACK function to combine the data from the other worksheets into a single source of data. If you need help referencing cells from different worksheets, check out this article. spreadsheetlife.com/how-to-reference-another-sheet/
Thank you so much for the video
Of course! Thank you for watching. 😊
Wow.... this is amazing..... Thank you, Sir!...
You're very welcome!
It is so nice so helpfull, Love from India
Thank you for letting me know. I'm so glad that you found this helpful! 😊❤️👍
Thanks
Halp full tutorial.
Need more explanation for accounting and generate reports for construction companies.
If you have specific questions, you are welcome to send me an email and I will answer the best that I can.
thanks bro very useful tricks , ( pro explanation , pro work) ,
🎉new better filter method 🎉 ,
Thanks so much for the feedback! So awesome to hear you've found it useful!!
😄🎉🎊
HELPED A LOT!!
That's so amazing to hear!! 😄
Thank you!
You're very welcome!
thank you..this is so helpful
So glad that you found this helpful!
Great work, you are superb
Thank you so much! So glad you enjoyed the video! 😄👍
This is a great tutorial! I'd like to know if there's a way for this search to be used by multiple people at the same time without overwriting each other (viewing each others' results). I.e., I'd like each user to see an instance of the Sheet with only their results showing.
Thank you so much! And what a fascinating question!
I don't think there is any easy way to accomplish the task you have described using the method I demonstrated in the video. However, I highly encourage you to check out the Filter Views feature available in Google Sheets. This feature seems like it would fit your situation much better. Check out the link below, and scroll to the section about Filter Views. support.google.com/docs/answer/3540681?hl=en&sjid=218351776827139505-NA#null&zippy=
I hope this helps!
@@spreadsheetlife Yes, thanks for the reply. I saw that option, but I was really looking for something that was more like a searchable database (where I wouldn't need to explain the steps to other users). I appreciate the reply! Your videos are awesome because they are so clear (and cover great topics).
Understood. I'm sorry I wasn't able to provide the solution you were looking for. But thank you so much for your kind words, and I'll do my best to continue creating clear and relevant content.
Wonderful. Can you do a video on how to create a searchable directory? Thanks!
That's a pretty awesome idea! Could you give me a few more details of what you mean by searchable directory?
@@spreadsheetlife If people complete a form and only some of the information is for the public to access, I guess I'd have to make a new sheet with only the public information in the cells and create a search button for those cells. Let's say it is a chamber of commerce directory and the public needs access to it. Thanks
Yes, that's one way to do it. If you wanted to make everything a little more automatic, you could write a formula that only captures the public data from the source data. Then setup the search formula on only that data. It's more of an advanced solution, but it is possible.
Hi! This tutorial is really helpful! I am also learning from other people's comments and sample links!
I am trying it now and combining it with iferror, ifblank, but I got stuck with a last problem. I want to ask why do spaces show up in between search results?
I have multiple sheets (1-4) in my google worksheet with same column headers/values, but different row entry lengths. I used a new sheet (5) as my search page. When I type a name, example Sean, it displays all the Seans from sheets 1-4 but there are spaces in between. What can I do so that the results all show up one after the other, with no spaces?
Also, is it possible to search 2 types of data using one search box? Example, I can either type name or id number, then the results will show up
To best answer your questions, send me an email at info@spreadsheetlife.com. And share a copy of your Google Sheets file with me if you can. This will allow me to better answer your questions.
Thank you for sharing
You are very welcome! 😊
Thank you for watching.
Thank you so much for this video! You are an expert at making complicated things simple for non techy people! Is there any way to not only search multiple columns, but to also make the result be only the date in that row and the one description cell that meets the criteria? Not the entire row.
Thank you so much! That is quite a compliment. You really made my day.
So just to clarify your question, you would like to know how to search both the date and description columns, and then return a result that is only the date and the description with no other columns returned. Is that correct?
Yes, you are correct. I have tried repeating part of the function with the "+", but can't quite seam to make the formula functional.
Ok, got it. This one was tricky, but I have a solution for you. Please see the spreadsheet below.
docs.google.com/spreadsheets/d/1lRtIx9BCi3LbyNcyAhaqtSOS8KvfLBl6-csPJsS0RVU/copy
The CHOOSECOLS function allows you to select which columns you want from the original data set. Then the second argument of the FILTER function filters by date. And the last argument is setup like I demonstrate in the video to search the description column for the search text.
I hope this helps.
Hi! This has been the most comprehensive video I've seen regarding search bar in Google Sheet. I was trying to do the same thing however my data set is on a different tab and I keep getting the mismatched range error. Any idea what could be causing the problem?
It's hard to diagnose the problem without context. You are welcome to email me screenshots or share the file. info@spreadsheetlife.com
Nice video! I was using drop downs for the non-search field column, but looks like drop downs do not show.
Thank you!
I'm a little confused about your question. Could you please clarify?
Thank you very much...
You're very welcome! 😊👍
This is a great help. Is there anyway of being able to search for a range of values from the same data set? In other words more than one of your descriptions?
Do you mean, is there a way to search for values in multiple columns instead of just one column? If so, see this spreadsheet. The formula looks in both the Description and Notes column.
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
@@spreadsheetlife I was not the one to ask the question, but this is exactly what I was looking for, thank you!
Awesome! I'm so glad that this was what you were looking for!
Hi, thanks for this! Really helps a lot. Just one question, is there a formula if i want to add more columns to filter? Thanks in advance!
I'm so glad you found this helpful! To answer your question, yes. Take a look at this. It might help.
docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
It works!! Thanks a lot!
Awesome! I'm so glad it worked!
If I wanted to have nothing display when the search box is empty, how would I do that?
Wrap the formula inside of the IF function. Test if the search bar cell is empty, and return a double quotation mark with no space in between if TRUE. Otherwise, return the rest of the formula. For example, the full formula would look something like this:
=IF(C2="","",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18))))
thank you very much
So glad this could help!! 😊👍
i love u thank u so much
You're very welcome! 😄👍
hey! thanks for the explanation (is similar to what i was working on) but i have a question, is it possible to search within the 2 columns? i mean, not just the "description" but also into the "date"?
i´ll be really thankful if you can help me with this; you already won a new subscriber! cheers!
Check this out! The formula searches two columns. Let me know if this answers your question!
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy
@@spreadsheetlife it actually does, thank you very much! (I ended up asking to chatgpt and it was basically the same idea), so thanks for your time :)
Happy to help! 😄👍
Hello for some reason there is an error in the results saying “filter range must be a single row or a single column” but I followed the same steps that you showed
How strange. Can you post your formula here so I can take a look at it for you?
@@spreadsheetlife = filter(A1:H509,isnumber(search(N2,B1:H509)))
I see the problem. Your last range B1:H509 is the issue. It can only be one row or column. You can't search multiple columns by writing it this way. So try changing it to H1:H509, or B1:B509 depending on which column you want to search in.
Thank you
You're welcome!! 😊
Hi! Thank you for your video. What is the formula if we just want the values to show only when we search it? Because when I follow your instructions, it shows all of the values first then when you search something on the search button that's when the time it will show only the one you typed in.
Just to make sure I understand, are you asking how to show no results when nothing is typed into the search bar?
Yes, that is correct.@@spreadsheetlife
Take a look at this. I modified the formula to show no results when nothing is typed into the search bar. I hope this helps.
docs.google.com/spreadsheets/d/1UBg8LrbM1O8p1MjEam3oLPd3uv69MdarK1zf0eFHHUg/copy
@@spreadsheetlife thank you for this, Sir. such a big help
Great video and if you're not busy I would appreciate some help on an issue im having. I am trying to create a search that is position sensitive. What i mean is that if I type into the search "abi" it will only return words that begin with "abi" and not every word that contains those letters. Are you able to show or explain how thats done?
I'm so glad you enjoyed the video!
As for your question, check out this spreadsheet right here: docs.google.com/spreadsheets/d/12inF5hkqs3OSKR8CZ9fwl9gcuQLbPARziuqabccW8Oo/copy
You can see the differences between the normal search that I teach in the video and a left search where you only get back results that start with the search term.
I hope this helps.
@@spreadsheetlife great thank you, i didnt notice it. that helps a lot.
Awesome!! Super glad this helps! 😄👍
Is it possible to have the searched data highlighted in the data section it was pulled from
Yes, this is possible for sure. Check out this spreadsheet below where I added a conditional formatting rule to highlight the data section.
docs.google.com/spreadsheets/d/1_YYutjON1MdqkDCl_dPwUU_6PNpYkaLJUPEVxyS8HZE/copy
Is it possible, to make it apply any changes made in the search field onto the source data?
Unfortunately that is not possible with the method shown in this video.
Hi can I use "&" to search more column not just the "Description". Example, I want to search the date
Check out this spreadsheet - docs.google.com/spreadsheets/d/11Q9IaXrLm9mDF4_3nkyQcTqOUiOGdWOpYHi1p2RAe3s/copy
You will notice that I added a plus sign and then another criteria which in this case checks to see if the date values are equal to a date typed into the search box. Now if you type in a date, the results will be filtered by that date. And if you type in a search term, then you get results with that search term in the description. The plus sign creates an OR search. You either search for a text term in the description, or search for a date in the date column.
I hope this helps.
Is there any way to retain the formatting of the results? I'm using this for a listing of videos with links, and it wipes out the links and any text formatting. Thanks!
Unfortunately, using this method will not retain the formatting and links. I'm sorry that is the case. To retain formatting and links, it would be better to select the source data and create a filter by going to Data > Create a Filter. I hope this helps.
Is there a way to hide the source data? I need others to search the file, but not see everyone else's data. (Private) And a big thanks for making this video. Even this noob can follow!
Yes, there are a few things you can do.
You can always hide the columns that contain the source data. The formulas will still work.
You can also store the source data on a different worksheet tab, and then hide that worksheet. Let me know if either suggestion helps.
@@spreadsheetlife Doesn't work. I can hide the source, but the data still appears under the search bar. I'm trying to hide everything except when someone searches then only their data appears.
Oh, gotcha. I see. So once you hide the data, modify the formula to look similar to this. =IF(ISBLANK(C2),"-",FILTER(F5:G18,ISNUMBER(SEARCH(C2,G5:G18))))
This will return a single dash if nothing is entered. You can also see it in action in the spreadsheet linked below.
docs.google.com/spreadsheets/d/1HoApRZxgjjO7lPAkEByvnY58ZzA7WVAYBBV_WMhHGes/copy
@@spreadsheetlife You are so kind. I got everything exactly how it should work EXCEPT when I go to publish it by embedding the code, the search function doesn't work. Any ideas? And is there a way I can pay you for your time? I hate taking advantage of you and your obvious expertise.
I got your email and sent a response from there! 😄👍
Hi! Id like to know is there any way to count how many links are in the column? Thanks!
For example, i named a cell "Present" with a link inside. Can i count that?
To count cells with hyperlinks, you will probably need to implement a VBA solution. If you'd like to explore some options, it would be easier for me to help via email. You can message me at info@spreadsheetlife.com.
is it possible to do this with the exact same function but be able to delete data from the search results?
Unfortunately, it will not work this way. Anything returned by the FILTER function can only be viewed but not edited or deleted. All changes must be made to the source data itself. I'm sorry that's the case.
Hai brother thanks... How to bring more than two spread sheet data into one search bar. And according to updating the data in the sheet, will we get the search result?
See the spreadsheet linked below. The formula searches for the search phrase in the description columns for both datasets.
docs.google.com/spreadsheets/d/1lMPwwbI3erYaAQzZk9hb41ZHRr7oqH-pVf9O2GuypeQ/copy
@@spreadsheetlifeunable to open the spreadsheet
Try opening the link on a desktop or laptop computer and not a mobile device. That might be the issue. Hope this helps!
Is there a way to edit the source data from the search results?
Unfortunately, you cannot do so. The only way to edit the data is by changing the source data itself. I'm sorry that's the case.
Probably missing something, but i get an error msg saying I am missing one or more open parentheses when I use it
Gotcha. You may have missed a parentheses when typing. You are also welcome to email me, and I can take a look at if for you.
Hi, how do I make multiple search boxes in the same sheet? For example I want to search it by date instead of by name. Thanks!
Hi there!
To create another search bar, just select a new cell (for your second search bar), and then enter in the same formula as before. The difference this time is that you select the date column (per your example) instead of the name column when specifying the column to search in. Everything else should be the same.
I hope this helps to answer your question!
@@spreadsheetlife so it would be possible to search, on the same cell, for both results? or i should have it separately?
Let me see if I understand your question correctly. Are you asking if you can setup one formula that will search based on the criteria from two different search boxes?
How would you do this is the search was a sheet itself and you multiple other sheets with data?
To clarify your question, are you asking how you would pull this off in the case that your source data is scattered across several different worksheets?
@@spreadsheetlifeyes, I would like to know how to set that up. Source data would be in multiple tabs within the same Google Sheet.
Okie dokie! The best way for me to help would be for you to send me an email at info@spreadsheetlife.com. Remind me what your question is, and I can share an example file with you and a brief explanation. 👍
...also, is there any way to have it search more than one column?
Yes there is. What kind of search would you be after? Would the search term have to be present in both columns, or in either column?
@@spreadsheetlife Hi, thanks for the quick responses! I basically have two columns one with the title of a video and its link, the other column has a bunch of key words. One sheet for the original list, another sheet for the search bar and its results.
I see. So you'd like to search in both columns and return any records where the search term is found in either column. Is that correct?
Is this gonna work on Android tablet or android phone?
Google Sheets should function the same way regardless of what device it is used on. So the answer to your question is yes. 😊👍
Can we change the data in the filter?
I'm so sorry, but I'm a little confused about your question. Could you try and clarify it for me?
Can you help me, my seaech result only show where the function, it doesnt show the whole line
You are welcome to share your spreadsheet with me via email, and I can take a look at it.
spreadsheetlife.com/contact/
Hi i would like to know if i search i get a result, but i will be able to edit the data within the search results i get, because i see you cant edit data within your results you search for. Can you maybe make it edible.
Since the search results are coming from a formula, you cannot edit them directly. The only way to edit them would be via the source data itself. I'm so sorry that's the case.
@@spreadsheetlife thank you
You're welcome! Sorry I didn't have a better answer for you.
Is there a way to edit the populated information from your original search and have it be updated back in its original cell?
Also, I found your video to be very helpful! Thank you!
I'm so glad you found the video helpful.
Unfortunately, you cannot edit the results generated by the FILTER function. You would only be able to make changes to the data via the original source data. I'm sorry that's the case.
@@spreadsheetlife Copy! With your experience do you believe there is a function/command that could be created to enable a feature like that? Also, are you self taught or perhaps took a college course to be so well versed at google sheets?
I'm sure it's a possibility, as spreadsheet technology continues to improve over time. However, I do not thing it would be possible to create a feature like that with what Google Sheets can do right now. Perhaps someone much smarter than me would say differently, but with what I know, I think it's a little out of reach at the moment.
As for my learning journey, I am self-taught when it comes to Google Sheets. My skills really started to blossom when I practiced making all sorts of spreadsheets. Task tracker, budget planner, etc. The more I created, the better I got. Practice, practice, practice! Also, my skills continue to improve as I help others learn. After all, as you work hard to make things clear for others, those things become even more clear for you.
@@spreadsheetlife Well said! Thank you for your input! I've also emailed you on a question regarding my spreadsheet. Thank you!
Awesome. I got your email and will be following up on it ASAP.
Can we sort the data from another sheet (sheet1, sheet2)?
Yes. You can use the SORT function to sort data from another worksheet. All Google Sheets formulas and functions can reference and use data from different worksheets.
Can you elaborate the detail please.. it would be really helpfull since i am new to this.. i tried to use the method from countif (used from another sheet) but didnt succeed in this matters
If you'd like, send me an email and I can take a closer look at what you're trying to solve. It would be easier to help via email than through TH-cam comments.
@@spreadsheetlifethank you for the help, i really appriciate it. May i know the email for further discussion?
Yes, it is: info@spreadsheetlife.com
If have search anything from source data then how to search? (What should take search range)? (I little confusing)
Are you asking how to search in more than one column of the data? Or are you asking something else?
@@spreadsheetlife yes, more than one column. (If on column text and one column no.)
I see. Take a look at this. One search field searches in one column, and the other searches in the other column. I hope this helps.
docs.google.com/spreadsheets/d/166QpQrdxqtMFU9KjzE0h8_GTpEJxB8C6afZzOeHjmJ4/copy
@@spreadsheetlife OK Thanks for update me.
This is helpful.
But as I want to make a search box like in MS Excel. (Where you can search any thing like *pe
And result shows you from table = Pen, Pencil, APE-505, stopped.)
It is helpful for if data is big don't know what is exact data.
@@spreadsheetlifein MS using helpnof Create a Table. (Ctrl+T)
How do you do this and have the search on a separate sheet?
Just to clarify, are you asking how to reference the source data if it were on a different worksheet tab or in another spreadsheet file?
@@spreadsheetlife yes, for example I have a list on sheet 1 but have my search bar on sheet 2
Ok, gotcha. To reference a range on another sheet, you include the sheet name, an exclamation point, and then the cell or range reference. For instance, look at the reference below.
=Sheet1!A1:A10
The above example references A1:A10 on Sheet1.
You can also read more about referencing across worksheet here: spreadsheetlife.com/how-to-reference-another-sheet/
Bro can you help me?
How to search between 2 or more row?
Hi there! Could I ask you to please clarify your question? The method shown in this video searches through multiple rows.
Are you asking for a method to search through multiple fields. For example, let's say your data had a description, category, and extra notes field. Are you asking for a method to search through all of the fields (columns) instead of just one?
@@spreadsheetlife Hi, i was wondering if you have any methods for the situation you just described?
Yes, absolutely. To search in additional fields, you would have to add a plus after the ISNUMBER function, and then repeat the ISNUMBER and SEARCH functions. The difference this time being that you select the new column that you want to search in. I realize that this can be very hard to understand with just words alone.
Check out this spreadsheet which demonstrates an example.
docs.google.com/spreadsheets/d/1pcWrAAWCFfmsHUdxzLD5GQJrm_FwsMXCpQKhRjNodAY/copy?usp=sharing
why is it not working on my end?
It's hard to say without context. It's probably a syntax error with your formula. What does your formula look like?
How to make it to a dropdown
To create a dropdown, you would have to implement data validation.
this doesnt work for me
Double check your setup and formula. If you are still having issues, let me know some more details and I might be able to help!
#ERROR! Even if I copypaste the the exact same formula...
I'm sorry you are getting an error. Sometimes the error is that a semi-colon is used as the argument separator instead of a comma in some regions of the world. Try that, and it may fix it.
You saved me ! Thanks !
Awesome! So glad that worked for you!
Nope, error.
Try taking a look at the practice file. It might help.
Thank you!
😊👍