Excel Magic Trick 916: Remove Blanks In Data Validation Drop Down List 5 Examples
HTML-код
- Опубликовано: 4 сен 2024
- Download Excel Start File: people.highlin...
Download workbook: people.highline...
3 examples of how to Remove Blanks In Data Validation Drop Down List 5 Examples:
1. Learn that "Ignore Blanks" Check Box does not remove blanks from list, but instead it avoids an error message when cell is empty
2. Remove Blanks from data set using: GoTo (F5), Special Button, Blanks, OK button, Right-Click and Delete Cells, Shift Up
3. Array Formula To Extract Data for Non-Empty Cells using the functions IF, ROWS, COUNTA, INDEX, SMALL, ROW and Comparative Operator NOT
4. Array Formula To Extract Data for Non-Empty Cells using the functions IF, ROWS, COUNTA, INDEX, AGGREGATE, ROW and Comparative Operator NOT
5. Create Dynamic Range Defined Name using INDEX and COUNTIF and the "One Or More Character" Criteria "?*"
Never had though it would be this complex to get something that simple. Thanks!
You are welcome! I am happy that RUclips and Excel can let us connect 1/2 a world away!
This is a great tutorial, it blew my mind. You look at the formulas themselves and they seem very complex, then you watch the video and it gets so simple. Hats off!
Glad it helps!
This has been very helpful. Thanks a lot. I liked your teaching style. ESCAPE.
You are welcome!
Thank you very much, hamy72! Coming from an Excel master like yourself, that is quite a compliment. This video was actually an answer to a question I got in my Excel class at Highline Community College, and in this class we had not covered array formulas yet, and so I had to try and explain it in a way so that the student could (hopefully) understand!
Mr.Mike million thanks for this video... i used ur trick 358 and 359 to develop a report for my project . really i was in need of this formula.... hats off to u ... :)
This is my first ever comment on RUclips ... I just wanted to say thanks for all your videos. You have a natural talent for teaching as well as Excel. I live on the other side of the world and your videos are a huge help. Thanks again for these videos.
Man you are Excel God!!. This video saved me 100s of hours. Thank you so much for creating this. Your explanation is so simple and easy to understand
I am in Awe of how You develop the formula step-by-step. You make it look so intuitive, organic. I am trying to Learn it.
Thank You!
You are welcome, Ankur!!!!
I am glad that you like it! I first say it from Aladin at the Mr Excel Message Board!
Absolutely amazing Mike. 👏
Used this for a program I'm working on that checks dimensional sizes of products against existing products in different metal alloys, so lots of blank fields are in the lists as the vast majority of alloys are rarely used. GUI sheet looks SO much cleaner after cleaning up it up with this! Many thanks!!!
Thank you very much! You are the most amazing Excel teacher in the world. Admire your work so much!
God..that was some one year course stuck into 23 minutes and 57 seconds....GREAT EXPLANATION MAN!!
Great tutorial. I have no word to say thanks really great. Please share the tutorial for Google sheets
I really enjoyed this video and formula. I'm writing some VBA code to automatically create the named ranges via a macro. The problem with the existing formula is that the double quotes get messed up on the VBA side. So I've made the following change, which gives the same result, as long as the blanks are double quotes and not truly empty.
Replace COUNTIF(range,"?*") with SUM(COUNTA(range),-COUNTBLANK(range)). The COUNTBLANK counts empty cells whether NULL or "" and subtracts from COUNTA.
I'm 11 years later but thank you so much, that fixed my problem!
Thank you very much.
This is so helpful.
Greetings from the Philippines!
New subscriber here😅🎉
Thank you, I don't think we could have solved our problem without this help. Going back to Trick 1 and will work forward.
Thanks Mike, this is exactly what i need to do but it only worked on the first row for me.
I'm so glad to find this video...I've been searching the web for almost half a day just to achieve this kind of drop-down list. Thank you so much...
Glad to help : )
This is AMAZING. You sir, are not only an excel genius - but you are incredibly entertaining!!!
Solution still works a treat - thanks!
You are welcome!
I used to use dynamic column by using the offset function and count function and it is work , this lesson i got also from your youtube you are a genius .
doosara2000 , you are welcome! Those particular videos do teach the fundamental skill of how to extract data with a formula, which seems to be such a common task in our day and age.
You are a genius. Thanks a lot!!!
You are the MAN!! This step by step with explanation is perfect! Also very entertaining
Cool! You are quite good with Excel!
Beautifully well explained, best solution I have found to this issue by far!
Thank you! This has made my life so much easier!
You are welcome!
Fantastic Tutorial, just what I needed to solve a current problem. Great work.
absolutely brilliant. Thank you
Excellent tip. Thanks!
Glad you like it, Paolo!!!!
Thank you very much! It helped a lot to me.
Thank you VERY much for the donation: it really helps : ) : ) : )
@@excelisfun 🤣 sorry man, thats all i can donate for now, i am trying to help everyone a little
It is soooo good: trying to help everyone a little. Go Team!!!!
You just saved my life! Your explanation is what I had been looking for all day long, and it was worth all the journey. Thank you, man :')
Glad it helped!
Your videos are so helpfull!!!!
Glad they help, Alex!! Thanks for the support with your comment, thumbs up and of course your Sub : )
That reference range formula for a named cell is exactly what i was looking for. Tried so many videos before this one!
Thank you for all the effort Mike you put into this tutorial explaining how to extract data for DV. I used formula from EMT 759 to extract and sort all the names.:)
Thanks sir very helpful video
Excellent tutor with narration!
Good formula !!!
THANK YOU! Exactly what I've been looking for!
Totally great solution!!!!
I love it!!
This is really insanely cool!!!!
Epic tutorial!
The start of your video gave me an idea and I hit pause to see what my solution would be compared to yours and it turns out Peter ten Thije and I had the exact idea. I love the ExcellsFun videos. Great Great Stuff....
Thanks a lot for all of these, helped me a lot, you are a very good instructor!!
I can not thank you enough, a lot of times you helped me.
You're the best
Really lovely... Well explained and saved my day!
I appreciate you adding this video. I found it helpful. I was able to solve the issue I had in Excel. Thanks again for taking the time to make the video and share.
You are welcome! I have 1000s of other videos with useful tips. Thanks for the support, Robert, with your comment, Thumbs Up and Sub : )
Thank you Sir, please keep it up.
Glad it helps!!!!
Love it. Thank you. You really know how to teach very well.
Glad you like it, AllClean Services!
Cheers mate, took me a while to take it in but got it working perfectly, life saver!
I still have not uploaded this to the web site, but I will do it on Monday May 14
Holy shnikes! This was awesome! I've been searching for months how to do this with some study material calculations I've been working on, Thanks so much!
Thank you, VERY helpful. Good explanations of "why and how"
You are welcome for the "why and how"!!!
I couldn't get it to work, but downloading your file seems to have done the trick. Thank you. Now how do I get rid of the duplicates?
Awesome!
Very helpful, especially because mine is dynamic and I also add some extra step for automatic sorting because I can not sort the data from the source
Thanks a lot.. you are my saviour. Been looking for this logic since ages. this will be of a great help to me. Cheers n keep up .
excell, connecting people , like it soo much , thanks for, share
thanks""
i was waiting for the same formula
thanks again
Nicely explained like Leila
Great lesson!. Thank you!!!
Wow, i can't Explain how amused i'm when watching your lesson! what a genius in handling the technical side & instructing in a wonderful way at the same time!
if i may ask, how do i remove something in a drop down list once it has been chosen! for example, i have 200 titles blocked to each employee. once a specific title is chosen for an employee it gets omitted from the (drop down list)! is there a way apart from using VB codes?! i can send you my file to clarify the idea! thanks alot.
?* what a radical use of the wildcards. Love it.
nice guide, but note that if the lookup list is empty , the dropdown will show all the blank cell in range.
Great video, this is EXACTLY what I was looking for. But for some reason I am getting a warning that says: "You may not use reference operators (such as unions, intersections, and ranges) or array constants for Data Validation criteria." Please help.
Glad to know the solutions Mister, but why it just doesn't work when i make a list in data validation, it said "the source currently evaluate to an error. Do You want to continue?" , i click continueand the list in the drop down list are not shown. i followed the step.. but it wont appear in the drop down list
same lmao
What about the case where you don't know where the last non-blank cell is; that is, how can the range be volatile, or does one need to set the column as A:A, or A1:A10000000?
AWESOME
This was a GREAT tutorial. Now for the kicker, LOL... Can I use this with another conditional drop down? Example using your spreadsheet: If there was another drop down (G5) that gave the choice of Boys or Girls. Once I select boys...THEN I need it to access your drop down information at cell F5. I tried referencing it by using in the validation =indirect(G5) but it would not work at all. Thoughts, ideas, suggestions? Thank you.
Hi there! Great video, very insightful. What if I want to create a list on a sheet but the data is spread across multiple sheets? How would the formulae change?
Fantasitic! this was super helpful in part of my workbook. Question: Is it possible to do this instead of with single cells, do it with groups of cells? Say you've got a small list with a header, then a few spaces, and another small list with a header. Is it possible to alter this formula to remove the spaces between the small lists?
amazing
There is a simpler solution, though you need one additional column for calculation.
Include a new column in front of column A. In (new) cell A2 type 1. In (new) cell A3 type =IF(B3="";A2;A2+1). Copy that formula down to the end of the data range.
In column C you type the numbers 1/2/3/4/ etc. You could do this through a formula, in that case I would suggest including a ceiling. This ceiling can be determined by using a MAX formula over column A.
Then in column D a VLOOKUP will do the trick.
isn't Vlookup inflexible when data gets added to tables and content isn't fixed? or is that a different one, I am struggling with data being copied with blanks and been trying for months to find solutions
I know this is an old video, but a quick question why does it not seem to work with a =Indirect(DName) in the valuation list?
Thanks Mike
So excellent. Thank you very much!
That was a great tutorial indeed. What if we're working with columns instead?
Hello Mike, instead of all that IF(ROWS(C$2:C2)>COUNTA($A122:$A$12,"" to prevent the #NUM error, why not use the IFERROR?
Hi, Mike, this is an awesome tutorial, thank you a lot !!!
Could you give me an advice...
If I need to remove blanks as you've sowed, but only that, that respects a condition... for example, to show just the entries that are no blank, values in column E > values in column F.
I have no Idea how to insert this condition into the array formula you've teached.
Thank you in advance
Mike, I can't find the workbook to download. Did you not upload it yet? Thx, Gary
EXCELLENT thank you sir
Awesome tutorial! Now here's my challenge, I need to pull a Hyperlink from the Data table.
Any suggestions?
it helped a lot, the only problem is that i cant use the equation "indirect" in the end. i am using a excel 2010 and a error mensage apears. So if i direct use the name of the list it works. but when i try to use indirect do a cell with the name of the list it dosent =/
I want to create a drop down list that is dependent on the values of two cells in the same column. It is always the same 2 cells, but their values change and there are always 7 blank cells between them. Do you have an idea of how I should proceed?
I see them and can download them. I am sorry but I can not determine what is wrong.
Thank you! Amazing!
I'm not sure if you've noticed this, but if you try to use this trick more than once in a workbook it continually changes previously named ranges into the most current. Meaning, the references in all of the formulae convert to the one last entered. . . I'm guessing it's just a glitch, but I was wondering, would you have any suggestions on how to fix this? For the time being I'll have to go in and manually change the references which is tedious, to say the least. . . Maybe you could save me some time in the future? Thanks for your consideration.
Hey yo, first of all thank you for this video, it helped a lot. I have a question: How do I do multiple dropdown list that based on previous selections? I thought I could use this method to define the range in Name Manager, but when I choose it in Data Validation using indirect function, it says "The source currently evaluates to an error. Do you want to continue?" How do I fix this?
Thanks for this awesome formula. Is there a way to get this to work if the rows are hyperlinks? I tried some different variations but can´t get that to work.
I found that if you only have blanks at the end of your array that you don't want to show up in the data validation drop-down then all you need is the last part of the solution, the dynamic range. Also, if your data is numeric instead of alphabetic then the COUNTA won't work, you need just the COUNT function.
Regarding the last part of the video, the Dynamic Range for the list. Is that formula supposed to also work in Excel 2007? I am using the file provided but the drop down list shows only blanks. When I look at the formula in C-17, it also resolved to a blank cell.
What an explanation, Bingo
Awesome man very informative, thanks. Got a lot of useful information out of this one.
You are welcome! Thanks for the support with your comment, Thumbs Up and Sub : )
I have a question, the list I have is created from another sheet called stock, and the list is created depending on an if statement, creating a list of items that are in stock. Your examples above work fine on my excel 2010 version but I also want the sheet work on versions 2003 up to current. The first example works fine in 2003 as long as it's a list I have created from pure text. But it wont work if the list is created with an if statement. Any ideas how to get it to work?
Great video. I'm having trouble with my drop down list. I'm trying to create a drop down list from a horizontal range. All the titles in the horizontal range are merged cells. How can I create a drop down list from a horizontal range with the cell titles merged?
Hey ExcellsFun, how do you make it so dropdown cells only show if a referring cell meets a specific criteria? So it should be blank otherwise
I've been searching a long time for this solution. This solves a big mess for me. Thanks. I've a question. My data is all words and no numbers. If I add a word to my primary list, how can I get the output alphabetical without rearranging the primary list?