You are the real MVP. Your tutorial helped me eliminate a ton of duplicated work by collecting the research data of an entire team into one database and using your search formula to prevent duplicated searches. You are a hero in my books, whoever you are.
Wow!!! You are a genius!! Excellent job!! Perfect explanation!! I subscribed in Part 1 already!! Can't wait to look at some more of your tutorials!! Thank you so much!!
Super helpful! I actually have a good test to do alongside the video, however, my data is showing up the "Partial Match" box before I enter anything in the search box. How do I keep it empty until I enter something?
Awsome tutorial. I tried this and it works great. But is it possible to create this in a way that one can edit and update the original tabel in the search results?
Great tutorial! Is there a way to make it scalable so we can search thru a bunch of columns say that we provide in another table? (instead of adding each line for each column)
Well explained and easy to follow. Great stuff. Instead of INDIRECT with a hardcoded table name, I would have used INDEX MATCH (or XMATCH). I would compare the table headers to the selected search columns. Finally I would use FILTER to return the results in those columns and then use FILTER again to return the results of the search box
Great video, thanks for taking the time to make it. I have followed your tutorial with great success. Question: Once you have the results you want, how to you edit a record?
Yeah! Thanks man. I used your lesson to create a tool for medical coders to look up which procedures to code or not to code for a hospital admission. I only needed the multi-column general search (no checkboxes) but I appreciate your methodical building block approach to teaching this. I am an educator myself and so I notice these things. Great job!
Really clever stuff in here and I love the fact you've managed it without any VBA or having to save the file as macro enabled despite using some ActiveX controls 👍
Hi Sir, You did good job, I like it most. Thanks, Now is it possible to create filter like Region US, Canada and Europe all row's data at one go as per our choice. please share link if you have already made a video or please make a video.
Thank you, that was a great tutorial. I was just wondering if the results could be made into a table for further searching? I tried but to make the search results a table but it didn't work. any ideas?
Great video, thank you so much! Everything is working perfectly, but I'm having trouble with the search not displaying images that are associated with each line of data. To take your example, each person would have a photo on a fourth column and we'd like the pictures displayed with the results. Is there a way to handle images in the search formula?
Great tutorial! I'm intending to use this as a guide to create a directory. Is there a way to create a simple interface for the users to easily update, delete, and add new data (contacts in my case)? I suppose that could be done on the data sheet but wondering if there is a way to do so by entering the data in fields (again, for the user). Perhaps using VBA?
I'm trying to to do this exact same thing but on the second dependent search box I need to have it check for multiple matches that won't be necessary in the same order as in the table I'm searching on. Ex (If I type "-AD-AC-CID", I want it to filter by the cell(s) that contains the texts "AD" , "AC" , and "CID", in any order as long as they're all 3 there. Do you have any suggestions?
Great stuff !! I am wondering how to filter using those techniques withing the original table with data, rather than generate a separate place with filtering results.
Thanks, mate! And good question. Unfortunately, this technique using the Filter function will only work with a separate place because you need to have your data somewhere to reference them for the function.
@@theofficelab Maybe to "cheat" Excel you could put your data in for eg. worksheet1 and your search results in worksheet2 - thanks to it you will see something like filtering within "original table" - I have done it like it and it works quite well - but I have other question - how to make that if you do not have anything insert in search box the search result would be empty (in partial match) and only when you start typing (inserting values) search results are showing values.
This is great but i have problem, ORIGINAL DATA cells are in different colors and i need those colors to be filtered also with number values, but i cannot get it in filtered boxes
hi again! I'm wondering if there's a way to make something similar in Google Sheets. I've been trying to replicate this strategy but it's very difficult.
Hi Dani, I am not sure if Google Sheets has a Text Box element like the one available in Excel. But you can build a cell based search box either using the FILTER function or, what I would recommend if you are somewhat familiar with SQL, the QUERY function. Maybe I'll do some Google Sheets tutorials in the future.
How to make a filter based on the text typed in a separate cell that filters the original table rather than creates a separate table with results? If possible, with functions not VB.
Great video! Would anyone happen to know how to create conditional format that would dynamically change depending on the search results? I'm trying to use two worksheets. One with the search, and the other as a maintenance log with green/yellow/red status box indicators. I'd like to have the worksheet with the search use information regarding the maintenance log to conditionally format so that it displays that same style of status indicator. So if an item is on the maintenance log with a ship date but no return date, the status would be yellow. However, I can not get the status to stay with the information when using the search, it stays tied to the cell it initially was in. Any ideas on how to create a formula so that it changes with the search results?
I'm assuming this won't work so good for searching numbers? Like if the data is for order information and you want to search for something like po# or order#. If anyone knows please let me know.
Hi Robert 🙋♂️ Unfortunately, that exact solution is only possible with the FILTER function (available in Excel 365). Workarounds for older versions either include VBA or have some cutbacks in terms of functionality. Sorry for that.
Do not put example files on spam site: "By entering my e-mail address and clicking the "Subscribe" button, I agree to receiving regular newsletters (including commercial offers)."
You are the real MVP. Your tutorial helped me eliminate a ton of duplicated work by collecting the research data of an entire team into one database and using your search formula to prevent duplicated searches. You are a hero in my books, whoever you are.
Click here for Part 1 of this tutorial : ruclips.net/video/gHXalY5rngI/видео.html
This channel is seriously awesome! Thanks for the upload
My pleasure, Abdullah 😊 Thanks for your awesome feedback!
Fantastic, it works brilliantly. Any idea of how to restrict the columns showing in the filter list?
Wow!!! You are a genius!! Excellent job!! Perfect explanation!! I subscribed in Part 1 already!! Can't wait to look at some more of your tutorials!! Thank you so much!!
Super helpful! I actually have a good test to do alongside the video, however, my data is showing up the "Partial Match" box before I enter anything in the search box. How do I keep it empty until I enter something?
Awsome tutorial. I tried this and it works great. But is it possible to create this in a way that one can edit and update the original tabel in the search results?
Amazing tutorial video. This is exactly what I need for a project I am doing now. Thanks a million. Subscribed!!!
Great tutorial! Is there a way to make it scalable so we can search thru a bunch of columns say that we provide in another table? (instead of adding each line for each column)
You are a life savior sir, thank you sooo much!!! Subscribing, no doubt 'bout that
Well explained and easy to follow. Great stuff.
Instead of INDIRECT with a hardcoded table name, I would have used INDEX MATCH (or XMATCH). I would compare the table headers to the selected search columns. Finally I would use FILTER to return the results in those columns and then use FILTER again to return the results of the search box
Perfect tutorial. Thanks.
Very Good Tutorial! Thank You!
Great video, thanks for taking the time to make it. I have followed your tutorial with great success. Question: Once you have the results you want, how to you edit a record?
Great Tutorial, this helped us a lot. Thank you!
Yeah! Thanks man. I used your lesson to create a tool for medical coders to look up which procedures to code or not to code for a hospital admission. I only needed the multi-column general search (no checkboxes) but I appreciate your methodical building block approach to teaching this. I am an educator myself and so I notice these things. Great job!
Excellent tutorial, thank you so much!!
thank you so much!!!! this was very helpful!!!
Thanks 😊 Glad to hear that!
Really clever stuff in here and I love the fact you've managed it without any VBA or having to save the file as macro enabled despite using some ActiveX controls 👍
Hi Sir, You did good job, I like it most. Thanks, Now is it possible to create filter like Region US, Canada and Europe all row's data at one go as per our choice. please share link if you have already made a video or please make a video.
Great tutorial!
Thank you!
Thank you, that was a great tutorial. I was just wondering if the results could be made into a table for further searching? I tried but to make the search results a table but it didn't work. any ideas?
Just amazing! Thanks a lot for the great tutorial!
Great work! Is there any way I can do the same without using Active X Form Controls because they're not allowed in Excel for MAC.
Great video, thank you so much!
Everything is working perfectly, but I'm having trouble with the search not displaying images that are associated with each line of data. To take your example, each person would have a photo on a fourth column and we'd like the pictures displayed with the results. Is there a way to handle images in the search formula?
Once I have this setup. If I wanted to use the data found in the search field to delete from the source table, would I be able to do that?
Great tutorial! I'm intending to use this as a guide to create a directory. Is there a way to create a simple interface for the users to easily update, delete, and add new data (contacts in my case)? I suppose that could be done on the data sheet but wondering if there is a way to do so by entering the data in fields (again, for the user). Perhaps using VBA?
Is there a way to add an additional date filter if the each entry has a date variable?
I'm trying to to do this exact same thing but on the second dependent search box I need to have it check for multiple matches that won't be necessary in the same order as in the table I'm searching on. Ex (If I type "-AD-AC-CID", I want it to filter by the cell(s) that contains the texts "AD" , "AC" , and "CID", in any order as long as they're all 3 there. Do you have any suggestions?
amazing !!!! you're the best. tx much
Great stuff !! I am wondering how to filter using those techniques withing the original table with data, rather than generate a separate place with filtering results.
Thanks, mate!
And good question. Unfortunately, this technique using the Filter function will only work with a separate place because you need to have your data somewhere to reference them for the function.
@@theofficelab Maybe to "cheat" Excel you could put your data in for eg. worksheet1 and your search results in worksheet2 - thanks to it you will see something like filtering within "original table" - I have done it like it and it works quite well - but I have other question - how to make that if you do not have anything insert in search box the search result would be empty (in partial match) and only when you start typing (inserting values) search results are showing values.
@@mateuszwnuk-lipinski8846 You can insert the if function right in front of your function as follows: IF(search_cell=""," ",
Excellent !!
Is it possible to have three search column in one search box?
For example search with name , region and selas ?
Great stuff!
Great video. Very good
Is it possible to add a filter as sales range (eg. filter sales from 5.000-10.000 and 10.000-15.000)
Or maybe for example, filter sales up to 5.000, 10.000...
How do you get it to show blanks? Eg if. You are doing multiple column search and you want it to include cells that include blanks
Is it possible to use this search box for multiple sheets?
ur the best 💟 thx
Is there a way to create this in VBA with a userform with the diff search textboxes?
This is great but i have problem, ORIGINAL DATA cells are in different colors and i need those colors to be filtered also with number values, but i cannot get it in filtered boxes
love it!
Is there anyone out there who know if this is has been done of anyone anywhere in VBA instead (not everyone has 2019/365)
Hi sir, can you please send me real time part one, sorry I can't find it. Many thanks
I can't get the file from your website. Can you please have a look?
👌👏👏 so good
hi again! I'm wondering if there's a way to make something similar in Google Sheets. I've been trying to replicate this strategy but it's very difficult.
Hi Dani, I am not sure if Google Sheets has a Text Box element like the one available in Excel. But you can build a cell based search box either using the FILTER function or, what I would recommend if you are somewhat familiar with SQL, the QUERY function. Maybe I'll do some Google Sheets tutorials in the future.
How to make a filter based on the text typed in a separate cell that filters the original table rather than creates a separate table with results? If possible, with functions not VB.
Great video! Would anyone happen to know how to create conditional format that would dynamically change depending on the search results? I'm trying to use two worksheets. One with the search, and the other as a maintenance log with green/yellow/red status box indicators. I'd like to have the worksheet with the search use information regarding the maintenance log to conditionally format so that it displays that same style of status indicator. So if an item is on the maintenance log with a ship date but no return date, the status would be yellow. However, I can not get the status to stay with the information when using the search, it stays tied to the cell it initially was in. Any ideas on how to create a formula so that it changes with the search results?
I'm assuming this won't work so good for searching numbers? Like if the data is for order information and you want to search for something like po# or order#. If anyone knows please let me know.
how can i do the same search box for multiple sheets ??
sadly I dont have filter function :( Do you have videos anything like this but dont use filter function
Can you help add search multiple sheet
why dont we use Pivot Table insead?
In Excel 2010 there isn't FILTER formula
How can I create this?
Hi Robert 🙋♂️
Unfortunately, that exact solution is only possible with the FILTER function (available in Excel 365). Workarounds for older versions either include VBA or have some cutbacks in terms of functionality. Sorry for that.
does this work for numbers
I doubt that to be honest.
downloaded the excel sheet, does not work at all.
error: #Name?
Which Excel version do you have? The FILTER function is only available in the recent Excel version.
where is filter function ? I couldn't find it in my excel
Make sure u r using the excel from the Office 365
FILTER FUNCTION IS AVAILABLE ONLY FOR EXCEL 365 !!!
Do not put example files on spam site: "By entering my e-mail address and clicking the "Subscribe" button, I agree to receiving regular newsletters (including commercial offers)."