You are the Yoda of Excel. Thank you for part 1 and part 2 of this solution. I really appreciate your attention to detail! Your solution works across multiple sheets too, so If you place the lists on a separate sheet, you can have multiple "Searchable Drop Downs" on various sheets all referencing one "Lists" sheet (which you can hide). Brilliant!
This has been a godsend for me in setting up multiple projects (from a list of circa 500+ projects, multiple customers, and multiple resources and aiding calculations of resource utilization and revenue generation that much easier. Thanks Neil, top stuff!
Thank you so much Neil. I created a separate data entry sheet and referenced the validation list on a separate sheet. It was a great help especially that the names to choose from are about a thousand names. Cheers!
Thank you Neil, excellent follow up... I can finish the project now or at least proceed to the next problem (lol). For anyone interested I am using the function to search a list of foods to find their carbohydrate content. I can now enter a food type, ie potato, carrot, chicken etc, and display the total carb content of the meal. I will then use the information to tell me the units of insulin I require to treat my diabetic problem... Thanks once again Neil, for a well made video...
Brilliant. This and your other searchable drop down list worked perfectly, and I modified it so that my database is on my second worksheet and my drop down lists are on the first. Works like a charm!
Great videos, just a quick addition that works for me to solve the empty dropdown if nothing is typed in. Within the data validation box, I use the following IF statement: =IF(D5="",G1:G22,OFFSET($I$2,,,COUNTIF($I:$I,"?*"))) In short, if nothing is typed, the full list is shown, however, if you type then the offset rule is triggered. (I used a named range for the full list for ease)
Thank you so much for putting this together. This info, plus the additional info from part 1, will save me so much time. A quick side note, I started to experience excel not responding shortly after implementing this. But it was do to some of my other sells using index\match of an entire column. When you use reference an entire column ($A:$A) you are asking Excel to look at over a million rows. Keep this in mind if you run into excel not responding.
Wow, what a fantastic result, brilliant. Not only cell reference but dynamic lists is new to me and always given me a headache having to use static named lists - really clever.
Great share Neil - The first video was good and then I needed exactly this to finish off the project. So glad I came across the first video. But then the second, as I thought I was stopped in my tracks again. Just wanted to say thank you :-)
That was brilliant, just answered the question I asked earlier. to get around the no return, what I have done is finished my data with a space, so if I include a space I get my full list Thanks again
I also thought about that but i ended up keeping my data as they are and modifying the formula which starts with if(isnumber(search... to something like if(or(cell("contents")=0,isnumber(search.... So far so good. Hope it wont crash
This was awesome. It really helped with the project I'm working on. I also found, if, in the Data Validation box, you check if the value in the current cell isn't equal to the value in the cell pulling "contents", and if not, have it pull from the full list, it fixes the blank issue For Example, in the Data Validation box of cell D5: =IF($D5$D$2,$G$2#,$I$2#) Now, this can be filled down and if you select any field that doesn't have the same value as the last thing you typed (which is in D2), then it will show the full name list. If, however, you select a name, then go back to the box and pull down the drop-down, you will only see that name. Also, I removed the returning of 0's with =IF(CELL("contents")="","",CELL("contents")), otherwise Null was returning 0
Thanks a lot for your tutorial on this. Helps me a lot.I have multiple drop down list, each one with different reference and the list is long enough to make my excel lagging. Rather than using CELL, i prefer macro.
Hi Neil, this is a very clever trick, thank you for sharing. To solve the last problem (5:50) one may do the following: (1) Select cell A1 (this is important) (2) Add the name "validation_list" and make it refer to the formula =IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101) (3) Change the data validation source to the formula =validation_list Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have: (1) changed the COUNTIF() function to the MAX() function (2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list.
Absolutely Fantastic! Not only the content, but the style in which you teach us how to become better users. One small suggestion is you may want to copy the contents of the original dropdown and then do a special paste into the target range rather than recreate the data validation in the new range.
Just contributing my 2 cents, To limit the search trigger to a specific column and row and to make the whole list visible when no text is entered initially to the dropdown list cells, or when you omit the text in those cells.. =IF(AND(CELL("col")=2, CELL("row")
I was struggling to understand this and get it to work so ended up playing around with the whole sheet. I ended up adding the cell function into the 'if' in column a so =IF(ISNUMBER(SEARCH(CELL("contents"),B2)),MAX($A$1:A1)+1,0).
You have literally made my life a living hell.. no wait.. My boss has you have saved me.. I just want to thank you.. Id almost kiss you if I met you.. :D
hey, the video was great. I really have started admiring you. Hundreds of other videos to achieve the same thing couldn't do it without venturing into VB. But you did it. Brave friend. Now one demand. Couldn't you provide us the same exercise file so that we could practice along with your video. We are not as smart as you are bhai.
If you want to extend this to only trigger the search if the data is entered in a cell from a specific column (even from another sheet) add the following function to the master search cell :=IF(CELL("col")="";CELL(contents);"")Regards,David
i think i got it: =IF(CELL("col",,CELL("contents"),"") i was trying it on the same worksheet but it wasn't working, had to switch worksheet i guess.. also i used a table for mine so it'll auto-add new dropdowns with tab at the end of the table, so mine looks like: =IF(CELL("col",Table2[TEST PARTS]),CELL("contents"),"")
Great video instruction Neil thank you. I have this working across multiple sheets (to populate courses with students names - also fills in additional info in columns from lookup table). My problem is when I protect each worksheet the dropdown doesn't refresh to show the names (only the last cell content entered), if I type part of the name and press enter THEN click the dropdown it works. Anyway to do a refresh on protected sheet?
Dennis - How'd you get it to work on a different sheet? I"m getting an error when trying to make the data validation look at the offset formula from the other sheet. Says I cannot use unions or references in Data Validation formulas.
These two parts have been super helpful! Thank you so much! Question though: Is there a way to either force-select or auto-complete the inputs in Column D? For instance, I want to make sure that the final input is a string that is equal to one of the names listed in Column G--in other words, can we put an error message if the user types Tiger and hits enter--because I want the actual input to be Tiger Woods. (I hope my question is clear).
Great video, it really helped me..thank you One question with Data Validation I need to have validation also working to confirm user has selected from list and it is not user input. Please advice
Hi Neil, Thanks a lot for part one and part two it wors perfectly! I Previously used INDIRECT as part of my drop down list, as the list was dependent on the contents of the cell next to the list. Is there a way of getting these to work together with the search function?
Great it works A1 but I have a scrip that move things around within the workbook and so the Cell("content") function is called every time and really put a toll on Excel. Is it possible to limit the range of the function of Cell("content") to a specific range. For example if there's a change only within A1:A100 ?
So I'm using this on an invoice, and inventory spread sheet. So it should be possible to auto fill a row of input referencing the 1 that is a match as a result of my word search. Thus auto filling the rest of the cells in a row by searching that column for the 1?
It should be relatively straightforward (if you've followed Neil's excellent guide all the way through, it sounds like what you want is far simpler) to look-up values in another table. You would lookup the selected search value, refer to the table where that information is held, and retrieve the relevant column of information. I use the search term to reduce a list of about 1000 items, but i then perform a vlookup to bring in other relevant information from another large data table
Thank you for posting this video (as well as Pt 1), it was very helpful. Is there any way of using this searchable drop-down list while still utilizing an Error Alert function?
Is it possible to select as a fresh start of searchable drop down list in every cells of the column? This method you have to select the empty value to make the value go back to the original which is empty in the D2 cell.
I liked the addition to this video of the =cell("contents") piece as I did want to use the data validation across a range of cells. Is there a way to limit this formula to only changes within a specific tab? Thanks
Great instructions thank you. I have one question that would help speed up data entry: is there a way to show the drop down list without clicking the arrow? Usually Alt-down arrow does this, but it appears blank with your method. When entering hundreds of data entry items, avoiding the mouse and clicking little buttons would be fantastic. I have also seen combo boxes show live search suggestions without clicking the arrow. Any thoughts? Keep up the great work..
After typing your keyword(s) press Ctrl+Enter then Alt+DownArrow. I have also made a suggestion that makes the full list appear if you only press Alt+DownArrow (copied below): " To solve the last problem (5:50) one may do the following: (1) Select cell A1 (this is important) (2) Add the name "validation_list" and make it refer to the formula =IF((!A1"")*(!A1=!$D$2),OFFSET(!$I$2,,,MAX(1,!$F$2:$F$101)),!$G$2:$G$101) (3) Change the data validation source to the formula =validation_list Compared to the previous video (watch?v=vkPoViUhkxU 9:50) I have: (1) changed the COUNTIF() function to the MAX() function (2) added the conditions that if the selected cell is not empty and is equal to the result of the CELL() function, then return the dynamically filtered list, otherwise, return the raw list. "
@@MohamedIbrahim-im5qs Hi I'm interested to find out more about this. But I can't achieve the desired outcome as per mentioned by following your instructions. Can you help me out by elaborating with a step by step guide? Thanks!
Neil, Fantastic videos and ideas. Thank you so much. I am trying to expand the capability of this drop down list into multiple tabs. Is there a way to use just the one list of data, but still be able to see it in the drop downs if I copy the tab? Even better if the user changes the tab's name?
I'd still like to know the answer to this for future reference, but what I did was include my list of data on the same tab as the drop down, so when I copy the tab, the name manager automatically, creates a new name that references the new tab.
Brilliant! Is there a way to keep the functionality after protecting the worksheet? I have locked certain cells and as a result the dropdown search wont work when sheet is protected. Any ideas? Thx!
Hey Thanks for the video.. really helpful. Have got few questions. Is it possible, if yes, how can one format the Input Message tip pop up box, so as to e.g have particual lenght/height? and the position to be flexible as you scroll up or down?
Very good video, I have one question, I am trying to create a register with multiple columns and I would like to use multiple searchable drop down lists, with different values in each list. Have you any ideas how I can go about this?
Yes, but that won't happen if you use the Offset formula in the Data validation source bar (he said he was just being lazy, but should have used that one as in the previous video) - 5:00
Hi Neil, This is brilliant, thanks for recording and putting this onto You Tube. Ive managed to follow everything and get the drop downs to work effectively. One problem I have though.... When I protect the sheet the drop down list stops working even though I have not locket the lookup cell. any ideas? Jason
Hai Neil, your video is great.. thanks you very much. But could you help me, if I used the shortcut key alt+down arrow, it show all the list not just the text I search. Is there any shortcut key for the drop down list?
An interesting point. If you are typing a name and get no matches (and you think there should be one, you might wish you could pull down the complete drop down list like you would with a normal data validation list. You can get this in the searchable list cells by placing an asterisk (*) in the cell and click the down arrow.
Hi Neil, your solution is very interesting thanks. I was wondering if it is possible to oblige an operator to insert in the cell only the content of the searchable drop down list. thanks massimo
+Neil Firth Thank you for these great tutorial videos, they are very practical. My data entry columns are in another sheet and I cannot seem to generate the dynamic list as intended. Are you able to provide a sample file that contains the data used in the video so I can compare please?
Hi Neil. Really good videos. These have helped a lot. Also, the comments too. I like your presentation style but you seem to have stopped youtubing - that's a shame. You mention someone called Tim Perchanski (?). I'd like to visit his website/youtube but can't find him. Have I spelt his name correctly? Can you let me know how to access his site? Thanks.
Thanks. This video tutorial is great, it solved my problem. But how to limit or auto-update the search list (in your case in column "I" ). For example if you search for "Pe" the drop down list will show all results with names having "Pe" and the the end blank spaces. Those blanks occurs due to different results count (search results may be 5 , 2, 9 or any count depending on search criteria and search data}.
I managed to address the same issue by making the validation look at an offset function. The offset function starts at the first cell the search results, and the height is specified by referring to another cell which counts the number of search results (Countif(range,"?*") - so if the search returns 10 things, the pick list is only 10 things. I hope you can follow that!
Dear Neil, your video is very helpfull but for my drop down list is confusing. I have drop down list with categories, next to this list i have another list that is connected by the formula =indirect, and i need this to search in those items of that category. Thank you !!!
I can't seem to get more than the first drop down cell to function correctly. It seems as though the additional cells in the rows below (D3 and below) are also using the absolute reference to D2 in the search formula from the Part 1 Video, but when I change it to a relative reference for the rows in the Search() formula, that doesn't work either.
Hi, this little series really helped me, thanks so much! Is there a way I can split this over two sheets? So, for example, I have everything but the drop-down on sheet 2 "pick lists" and the drop down in sheet 1 "data"?
+Courtney H In response to my own question (in case anybody else is wondering) I didn't want to do all of this on the data sheet and then have to hide it, so, instead, create a named range referring to that offset formula, but use external references within that formula. Not sure if this is the proper way but it worked for me. Eg. I now have a named range on sheet "Data" called "validation_list_sheets" which refers to: =OFFSET('Pick Lists'!$B$3,,,COUNTIF('Pick Lists'!$B$3:'Pick Lists'!$B:L,"?*"),) hope this helps somebody :)
Hi Neil, I tried your dropdown because I have a very large table over 2000 lines and each line has a dropdown, and the list for the dropdown also has over 500 customers listed. The way you proposed the searchable dropdown works but it is very temperamental It will work then knowing the data is in the dropdown list data, the company name does not populate the dropdown. Not sure if it is because I am using Office 365 the latest version of Excel. and it does not like some of the old formula any more. I really don't know. some of the newer formula need helper cells for each spill. This means i need a separate sheet to run the searchable dropdowns. this is not a good idea. Not sure if you can help.
can i export and use the same searchable drop-down menu in another worksheet in the same workbook without having to copy the entire data from the existing sheet. what i need to do is to use the dropdown menu and its data in another sheet.
How to make =Cell("contents") only gets updated when typing in specific cell range, like in this video it will only get updated when typing something in the yellow cell. It's kinda annoying knowing the list on the right is getting refreshed everytime I type anything anywhere.
I came up with this: =Cell("contents") only gets updated when typing in a specific sheet. Place this into the VBA code of that specific sheet. (two Sheets needed) 1.Private Sub Worksheet_Activate()With Worksheets("SearchSheet")Cells(x, y).Value = "True" End WithEnd Sub Private Sub Worksheet_Deactivate()With Worksheets("SearchSheet") .Cells(x, y).Value = "FALSE"End WithEnd Sub. 2."=cell("Contents")" into C2. 3.D2(or whereever your search is liked) =IF(Xy="FALSE","",C2)
i'm pretty new to excel, but i got the whole project above working on my laptop, i can't quite figure out how to change your code to work for me though, the VBA editor is giving me errors
