Just wanted to post a comment so you know that your video is still useful today! currently useing this formula as a member of my HR department and its been super useful to those of us still working without some of the newer features not available for Microsoft 2016 - thank you!
Hi Neil, Thanks for posting this great tutorial! Now in 2020 most tutorials focus on the FILTER function, which is exclusive to the Excel365 edition. Very thankful that you posted this about dynamic ranges for all of us who have older or stand-alone versions.
WOW!! NOW THIS IS A LEGENDARY FORMULA TRICK! IMAGINE UPLOADED IT 11 YEARS AGO; BUT NOT LATER 11 YEARS AGO, EXCEL PROVIDED A SIMPLE "FILTER" FORMULA AND YET NOT ALL EXCEL IS APPLICABLE ON THAT. NOW SIR YOU ARE A TRUE FATHER OF EXCEL. THANK YOU!!!!
I know this has been up a long time but I happened to Google the exact name of the video. It's complicated but extremely well explained and when I not only got it to work but got it to work in different cells with different validation..well..satisfying. I started out with Excel in about 1998. Back then you had to be able to work around stuff in ways that these days are just automated by a GUI. Anyway, thanks all these years later for uploading this.
Sir, VERY SLOW & CLEANLY explanation. Previously I used to follow the procedure blindly watching other videos. I am now very confident to write the formula after watching your video. Thank you very much Sir.
Sir, I have watched lots of excel tutorials but for me, I have learned more from you. From your videos I have maximized more functions. I also learned how to extract records from multiple categories simply by replacing the search function to and function. Thank You so much!!!
Hundreds of people have tried to find a way to create such a drop down choose able list without venturing into VB. But no body could. I myself have googled much but couldn't find a way to do the same, until of course I came across this video of yours. Bravo buddy. You really are an expert. Now, one thing. Couldn't you provide the exercise file along with. So that the less smart people like us could follow along your guidelines while watching the video.?? Still, thanks buddy. You rock!!!
This is one of my most favorite Excel tools learned from TH-cam. It's presented very well, extremely well explained step-by-step, easy to follow and replicate. I put together a series of searchable drop down lists in tables, one for each row of a quote document. Now the user can search a short list of parts to select for the quote. This is much, much easier.
Wow, that was such a great and simple tutorial. I especially LOVED how you explained the formulas step by step instead of just saying "type this long formula into I2 and you're done" like many websites and videos say. I actually understand what is going on with that formula!
Great explanation. You build incrementally, one concept at a time which make it understandable. I just had to watch it more than once to understand it all. Thanks for this.
Thank you so much for putting this together. This info, plus the additional info from part 2, 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.
Mr. Firth: I just finished studying and putting into action the videos on how to Create a Searchable Drop Down List in Excel (1&2). After several hours of going between your videos and my application, I got it to work. I am associated with the Saint Vincent DePaul Society, and part of what we do is deliver food and other items to a food pantry. We need to keep track of how much we take there. I use this Excel application to do that. Your videos were of great help to me. Thanks.
Hi Neil, I was referred to this video by someone who used my financial calculators to improve the user interface. I did everything as you stated and it worked like a charm. Thank you very much.
I am so much in tears right now! THANK YOU SO MUCH for sharing what you know with all of us! You helped me and all of the people who'll be using the template I'm preparing. Thanks again!
This was a great video. I have a very large database and trying to scroll through hundreds of products it to find a certain one was very time consuming. This video helped me to make my database a searchable one. Thank you.
I dis this formula in the source of a drop-down list and it worked very well: =OFFSET(Sheet3!$A$2,$B$22,0,MATCH("*",Sheet3!$A$2:$A$45,-1)+1,1) Where $B$22 = MATCH(A22,Sheet3!$A$2:$A$45,1)-1) :)
Thank you very much Neil. This method is very useful and I found the second part (as your response to those who wish to incorporate the function to other cells) more appropriate for my application. This is exactly what I was looking for. More power to you Neil and thank you so much. Mabuhay from the Philippines!
Works great with one position!! I just found your followup tutorial on adding the feature to multiple rows!! This just anwered my newest question today. Thanks for the great work!
GREAT VIDEO!! thanks it is really helpful!!! not just for the dynamic drop down list, but for how you conjugate formulas... it gives me a lot of ides... thanks so much!!
Brilliant Neil. I only got halfway and gave a thumbs up and subscribed. A good practical use of formulas that I have known of for many years. (Or should that be ''tears")
YOU DON'T KNOW HOW MUCH HAPPY YOU MADE ME TODAY, I WAS UPSET WHEN FILTER FUNCTION NOT AVAILABLE IN MY EXCEL BUT YOUR WAY IS AN OUTSTANDING SOLUTION. THANKS A TON. BE BLISSFUL .
Back in 2012 when this was uploaded, I bet not many people would have been able to guess who the future US President and UK Prime Minister would be in that sample list!
Great tip! In Excel 2010, if you protect this sheet with typical settings, you cannot click the drop down until you type and press enter in the cell. Make sure you choose the "Edit objects" setting when turning on protection for it to work as normal.
Thanks Neil, based on your 2 vids I have made myself a version for scientific names using LEFT(cellRef, LEN()) to restrict the search to as many letters of the genus name as the operator enters! Very handy for a list of 4000+ names.
Thanks Neil! This has given such a great solution to a big issue I was having. My little tweak is that I've given up v/hlookup as my 2016 new year's resolution and prefer INDEX or OFFSET (MATCH) as this can speed things up with a really big file.Your step by step approach was really perfect. Thanks again!
Thank you very much for this. I have seen other far more complicated and excessive ways to do something similar, but this one is the most simple and logical. It's a great help. Cheers mate.
This was sooo convoluted..but it works lol =D (I don't mean your video and explanation was convoluted I just mean the whole process to do it, your video was great!)
This is the most simple video i have even seen for the purpose. It would be great if you could also share the technique wherein the drop down shall reflects the suggestions based on the details entered in search box, without clicking on drop down arrow.
Neil....that is Awesome to have! You lost me so fast, but it is me. I am trying to jump in the deep end of the pool considering I am so into Excel now.
Very helpful indeed, it makes one understand how excel truly works, I have just learnt of the new excel function #filter and have noticed it does the trick up to leave us just before the offset function. you can also try this formula dear colleagues for those with Office 365 or Microsoft 365 { =FILTER(G2:G30,ISNUMBER(SEARCH($D$2,G2:B30)),"") } Microsoft is ever improving. But otherwise thanks so much for the great lesson.
Hi Neil Thanks for this video, it works great on one of my workbooks. I do have a slight problem at the moment. You see, on one instance in this workbook it works just fine but in another it's giving me the "circular reference" error and I have followed your instructions to the T, except I'm using tables for data population. My formulas is a follows; sheet 1 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A1)+1;0) sheet 1 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A2)+1;0) This works great sheet 2 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B2)+1;0) sheet 2 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B3)+1;0) Cell B3 should give the value of 1 and B6 should give the value of 2 in this case, but when I auto-fill if freaks out with the circular reference and give me 0s all the way. I'm stuck. The semicolon work here as the comma does at your side. Any help will be appreciated.
I can not believe that this is a video posted 11 years ago when there was no spill array function(like Filter function). You are a master.
Just wanted to post a comment so you know that your video is still useful today! currently useing this formula as a member of my HR department and its been super useful to those of us still working without some of the newer features not available for Microsoft 2016 - thank you!
Hi Neil,
Thanks for posting this great tutorial!
Now in 2020 most tutorials focus on the FILTER function, which is exclusive to the Excel365 edition. Very thankful that you posted this about dynamic ranges for all of us who have older or stand-alone versions.
WOW!! NOW THIS IS A LEGENDARY FORMULA TRICK! IMAGINE UPLOADED IT 11 YEARS AGO; BUT NOT LATER 11 YEARS AGO, EXCEL PROVIDED A SIMPLE "FILTER" FORMULA AND YET NOT ALL EXCEL IS APPLICABLE ON THAT. NOW SIR YOU ARE A TRUE FATHER OF EXCEL. THANK YOU!!!!
I know this has been up a long time but I happened to Google the exact name of the video. It's complicated but extremely well explained and when I not only got it to work but got it to work in different cells with different validation..well..satisfying. I started out with Excel in about 1998. Back then you had to be able to work around stuff in ways that these days are just automated by a GUI. Anyway, thanks all these years later for uploading this.
I just want to express my gratitude. The technique described is quite elegant. Good job.
Sir, VERY SLOW & CLEANLY explanation. Previously I used to follow the procedure blindly watching other videos. I am now very confident to write the formula after watching your video. Thank you very much Sir.
Sir, I have watched lots of excel tutorials but for me, I have learned more from you. From your videos I have maximized more functions. I also learned how to extract records from multiple categories simply by replacing the search function to and function. Thank You so much!!!
Hundreds of people have tried to find a way to create such a drop down choose able list without venturing into VB. But no body could. I myself have googled much but couldn't find a way to do the same, until of course I came across this video of yours. Bravo buddy. You really are an expert.
Now, one thing. Couldn't you provide the exercise file along with. So that the less smart people like us could follow along your guidelines while watching the video.?? Still, thanks buddy. You rock!!!
This is one of my most favorite Excel tools learned from TH-cam. It's presented very well, extremely well explained step-by-step, easy to follow and replicate. I put together a series of searchable drop down lists in tables, one for each row of a quote document. Now the user can search a short list of parts to select for the quote. This is much, much easier.
Watched this when i started learn Excel. Today i watch again and i feel grateful because this video actually taught me a lot about Excel formula.
Wow, that was such a great and simple tutorial. I especially LOVED how you explained the formulas step by step instead of just saying "type this long formula into I2 and you're done" like many websites and videos say. I actually understand what is going on with that formula!
Thanks! 9 years later and I actually needed this today lol that's a million
Great explanation. You build incrementally, one concept at a time which make it understandable. I just had to watch it more than once to understand it all. Thanks for this.
i'm totally an excel noob and I actually successfully manage to make the damn drop down list, thank you very much!
Brilliant! Clear and concise walkthrough with just the right amount of details. Thank you!
Thank you so much for putting this together. This info, plus the additional info from part 2, 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.
Mr. Firth:
I just finished studying and putting into action the videos on how to Create a Searchable Drop Down List in Excel (1&2). After several hours of going between your videos and my application, I got it to work. I am associated with the Saint Vincent DePaul Society, and part of what we do is deliver food and other items to a food pantry. We need to keep track of how much we take there. I use this Excel application to do that. Your videos were of great help to me. Thanks.
Hi Neil, I was referred to this video by someone who used my financial calculators to improve the user interface. I did everything as you stated and it worked like a charm. Thank you very much.
I am so much in tears right now! THANK YOU SO MUCH for sharing what you know with all of us! You helped me and all of the people who'll be using the template I'm preparing. Thanks again!
Wow. It's 2020 and you are still helping folks out with this video. Thank you! Works great.
This was a great video. I have a very large database and trying to scroll through hundreds of products it to find a certain one was very time consuming. This video helped me to make my database a searchable one. Thank you.
Hi Neil, I am so grateful to see this video. This helps me bigtime with one of my job. Wonderful!
This what I am looking for in so many weeks. Dynamic drop-down list without macro or VBA. Thank you Neil.
Thank you! I have studied so many versions of instructions and this one worked!
I dis this formula in the source of a drop-down list and it worked very well:
=OFFSET(Sheet3!$A$2,$B$22,0,MATCH("*",Sheet3!$A$2:$A$45,-1)+1,1)
Where $B$22 = MATCH(A22,Sheet3!$A$2:$A$45,1)-1)
:)
A bit complex but truly sophisticated; congratulaitons and very many thank you's, Sir!
even the Video is old you deserve more than 10000 likes.
thanks for the great explanation .you saved me lot of time.
plus you deserve sub
Great learning experience. I was looking for something else, but found this and I was hooked. Thank you teacher.
Fantastic solution to an annoying problem...thanks Neil for a great video tutorial and solution
Thank you very much Neil. This method is very useful and I found the second part (as your response to those who wish to incorporate the function to other cells) more appropriate for my application. This is exactly what I was looking for. More power to you Neil and thank you so much. Mabuhay from the Philippines!
This is some serious bad ass excel skills right there, people
Works great with one position!!
I just found your followup tutorial on adding the feature to multiple rows!! This just anwered my newest question today. Thanks for the great work!
thanks a zillion trillion billion million ... u made me a genius in excel by the end of ur vid.tutorial
This is a fabulous workaround and easy to set up. Many Thanks
Neil, that is the best video I have ever come across! very useful, thanks for efforts .
super helpful. Nice and thorough detailing along the way.
GREAT VIDEO!! thanks it is really helpful!!! not just for the dynamic drop down list, but for how you conjugate formulas... it gives me a lot of ides... thanks so much!!
Brilliant Neil. I only got halfway and gave a thumbs up and subscribed. A good practical use of formulas that I have known of for many years. (Or should that be ''tears")
YOU DON'T KNOW HOW MUCH HAPPY YOU MADE ME TODAY, I WAS UPSET WHEN FILTER FUNCTION NOT AVAILABLE IN MY EXCEL BUT YOUR WAY IS AN OUTSTANDING SOLUTION. THANKS A TON. BE BLISSFUL .
Excellent video. Thank you !! It is exactly what I was looking for since I do not have Dynamic Arrays.
Thank you very much. Your explanation is very easy to understand and can be use to other excel version.
Back in 2012 when this was uploaded, I bet not many people would have been able to guess who the future US President and UK Prime Minister would be in that sample list!
Wonderful remark xD
So, Angelina Jolie can be the next president?
who cares
@@kikin310 You obviously care enough to leave a comment 🤣
Hi Neil . It comes from an Innovative and genuis mind. Thanks a lot.
This has been really useful and it worked successfully
Great tip! In Excel 2010, if you protect this sheet with typical settings, you cannot click the drop down until you type and press enter in the cell. Make sure you choose the "Edit objects" setting when turning on protection for it to work as normal.
WOW
Life saver. Brilliant tutorial and very easy to follow.
Thanks Neil, based on your 2 vids I have made myself a version for scientific names using LEFT(cellRef, LEN()) to restrict the search to as many letters of the genus name as the operator enters! Very handy for a list of 4000+ names.
No idea how useful it is ... saved me
Thanks Neil! This has given such a great solution to a big issue I was having. My little tweak is that I've given up v/hlookup as my 2016 new year's resolution and prefer INDEX or OFFSET (MATCH) as this can speed things up with a really big file.Your step by step approach was really perfect. Thanks again!
This is exactly what im looking for! process is a bit complicated but does what I want to have on my spreadsheet! thank you so much Neil!
Very good job ! I wonder something, people who disliked the video, do they have a better one ?
Excellent......... It is very helpful to me.....Thank you very much....
I'm impressed. Well presented and all formulas stepped through.
Thanks I learnt some very useful procedures.
Do you have any input on the above comment from me?
Mate, you are a genius. God bless you.
Thank you very much for this. I have seen other far more complicated and excessive ways to do something similar, but this one is the most simple and logical. It's a great help. Cheers mate.
Excellent stuff. Greetings from Papua New Guinea (North of Australia).
the best!!!! big time the best tutorial , Thank you very much
Don't need this just this minute, but it's GREAT to know it's here when I do. Thank you. Good stuff.
Neil! You are an Excel Guru...
This was sooo convoluted..but it works lol =D (I don't mean your video and explanation was convoluted I just mean the whole process to do it, your video was great!)
Genius!! Very well explained, simple, and it works.
Genius 100% ..simple ..not for me ..I am dumb maybe 👍😉
The simplicity of this is awesome!
Just a powerful search button.
Thank you
kudos. May ALLAH give u reward in this world & hereafter
Many thanks for the clear explanations at each step. This has helped me greatly!
Awesome. Thanks for the solution. Really appreciate it.
WOW, it looks so easy after your presentation, i have trying this for last one year. GREAT!! Thank you!!
@m p ha ha ha well said
SIr, You are just simply great in teaching.
really great, i love the way you explain every part of each formula. people should teach like that more often.
This is what I would call an advanced user! you're the shit!
This is the most simple video i have even seen for the purpose. It would be great if you could also share the technique wherein the drop down shall reflects the suggestions based on the details entered in search box, without clicking on drop down arrow.
Neil....that is Awesome to have! You lost me so fast, but it is me. I am trying to jump in the deep end of the pool considering I am so into Excel now.
I found this video quite helpful. Keep it up. I love your channel.
I think I like this better than another way that uses VBA. Going to give this a test. Thank you
Very helpful indeed, it makes one understand how excel truly works, I have just learnt of the new excel function #filter and have noticed it does the trick up to leave us just before the offset function. you can also try this formula dear colleagues for those with Office 365 or Microsoft 365 { =FILTER(G2:G30,ISNUMBER(SEARCH($D$2,G2:B30)),"") } Microsoft is ever improving. But otherwise thanks so much for the great lesson.
Dude, you are a lifesaver!
Neil, thanks muuuch! this has been most helpful! simple and like the explanation of why you go to the end result
Very talented. Thanks for sharing.
Exactly what I wanted...very nicely explained...if possible write step by step formulas to be used in your description
Exactly what I needed, thank you! Your explanations were very clear and understandable and made the process easy listening and learning.
Thanks Neil, after long searching on the web I finally found the answer for my searching problem.
amazing, thank you so useful after a day of searching for dynamic vlookup !!!!!
Thank you very much Neil for this very productive piece.
absolutely brilliant! many thanks - this works a treat :)
Greet , many thanks , explain every step very well
Brilliant. Solves a problem that I am currently working on. Thank you!
Very very good stuff. I have seen each of these separately but itis the combination that iis powerful!
Many thanks Neil. Articulate. Step by step.
This is just awesome, and awesomely useful, speechless, thank you.
U r best.....& also thanks for, the search down list in a multiple cell.!!!
Excellent! Regards from El Salvador
VERY USEFUL FORMULA AND VERY GOOD EXPLANATION SIR
Hi Neil! This was absolutely awesome! Fantastic job....
Hi Neil
Thanks for this video, it works great on one of my workbooks.
I do have a slight problem at the moment. You see, on one instance in this workbook it works just fine but in another it's giving me the "circular reference" error and I have followed your instructions to the T, except I'm using tables for data population. My formulas is a follows;
sheet 1 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A1)+1;0)
sheet 1 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$5;[@[Steel classifcation]]));MAX($A$1;A2)+1;0)
This works great
sheet 2 cell A2 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B2)+1;0)
sheet 2 cell A3 : =IF(ISNUMBER(SEARCH('Weld Calculation Report'!$C$10;[@Electrode]));MAX($B$1;B3)+1;0)
Cell B3 should give the value of 1 and B6 should give the value of 2 in this case, but when I auto-fill if freaks out with the circular reference and give me 0s all the way. I'm stuck. The semicolon work here as the comma does at your side.
Any help will be appreciated.
Brilliant I have been looking for this solution for ages.
Khup valo hoyeche sir
Awesome Neil. Thanks for the video. I got exactly what i wanted.
Very useful to my database now I appreciate it thank you
Hi Neil,
Thank You For the Tutorial.. it's just Awsome... Ur teaching style is really appreciatable..
Hello NEIL ;;
i really wants to thank you for this video ;;; THANK YOU , THANK YOU ;;; THANK YOUUUUUUUUU