Hi guru... Thank God I found you! I've been searching a lot looking for examples and even though i am not good on excel, your example is very clear and well explained... Thanks a lot!
Hi Thanks .. very nice video.. I have a question .. daily i need to change the names as it is increasing/decreasing so we need to change the reference of these formulas. Is there any way to make it dynamic?
Hi guru How to use this data validation for multiple cells, in given case we have fixed search for one cell A2, if I want to use same for A2:A500 then how to use Thanks in advance
Excellent video!!! Thanks so much! How do I apply the searchable drop down list to other sheet? It doesnt seem to work when applied to different sheet, the search result is the same as the original searchable drop down list. Is there to solve this? Thanks in advance!
This is great for having only one searchable dropdown, but what if you wanted another one in A3 also? I have a spreadsheet for which I would like to have multiple instances of the same dropdown search on many rows.
You made my life easy.....you are excellent and keep it up....and a BIG THANKS..... I have a question for you, How can i link the value of that cell where we do the data validation to an another particular cell like we do when we use combo box in excel.. Is it possible in data validation also.. hope your reply....
Hi, I wanted to know if there's an option to use this with all the rows in a table. For example, I have a separate table for my sales. and I have different rows for all the products I sell. so is there a way to use this searchable drop down data validation. rightnow I am using the basic data validation step.
great stuff. but how do you do it if your information is on another sheet? how would i get it to work i gte nothing but error on page where i want the drop down..
Thank you so much sir... It's awesome BUT I have a question. Its works on just one cell if I need in multiple cells same formula then what should I do? because right now its not working in other cells as I just copied & pasted in others cell same formula but it can't works PLEASE HELP ME OUT!!!
So Cool. Thank you so much !! I am doing this in a Table in 2016 version of excel. How do I create the formula for the "Auto suggest List" specifically the Rows function formula? In other words i want the range of rows to be dynamic. My formula ends up looking like this, but with no results shown: =IFERROR(VLOOKUP(ROWS($D$11:D11),TBL_Minor_Factions[[ID]:[Minor Faction Name]],2,0),""). So this array doesn't work for some reason: TBL_Minor_Factions[[ID]:[Minor Faction Name]]. What array would using Table nomenclature?
Thank you Dear. Important Links MS Excel Beginner Series ruclips.net/video/3kNEv3s8TuA/видео.html MS Excel Intermediate Series ruclips.net/video/U3bxRiJeWlg/видео.html MS Excel Advanced Series ruclips.net/video/98PwqRd9Rfc/видео.html
Hi Guru, I saw very closely searchable data validation, but it works in FIRST row only ............... how to make it workable to rest of the rows if i have multiple entries in single sheet ?
Thanks for instruction and it is very useful. What if there are couple of same names in the Names Row, how to eliminate other same name to one. For instance, there are several "vodka" in the data field, how to make one vodka on searchable drop down menu without using Macro.
hello! sir.. thanks for the video. .. i want to make a request can we have any other easier technique to create dropdown list in Excel. as this method is very complicated and complex.
He Guru, I was able to make your tutorial and I encountered a problem. The dropdown list only functions on the first row of the Drop Down search list. On the 2nd Row, the drop down list only shows the entry in the first row. Why is taht? Pls help.-MIke fr PH ty
+ro anwang , Hi, just type =cell("content") on the cell where you first enter the name to search, it will give you a circular error, but ignore it, then you can use the drop down menu where ever you want, then copu the offset formula and enter it on the range value of the drop down menu
This was very helpful and easy to follow. How ever I am getting #Value! in the unique ID column when I type in the first few letters I am searching on. I should tell you I am using the Drop down in one sheet and the list from another. It never actually assigns the unique ID or incremental numbers. Here is the formula.=IF(ISNUMBER(SEARCH('Value Proposition'!$C$4,Sheet1!$D$4:$D$93)),MAX($C$1:C1)+1,0). Any thoughts or suggestions?
Hi guru How to use this data validation for multiple cells, in given case we have fixed search for one cell A2, if I want to use same for A2:A10 then how to use
Thank you Dear. Important Links MS Excel Beginner Series ruclips.net/video/3kNEv3s8TuA/видео.html MS Excel Intermediate Series ruclips.net/video/U3bxRiJeWlg/видео.html MS Excel Advanced Series ruclips.net/video/98PwqRd9Rfc/видео.html
hi, How To Create A searchable drop down list (from other sheet) in excel 2010 in hindi, and it also be used in multiple row in another sheet, plz help.
Sir Maltipal drop down bnate h to jo pahle wale m search kiya huaa hota h wahi dusre wale m dikhata h chahe dusra name search kar lo phir bhi please btao kaise sahi kare Hindi m batana
I get stuck on MAX. This function does not work for me. It will provide 0 in every column even if it should be a 1. Here is what I input. =IF(ISNUMBER(SEARCH($A$1,C1:C81)),MAX($B$1:B1)+1,0) A1 is my search word, C1 is the column with the list of items to search, B1 is the column with the true or false. Any help would be greatly appreciated.
Hi, Thanks for the video. Very useful. IF(ISNUMBER(SEARCH($A$2,$D$2:$D$8)),MAX($C$1,C1)+1,0) formula does not work for me as expected. List is not coming up in the incremental numbers wherever the search is matched. Help here is highly appreciated.
firstly sir thanks for uploading the video it is really helpful for me but i need ur help to solve my query.That is good for single{(cell (A2) in video }searchable drop down list but if we need same searchable option in cell,A3,A4,A5............... How can we create the same in other cell i required this, suppose i make item list in every cell if i put the item name in cell than required drop down list should be come with the option sir plz help me out
Did you ever figure out how to copy this searchable list to other cells? I have tried multiple ways to copy and paste, however when I paste to a new cell the drop down list is just a regular list and does not search.
+Amanda Bell hey even am looking for the same thing, please help me on this query. I want to copy paste the same dropdown list to other cells as well, without changing the array.
What if I wanted the drop down list in cell A2, to appear on multiple continuous cells on another sheet. For example, how do you make the drop down appear on sheet 1, in cell A14 to A50?
+Picking Time Videos hey even am looking for the same thing, please help me on this query. I want to copy paste the same dropdown list to other cells as well, without changing the array.
+Abhinav Srivastava The following is the link to my post on the forum. I don't think you need to login to view. There is no code in this workbook. To understand it, look at the Name Manager, Data Validation, and LIST MANAGER worksheet. Note that on LIST MANAGER sheet, for Income, columns B,C, E & F are used; for Expenses, columns G,H,J & K; for Misc-Expense columns L, M, O & P are used. To see it work, go to JANUARY sheet, click in a cell under NAME, in Income, Expense or Misc-Expense section, then enter a few (2 or 3) characters, then click the down arrow, and pick from list. (make sure the name(s) that contains those 2 or 3 characters are in the right list on LIST MANAGER) COPY AND PASTE THE FOLLOWING INTO YOUR BROWSER: www.excelforum.com/excel-formulas-and-functions/1123119-searchable-drop-down-on-different-sheet.html
+Picking Time Videos In the post on Excelforum, find the workbook that say "FINAL" after the name. You maybe required to register in order to download the workbook. Also, Windy is the person who completed this for me. You may want to contact her for a better understanding of how she used the Name Manager, Data Validation, and created the formulas in the column listed above on the LIST MANAGER. Good luck.
Hi Guru, very useful. Could you advice how do we use this data validation for multiple cells please? As mentioned by a few comments here, can't seem to use it for A3: A50? Thanks.
Please help. I cant get past the MAX formula. Everything works, but it will only add the +1 if the cell before also was a hit on the search. So, when I search for "vod", my list looks like this: 0 rum1 1 vodka1 0 rum2 1 vodka2 2 vodka3 0 rum3 1 vodka4 This is my formula in B2 is: =IF(ISNUMBER(SEARCH($A$2;$C$2:$C$36));MAX($B$1;B1)+1;0) This is my formula in B3 is: =IF(ISNUMBER(SEARCH($A$2;$C$2:$C$36));MAX($B$1;B2)+1;0) (for some reason i have to use ";" in stead of ",", or else i get an error) And I use Excel 2010 Thanks in advance!
Really ticks me off when a poster doesn't answer questions, esp. when multiple people are asking the same thing - like how to extend the range of data validation. If it can't be done, say it can't be done. If you don't want to answer questions, don't post. This has given me nothing but a headache.
SO +MyELesson .. Dropdown cannot be copied to another cell .. useful stuff but if cannot be copied to other cell then 99.5% people cannot make use of it !!
![Noob To Max With DRAGON REWORK In Blox Fruits [FULL MOVIE]](http://i.ytimg.com/vi/LnBMOinoOvA/mqdefault.jpg)
Holy Toledo. This is *IT* man. No VBA, no complicated things. This might just be what I have been looking all my life (ok ok all my *week)
Hi guru... Thank God I found you! I've been searching a lot looking for examples and even though i am not good on excel, your example is very clear and well explained... Thanks a lot!
Hi Guru
It's Really use full for me. Thanks a lot.
THANK YOU SO MUCH
I really achieve what i wanted and i can now finish my project thanks to the content in this video.
Best REgards
Excellent Tutorial ! Cool to listen to you and follow :)
Thanks
Thank you, you should see the power of Pivot charts which i have explained in this new video ruclips.net/video/cvRUuhR9iec/видео.html
understandable even for a beginner, thanks :) However, what if I need the drop box on each line again with a full list?
Thank you a little long process but well worth it. THANK YOU!!!☺
You're a hero bro
After looking this i went crazzzzzzzy thanks a tons dud you rock
Mal Mohammad Hahaha
Thank you so much, love your channel. Will subscribe
Welcome, you can watch all my videos at www.myelesson..org
Hi
Thanks .. very nice video.. I have a question .. daily i need to change the names as it is increasing/decreasing so we need to change the reference of these formulas. Is there any way to make it dynamic?
Hi guru
How to use this data validation for multiple cells, in given case we have fixed search for one cell A2, if I want to use same for A2:A500 then how to use
Thanks in advance
sudeep kasamsetty .... i have the same question
You can use this one www.excelautocomplete.com/
Excellent video!!! Thanks so much! How do I apply the searchable drop down list to other sheet? It doesnt seem to work when applied to different sheet, the search result is the same as the original searchable drop down list. Is there to solve this? Thanks in advance!
This is great for having only one searchable dropdown, but what if you wanted another one in A3 also? I have a spreadsheet for which I would like to have multiple instances of the same dropdown search on many rows.
You made my life easy.....you are excellent and keep it up....and a BIG THANKS..... I have a question for you, How can i link the value of that cell where we do the data validation to an another particular cell like we do when we use combo box in excel.. Is it possible in data validation also.. hope your reply....
this is very use full
thank you..
Welcome .
Hi.thanks for the tutorial. Is there any way to lock the cells without ruining the function? Because once i lock the cells. It will not work.thanks
Nice video. What did you use for your screen capture software was it Camtasia?
Yes Andy :)
Hi,
I wanted to know if there's an option to use this with all the rows in a table. For example,
I have a separate table for my sales. and I have different rows for all the products I sell. so is there a way to use this searchable drop down data validation. rightnow I am using the basic data validation step.
No :(
Really useful thanks
great stuff. but how do you do it if your information is on another sheet? how would i get it to work i gte nothing but error on page where i want the drop down..
Dominic Abrams I would try to create a version for that that soon :)
Great that will be awesome.. Please send me the link when you have done this.. would like to edit my invoice system to do this...great work keep it up
Thank you so much sir... It's awesome BUT I have a question. Its works on just one cell if I need in multiple cells same formula then what should I do? because right now its not working in other cells as I just copied & pasted in others cell same formula but it can't works PLEASE HELP ME OUT!!!
So Cool. Thank you so much !! I am doing this in a Table in 2016 version of excel. How do I create the formula for the "Auto suggest List" specifically the Rows function formula? In other words i want the range of rows to be dynamic. My formula ends up looking like this, but with no results shown: =IFERROR(VLOOKUP(ROWS($D$11:D11),TBL_Minor_Factions[[ID]:[Minor Faction Name]],2,0),""). So this array doesn't work for some reason: TBL_Minor_Factions[[ID]:[Minor Faction Name]].
What array would using Table nomenclature?
Hi menn, thanks a lot this video. But when I type the offset with the countif it gives me #Ref!
Hello!
Great Videos! I have a question about the search results, how can I link the search result to its corresponding sheet?
+Leo Celes I dont thinks so, try it and let know.
Hey, Thanks for this wonderful video man. Can you please suggest why offset is used and what is the function of this formula/function. Thanks!
Welcome, you can check the offset function on my website www.myelesson.org
can we do it on userform instead of worksheet?
Nadia Rayhanna I would love to try that .
Nice video. Now... can we do the same thing WITHOUT the helper columns?
super cool Sir... champ
Thank you Dear. Important Links
MS Excel Beginner Series
ruclips.net/video/3kNEv3s8TuA/видео.html
MS Excel Intermediate Series
ruclips.net/video/U3bxRiJeWlg/видео.html
MS Excel Advanced Series
ruclips.net/video/98PwqRd9Rfc/видео.html
Hi Guru, I saw very closely searchable data validation, but it works in FIRST row only ............... how to make it workable to rest of the rows if i have multiple entries in single sheet ?
How I can copy drop down list in other cells of the same column of same sheet??? Plz help
Worked perfectly! Great instruction however, when I try to use it on another page I get an error. Any suggestions?
thanks a lot, my dear.
Thanks for instruction and it is very useful. What if there are couple of same names in the Names Row, how to eliminate other same name to one. For instance, there are several "vodka" in the data field, how to make one vodka on searchable drop down menu without using Macro.
Hello Guru, can you please upload a searchable drop down list which uses the data from another worksheet. Thank you.
Thanks for the wonderful video. How to have the same searchable dropdown list for a range of cells? Request your help and guidance. Thanks in advance.
you just need to drag the autofill cell function down to the end of cell that you want mr rahul...
i have a many problems in my excel sheet i tried to make bill receipt in excel but i want to use searchable box in it.
hello! sir.. thanks for the video. .. i want to make a request can we have any other easier technique to create dropdown list in Excel. as this method is very complicated and complex.
Will Try.
Superb
Sir make video all commercial formulas in excel
thanks. but what if I need the drop box on each line again with a full list?
Hey soby I tried this draging down to other lines and it worked.Try it.
Awesome !
i share it from jana schrenkova down there
SOBHY BAUMY hi how can you make the drop down list in rach line?
He Guru, I was able to make your tutorial and I encountered a problem. The dropdown list only functions on the first row of the Drop Down search list. On the 2nd Row, the drop down list only shows the entry in the first row. Why is taht? Pls help.-MIke fr PH ty
ro anwang Try following the steps once again as shown in the video , there should be no errors .
+My E-Lesson Having trouble with "copying the row across" wasn't sure what you did there
+ro anwang , Hi, just type =cell("content") on the cell where you first enter the name to search, it will give you a circular error, but ignore it, then you can use the drop down menu where ever you want, then copu the offset formula and enter it on the range value of the drop down menu
Thanks so much!
Welcome :)
Awesome!
This was very helpful and easy to follow. How ever I am getting #Value! in the unique ID column when I type in the first few letters I am searching on. I should tell you I am using the Drop down in one sheet and the list from another. It never actually assigns the unique ID or incremental numbers. Here is the formula.=IF(ISNUMBER(SEARCH('Value Proposition'!$C$4,Sheet1!$D$4:$D$93)),MAX($C$1:C1)+1,0). Any thoughts or suggestions?
Hi guru
How to use this data validation for multiple cells, in given case we
have fixed search for one cell A2, if I want to use same for A2:A10
then how to use
I have the same question too, did you managed to figure it out?Thanks!
Your Fan Sir
Thank you Dear. Important Links
MS Excel Beginner Series
ruclips.net/video/3kNEv3s8TuA/видео.html
MS Excel Intermediate Series
ruclips.net/video/U3bxRiJeWlg/видео.html
MS Excel Advanced Series
ruclips.net/video/98PwqRd9Rfc/видео.html
hi,
How To Create A searchable drop down list (from other sheet) in excel 2010 in hindi, and it also be used in multiple row in another sheet, plz help.
Hi Guru,
ro anwang was right ....only the entry in the row will apprea in dropdown list.
+Jeffrey Baston , read the answer I typed to ro anwang
What to do if the names are in another sheet?
How to create trading and profit & loss account and balance sheet in excel
Sir
Maltipal drop down bnate h to jo pahle wale m search kiya huaa hota h wahi dusre wale m dikhata h chahe dusra name search kar lo phir bhi
please btao kaise sahi kare
Hindi m batana
Guruji I did everything shown, the only problem i encountered is
in the last step my dropdown is empty. kindly help.
Thank you
Please upload in hindi Guru ji..
its a amazing trick....
Sunil Pathak The Hindi version is also available , add Hindi at the end of name of the video and search again : )
I get stuck on MAX. This function does not work for me. It will provide 0 in every column even if it should be a 1. Here is what I input. =IF(ISNUMBER(SEARCH($A$1,C1:C81)),MAX($B$1:B1)+1,0) A1 is my search word, C1 is the column with the list of items to search, B1 is the column with the true or false.
Any help would be greatly appreciated.
Hi,
Thanks for the video. Very useful.
IF(ISNUMBER(SEARCH($A$2,$D$2:$D$8)),MAX($C$1,C1)+1,0) formula does not work for me as expected. List is not coming up in the incremental numbers wherever the search is matched.
Help here is highly appreciated.
Did anyone ever answer you? My SS is doing the same thing, no incremental.
There's one I found guys so much better than this it can auto complete .
ruclips.net/video/jrqvKDf6gzI/видео.html&feature=share
Nice Video. :-)
Alex King Thank you
firstly sir thanks for uploading the video it is really helpful for me but i need ur help to solve my query.That is good for single{(cell (A2) in video }searchable drop down list but if we need same searchable option in cell,A3,A4,A5............... How can we create the same in other cell
i required this, suppose i make item list in every cell if i put the item name in cell than required drop down list should be come with the option
sir plz help me out
Did you ever figure out how to copy this searchable list to other cells? I have tried multiple ways to copy and paste, however when I paste to a new cell the drop down list is just a regular list and does not search.
+Amanda Bell hey even am looking for the same thing, please help me on this query.
I want to copy paste the same dropdown list to other cells as well, without changing the array.
What if I wanted the drop down list in cell A2, to appear on multiple continuous cells on another sheet. For example, how do you make the drop down appear on sheet 1, in cell A14 to A50?
+Picking Time Videos hey even am looking for the same thing, please help me on this query.
I want to copy paste the same dropdown list to other cells as well, without changing the array.
I have a working example, that solved my problem, I can send it to you. Are you a member of Excel Help Forum, Ozgrid Forum, or VB Forum?
no am not, can you plz send me the link of the video here, that would be great
+Abhinav Srivastava The following is the link to my post on the forum. I don't think you need to login to view. There is no code in this workbook. To understand it, look at the Name Manager, Data Validation, and LIST MANAGER worksheet. Note that on LIST MANAGER sheet, for Income, columns B,C, E & F are used; for Expenses, columns G,H,J & K; for Misc-Expense columns L, M, O & P are used. To see it work, go to JANUARY sheet, click in a cell under NAME, in Income, Expense or Misc-Expense section, then enter a few (2 or 3) characters, then click the down arrow, and pick from list. (make sure the name(s) that contains those 2 or 3 characters are in the right list on LIST MANAGER) COPY AND PASTE THE FOLLOWING INTO YOUR BROWSER: www.excelforum.com/excel-formulas-and-functions/1123119-searchable-drop-down-on-different-sheet.html
+Picking Time Videos In the post on Excelforum, find the workbook that say "FINAL" after the name. You maybe required to register in order to download the workbook. Also, Windy is the person who completed this for me. You may want to contact her for a better understanding of how she used the Name Manager, Data Validation, and created the formulas in the column listed above on the LIST MANAGER. Good luck.
Thanks again Sajesh! Some of my Unique ID's or duplicating so not giving the complete list. Please see the example.
1
Texas, Beaumont
1
Texas, Brazoria
2
Texas, Dallas
2
Texas, Fort Worth
2
Texas, Galveston
3
Texas, Houston
3
Texas, Rest of Texas
Hi Guru, very useful. Could you advice how do we use this data validation for multiple cells please? As mentioned by a few comments here, can't seem to use it for A3: A50?
Thanks.
saw an almost identical video by Neil Firth....but his was posted earlier
Please help.
I cant get past the MAX formula. Everything works, but it will only add the +1 if the cell before also was a hit on the search.
So, when I search for "vod", my list looks like this:
0 rum1
1 vodka1
0 rum2
1 vodka2
2 vodka3
0 rum3
1 vodka4
This is my formula in B2 is: =IF(ISNUMBER(SEARCH($A$2;$C$2:$C$36));MAX($B$1;B1)+1;0)
This is my formula in B3 is: =IF(ISNUMBER(SEARCH($A$2;$C$2:$C$36));MAX($B$1;B2)+1;0)
(for some reason i have to use ";" in stead of ",", or else i get an error)
And I use Excel 2010
Thanks in advance!
I found my error! :D
In the Max formula, I used ; in stead of :
Thank you for this, and all the other tutorials! :D
how to creat searchable dependent drop down list
is it useful in practical life? i think not......
Dear Sir... pls come up with an easier option to create this amazing searchable list in Excel. #A #Humble #REQUEST
Will try
Really ticks me off when a poster doesn't answer questions, esp. when multiple people are asking the same thing - like how to extend the range of data validation. If it can't be done, say it can't be done. If you don't want to answer questions, don't post. This has given me nothing but a headache.
SO +MyELesson .. Dropdown cannot be copied to another cell .. useful stuff but if cannot be copied to other cell then 99.5% people cannot make use of it !!