Hi Chandoo. Great tutorial! I did the shrinking Data Validation List formula this way: =SORT(FILTER(players,COUNTIFS(D6:D11,players)=0)). So, eliminated the need for NOT() by making the TRUE condition that the COUNTIFS result = 0. Just another way to get there. Thanks for sharing your tips and tricks :)) Thumbs up!!
Thanks so much! I have been slowly decorating the home office in last 6 months and I like the way it looks now. It brings a smile on my face every time I walk in.
Super Chandoo. Today I am going to implement the first one formula in my invoice reprocessing tracker so that I won't allow duplicates data. Thanks its great help.
Great Video!! for 2nd DV also this works: =SORT(FILTER(players,ISNA(XMATCH(players,D6:D11)))) (countifs is faster, but sometimes , if more array precalculations are involved to refine a list, countifs range argument could be a problem)😉✌
Awesome trick on how to create self shrinking drop down. Thanks for the last line that made my day. I was also able to download the free copy of excel formula for myself. G8 work
You are simply rocking Mr.Chandoo. but i wonder, you are yet to do a video about Excel data entry forms and hope you will do soon.. Expecting asap. Thank you
Hello, I would like to thank you for your contributions and work on RUclips. Would you please have any other material in excel that you don't have on RUclips to share sales versus forecast accuracy? Thank you so much
Hi Rogerio... Thanks for the love and appreciation :) I have a website with over 1000 articles, tips and examples. Please visit chandoo.org/wp/ and learn :)
@@chandoo_ in second one you applied COUNTIFS very uniquely to create array of matching counts. I thinks due to dynamic array functionality, if we switch like this in XMATCH with ISNUMBER, COUNTIFS, XLOOKUP, VLOOKUP it will create array, that can directly be used in filter
Hi chandoo, i appreciate the great work you are doing I am having challenges getting the exact number of participants who have benefited from a given program, the participants can recieve more than one. however when i try to get the number of participants it goes beyond the overall number, how can you help me? Am looking forward to your response
I couldn’t say that right even for one time😆.. I am sure you also attempted more than 6 times.. would love to see the bloopers raw footage for this one..
I went back and watched the footage 🤔. Strangely, I got that on one take 😎. I even said that 3 more times (after I aske the viewers to say it 6 times), but chose to remove it in the final cut. I am surprised I didn't stutter as I am not a native speaker of English and I often mess up.
You are welcome. Please see these pages Cascading drop downs - ruclips.net/video/QPcC0WVjsTI/видео.html chandoo.org/wp/robust-dynamic-cascading-dropdowns-without-vba/
Awesome work Chandoo! Been learning a lot from you! I had a query I was struggling with, maybe you could give some advice? Is there any way to give a NOT filter in this advanced filter? Like I want to filter out values that have "*Laptop*" and "*Desktop*". I ask this because I am trying to create a complex boolean string search. For eg, ("*Dell*" OR "*Asus* NOT ("*Laptop*" OR "*Desktop*")). How do I implement such complex searches in Excel? Would love to get some help. Thanks!
Hi Chandoo, do you know if you copy a value and paste from validated range show duplicate? The formula stop duplicate only when you type same value twice.
Chandoo, May I ask a favour of you. I use the xlsb file format a lot as it make files much smaller and they are still capable of running macros. Are there any downsides to using that file format as I would imagine many millions of gigabytes could be saved if more people used it.
hello chandoo i want to make validation for emp id like Imp id 001 imp id 002 Imp id 003 and want that duplicate validation on it if i use contif(------)=1 that only work for numbers, not text with number (Imp id 001) it any video link which i can see
Hi Eli... Visit chandoo.org/wp/subscribe/ and add your email there. If you already did that, just check your email inbox / junk mail folder for an email from me with the details. Happy reading.
@@chandoo_ The formula and the reference is correct, But only when I Pick all the items in the list I get that message (#CALC), Try it please and you will understand what I mean
Sounds like a good book. I just read up about the author and looked the reviews on Amazon. I will see if our local library stocks it, else I might order it.
Once you have added the validation, you can protect the spreadsheet. Make sure the input cells are "unlocked" from format cells > protection option. This can prevent such unwanted copy paste. Another option is to train your users. Excel Data validation is not bullet proof.
You are awesome Chandoo and since long time 🙏🙏🙏
Thank you Dhananjay...
you are my first guru of excel......... Thank you sir.....
Thank you Sameer.
Hi Chandoo. Great tutorial! I did the shrinking Data Validation List formula this way: =SORT(FILTER(players,COUNTIFS(D6:D11,players)=0)). So, eliminated the need for NOT() by making the TRUE condition that the COUNTIFS result = 0. Just another way to get there. Thanks for sharing your tips and tricks :)) Thumbs up!!
Nice work! I should have used =0 check as that is easier to explain. Sometimes you miss the obvious ones. :)
@@chandoo_ Thanks :)) Cheers!!
Great tips sir! By the way, your office looks awesome! I love the way it looks.....very clean look!
Thanks so much! I have been slowly decorating the home office in last 6 months and I like the way it looks now. It brings a smile on my face every time I walk in.
YOU are awesome Chandon...thanks for your help which I use everyday.....🇲🇺🇬🇧
Thank you Sayeda...
Actually You are awesome. Thanks For all your Videos Related to Excel, Power BI and Much more about Data Analyst
Thank You Sir Chandoo for the nice tutorial and worthwhile video.
Super Chandoo.
Today I am going to implement the first one formula in my invoice reprocessing tracker so that I won't allow duplicates data.
Thanks its great help.
Great 👍
@@chandoo_ hi Chandoo,
I observed that, the technique got failed when you use copy and paste in column C.
Very creative way of putting this. thoughtful and super helpful
Thanks so much!
Your channel is amazing! You have really helped me tremendously! I am a new subscriber! You really have made this very simple for me😊
Wow , you are the one from whom I learnt dependent drop-down and now "self shrinking selection" !! Thanks a lot 🙂
My pleasure 😊
Great Video!! for 2nd DV also this works:
=SORT(FILTER(players,ISNA(XMATCH(players,D6:D11)))) (countifs is faster, but sometimes , if more array precalculations are involved to refine a list, countifs range argument could be a problem)😉✌
Thank you.. Great tip on using XMATCH...
Awesome trick on how to create self shrinking drop down. Thanks for the last line that made my day. I was also able to download the free copy of excel formula for myself. G8 work
I have just implented the second method in my worksheet. Awesome and smart 👍
Great 👍
YOU ARE DEFINITELY AWESOME CHANDOO!
😊😍
मेरे program run करने के लिए auto shrink hi ढूंढ रहा था.. Got it formula 😊
ఎక్సెల్ expert ma చందు గారు 🙏
You are really awesome sir👍
చాలా థన్యవాదాలు అండి 😀
You are simply rocking Mr.Chandoo. but i wonder, you are yet to do a video about Excel data entry forms and hope you will do soon.. Expecting asap. Thank you
Thanks Prakash... I am planning to add a video on data entry forms and some advanced uses.
@@chandoo_Thank you very much 😀
Hello, I would like to thank you for your contributions and work on RUclips. Would you please have any other material in excel that you don't have on RUclips to share sales versus forecast accuracy? Thank you so much
You can sign up for Chandoo’s email list here:chandoo.org I think there are some courses on Udemy too.
Hi Rogerio... Thanks for the love and appreciation :)
I have a website with over 1000 articles, tips and examples. Please visit chandoo.org/wp/ and learn :)
Chandoo you are bang awesome. It was really tricky to digest but finally opened your excel file and succeeded.
Glad to hear that... Yeah, the second one is tricky, but super valuable in business situations.
@@chandoo_ in second one you applied COUNTIFS very uniquely to create array of matching counts. I thinks due to dynamic array functionality, if we switch like this in XMATCH with ISNUMBER, COUNTIFS, XLOOKUP, VLOOKUP it will create array, that can directly be used in filter
yeah.. you can. Let me know what you come up with :)
@@chandoo_ sure
Super explanation about a data validation . Explanation isa awsome . , mind blowing.
Glad you liked it
Really creative video! Awesome work!
Awesome 👍 thanks for the wonderful video
Glad you enjoyed it
Thanks Chandoo 👍👍
Hi Chandoo,
Is there any way to make two different data validation for a single range of data? Thank you
Hi chandoo, i appreciate the great work you are doing
I am having challenges getting the exact number of participants who have benefited from a given program, the participants can recieve more than one. however when i try to get the number of participants it goes beyond the overall number, how can you help me?
Am looking forward to your response
I couldn’t say that right even for one time😆.. I am sure you also attempted more than 6 times.. would love to see the bloopers raw footage for this one..
I went back and watched the footage 🤔. Strangely, I got that on one take 😎. I even said that 3 more times (after I aske the viewers to say it 6 times), but chose to remove it in the final cut. I am surprised I didn't stutter as I am not a native speaker of English and I often mess up.
you are awesome..
Thanks bhayya... This is a cool content... I am new to your channel, did you already have a video on conditional drop-down list?
You are welcome. Please see these pages
Cascading drop downs - ruclips.net/video/QPcC0WVjsTI/видео.html
chandoo.org/wp/robust-dynamic-cascading-dropdowns-without-vba/
Awesome work Chandoo! Been learning a lot from you!
I had a query I was struggling with, maybe you could give some advice?
Is there any way to give a NOT filter in this advanced filter?
Like I want to filter out values that have "*Laptop*" and "*Desktop*".
I ask this because I am trying to create a complex boolean string search.
For eg, ("*Dell*" OR "*Asus* NOT ("*Laptop*" OR "*Desktop*")). How do I implement such complex searches in Excel? Would love to get some help.
Thanks!
Thanks Abhishek.. I will have to research this. Let me comeback to you.
Your all videos are excellent 👍
Thank you so much 😀
Just wondering. How come you don't have a million subscribers already?
Awesome Anna !
Hi Chandoo, do you know if you copy a value and paste from validated range show duplicate? The formula stop duplicate only when you type same value twice.
Great! Thank you.
You are welcome!
No! Not me. You are Awesome, Chandoo! :)
😊😀
🤯🤯🤯 - you're a legend!!
😎😍
Excellent
😍
Great think
What excell version you are using?
so useful
Thank you so much Sir
Most welcome
Chandoo, May I ask a favour of you. I use the xlsb file format a lot as it make files much smaller and they are still capable of running macros. Are there any downsides to using that file format as I would imagine many millions of gigabytes could be saved if more people used it.
Hi Barry... There is nothing wrong with XLSB files as such. I use them too when I need to add a macro or save on file size.
Actually you are Awesome
😊😍
Hi, i am also wanted to be small youtuber like this.. Can u tell how the video gets zoomed to focus while u do some action in cells??
Hi Aneesh... I am using Camtasia to do my screen recordings. You can download it from techsmith website.
@@chandoo_ thanks for your reply.
does this work when Microsoft forms are automatically inputted into excel online? or is this only for manual entries
With forms, you can't set up data validation rules like this. But forms have their own "validation rules" and "branching criteria". So try that.
@@chandoo_ if you could assist I would really appreciate it. All of the sudden Microsoft forms entries have started duplicating in excel online.
Awesome
Thank you
You're welcome
hello chandoo
i want to make validation for emp id like
Imp id 001
imp id 002
Imp id 003
and want that duplicate validation on it
if i use contif(------)=1
that only work for numbers, not text with number (Imp id 001) it any video link which i can see
Hello! I’ve been subscribed but I didn’t get the book 😔
Hi Eli... Visit chandoo.org/wp/subscribe/ and add your email there. If you already did that, just check your email inbox / junk mail folder for an email from me with the details. Happy reading.
This's Perfect
Thank you...
Just Wow!
Thank you Vinod...
Thank You
What if I pick all the List Item
I get A #CALC in the first cell on the list, How I can mange this
If you use a formula or incorrect reference in the data validation, you get #CALC! error. Check the cell values and fix that.
@@chandoo_ The formula and the reference is correct, But only when I Pick all the items in the list I get that message (#CALC), Try it please and you will understand what I mean
Hi chandoo,
Instead of typing a duplicate number ,if we drag the above number then this data validation will not work
This is a limitation of Excel. Any data validation rules are not checked when dragging or copying values.
Thank you chandoo . I am big fan of your teaching method
Sir create vedios on Web excel plz
Thanks Rashid.. I will include some examples on web excel in future videos.
thanks
You're welcome!
Speaking of Awesome: Here's a great book: You Are Awesome: How to Navigate Change, Wrestle with Failure, and Live an Intentional Life by Neil Pasricha
Sounds like a good book. I just read up about the author and looked the reviews on Amazon. I will see if our local library stocks it, else I might order it.
3:05 😂 do you have any formula for tounge twisters.
Just use REPT("Self shrinking selection set ", 6) and Speakcells...
If we paste black cell in data validation cell then data validation remove, how to fix data validation cell....?
Once you have added the validation, you can protect the spreadsheet. Make sure the input cells are "unlocked" from format cells > protection option. This can prevent such unwanted copy paste. Another option is to train your users. Excel Data validation is not bullet proof.
@@chandoo_ thanks for Replay, I have only second option, because my staff paste from different sheet also.
you too awesome chandoo (ɔ◔‿◔)ɔ♥
Hahahah... "Dead simple".
😆😆😆