The seed is planted. Never know when one needs another card from your sleeve. Clipboard thing, first time i see it, will try soon, looks very useful. Thank you Leila, always a pleasure.
Hi Leila, Thanks for the good video again. Thanks for that I have been enjoyed watching it again. Just one little thing, if we didn't change any thing in a dialog box, like the Data Validation dialog box, it is better much to click the 'Cancel' button to close the window/dialog box than to click the 'Ok' button. The reason being is that when 'Ok' is clicked, it would trigger a whole string of programming functions/classes to be called to complete that action, while 'Cancel' button would not trigger any of those. Of course, on the surface, it looks the same but it is not in terms of using system resources and efficiency. Once again enjoyed your video as always. Please keep up with the good work Leila.
3rd condition I did it myself without watching the video, though it took long time. I failed many times but eventually I figured it out. Little sense of accomplishment but definitely I enjoyed it a lot. Thanks Leila, you are awesome.
Lovely..and very useful. I prefer key-board short-cut ALT+D+L and then TAB or SHIFT+TAB to move around in data validation dialogue box. For those who are not well-versed with clip board; they can simply hit a space bar at the beginning of the partial function (so that their efforts are not lost) and then continue with the next leg. After completing individual functions combine them step-by-step in AND function. Thanks for upload Leila :)
Hi Leila, Great video as always. When I first saw you using the Value function to convert text to numeric I was reminded of Mike Girvin's videos where he used a double negative (- -) to do the trick. I looked at your comments from other views and saw this was option was already suggested a few days ago. Another thought that came to mind was to use named ranges (or named range) in place of the actual formulas that would be entered into the custom box. This would save real estate in the data validation box but it demonstrates how you could say the same thing in Excel (get the same results) but just in another way.. The more I watch videos like yours, Bill Jelen and Mike Girvin I'm becoming more and more convinced Excel is an language unto itself. If you think about it, in Excel you do have 5 or 6 languages that exist presently (e.g. formulas, custom formatting, the M language, Dax, VBA, ASCII, etc). Would it be possible to create a series of videos that covers all aspects of data Validation? I normally only use the list box feature and I think I might be missing out on other functionality it can provide in my spreadsheets. I also wonder if you could create a video on actual spreadsheet design. I'm always floored by your Excel files in the manner in which you set them up. They look so neat, clean and organized. Not all cluttered like my spreadsheet tend to be (with too many colors, etc). Michael
Hi Michael - thank you for your comment. In the validation basics video, I cover the other aspects: th-cam.com/video/FRiFfKb_B_A/w-d-xo.html - do you mean this? :) - I always wanted to do a course / videos on spreadsheet design - I have to think about that....maybe some case studies here would be great...
@@LeilaGharani Sirji i've created data validation list in excel sheet, the list is appear on my laptop, but i copy the same file on my pc the tag which is not display ! pl told me solution for appearing this list ! If any setting in excel, pl told me ! Your guidance is my learning point ! Mam pl reply
Great explanation! I just used it for 00/0000. 2 numbers "/" 4 numbers =AND(LEN(A1)=7,ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE((RIGHT(A1,4)))))
Nice presentation of many text functions. But I really liked the clipboard functionality. In never used it, but I will give it a try. Very useful for nesting formulas.
Thnx 4 this usefull developer tip to split the validation in simple steps and combine it via the clipboard since the validation window is so hiddious for editing :-|
Just wonderful. So happy there are people like you on TH-cam. Is there a way to add validation for specific number of decimal places allowed in a cell?
Hi Leila, love your video. I would like to ask if Data Validation can be applied on a cell with formula. Example, I want to apply a data validation on A1, which has a formula=Sum(B1:D1), I want to restrict the value in A1 to be less than or equal to 10 with data validation. If not, then message will pop up.
5:30 What about symbols, that also would be non-numeric but would break the Data Validation? Would you have to use Unicode or is there a more elegant way to deal with it.
Thanks Leila. Love all your work! Is there a way to lock the range if the number had reach certain amount. For example I got a list, then after 100 the people can't put their name/tick/put 1. Thanks
Hi Lil. I'm glad you like the tutorials. Thank you for your support of my courses. Do you mean the filter functionality like this? th-cam.com/video/VqQACB_69SQ/w-d-xo.html . I cover copy and pasting of visible cells inside the course as well - specially when we cover the 2nd milestone of the course where we build a regional sales tool.
Leila Gharani yes just like that but instead of typing the criteria it is in the form of a drop down. See the first few seconds of this th-cam.com/video/pCAXRLYr59E/w-d-xo.html Anyway I hope everyone buys your VBA course. It is THAT good. Can't wait for more!
Hi Leila, I used a data validation to proctite formula's in a row. Then I wanted to clear dat data (remove the validation). It did not work. It asked me to enter a formula.... weird because it did not allow me to go to the settings window in the first place. I did not save my work and started again.... Any idea?
When I used data validation have criteria as data And type date manually in those cell it work perfectly But when I copy paste the date from some were else that data validation of date does not work and it accept the value and does not show error That the date u have enter is restricted by user How to fix that BCS
Great video. You explain things well enough for your viewers to experiment and learn rater than just copying exactly what you're doing. I was playing around with the first example and found the first character could be a punctuation character and still pass validation. My solution was to replace the "=NOT(ISNUMBER..." argument with "=OR(LEFT(A5,1)="A",LEFT(A5,1)="B",LEFT(A5,1)="C")...LEFT(A5,1)="Z"). This worked, but, it seems like there should be an easier way to enforce the first character is a letter. How would you suggest handling this?
Hi Phillip. Thank you! Yes - you're right, anything that's not seen as a number is considered text for the 1st character. One way I can think of restricting it to the alphabet would be to use this formula ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")) so the entire formula would now be: =AND(LEN(A5)=5,ISNUMBER(VALUE(RIGHT(A5,4))),ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
Hi Leila, Thank you for another great video! I am trying to use the VLOOKUP formula in the custom data validation and excel is not accepting it. Can you please suggest a workaround? Your help is highly appreciated!
Hi Ma'am if I put a formula in a cell and I get the result is correct then showing 1,2,3,4,5 and if the result is not correct then Showing #N/A or 0 now I Want to put the data validation base on my result but I don't want to remove my formula is it possible ? if Possible then how can plz help
Stay ahead with our weekly newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
The seed is planted. Never know when one needs another card from your sleeve. Clipboard thing, first time i see it, will try soon, looks very useful. Thank you Leila, always a pleasure.
That's great! I'm glad you found something new here. Thank you for your support.
Love the way that you just broke down in small manageable chunks an excel problem.
Like the use of the clipboard too, great tip!
Thank you Darren. I'm glad you like the presentation. Yes - the clipboard is great. It works for Word and PowerPoint too.
Hi Leila,
Thanks for the good video again. Thanks for that I have been enjoyed watching it again.
Just one little thing, if we didn't change any thing in a dialog box, like the Data Validation dialog box, it is better much to click the 'Cancel' button to close the window/dialog box than to click the 'Ok' button. The reason being is that when 'Ok' is clicked, it would trigger a whole string of programming functions/classes to be called to complete that action, while 'Cancel' button would not trigger any of those. Of course, on the surface, it looks the same but it is not in terms of using system resources and efficiency.
Once again enjoyed your video as always. Please keep up with the good work Leila.
3rd condition I did it myself without watching the video, though it took long time. I failed many times but eventually I figured it out. Little sense of accomplishment but definitely I enjoyed it a lot. Thanks Leila, you are awesome.
Greatest tutorial about data validation, Thank you very much for your effort!!!
Full of value! As always! Will never see Data Validation the same way!
Glad it is helpful! It can be a powerful tool.
Lovely..and very useful.
I prefer key-board short-cut ALT+D+L and then TAB or SHIFT+TAB to move around in data validation dialogue box. For those who are not well-versed with clip board; they can simply hit a space bar at the beginning of the partial function (so that their efforts are not lost) and then continue with the next leg. After completing individual functions combine them step-by-step in AND function. Thanks for upload Leila :)
Thank you Sachin for the pointers and the shortcut keys :)
Hi Leila,
Great video as always.
When I first saw you using the Value function to convert text to numeric I was reminded of Mike Girvin's videos where he used a double negative (- -) to do the trick. I looked at your comments from other views and saw this was option was already suggested a few days ago.
Another thought that came to mind was to use named ranges (or named range) in place of the actual formulas that would be entered into the custom box. This would save real estate in the data validation box but it demonstrates how you could say the same thing in Excel (get the same results) but just in another way..
The more I watch videos like yours, Bill Jelen and Mike Girvin I'm becoming more and more convinced Excel is an language unto itself. If you think about it, in Excel you do have 5 or 6 languages that exist presently (e.g. formulas, custom formatting, the M language, Dax, VBA, ASCII, etc).
Would it be possible to create a series of videos that covers all aspects of data Validation? I normally only use the list box feature and I think I might be missing out on other functionality it can provide in my spreadsheets.
I also wonder if you could create a video on actual spreadsheet design. I'm always floored by your Excel files in the manner in which you set them up. They look so neat, clean and organized. Not all cluttered like my spreadsheet tend to be (with too many colors, etc).
Michael
Hi Michael - thank you for your comment. In the validation basics video, I cover the other aspects: th-cam.com/video/FRiFfKb_B_A/w-d-xo.html - do you mean this?
:) - I always wanted to do a course / videos on spreadsheet design - I have to think about that....maybe some case studies here would be great...
This is the best I have ever seen thank you very very much, I recommended this for my students
And I thought I know data validation quite well...but this something different and innovative..good work Leila
Hi Leila, You speech is very good and your English also very understandable..
thank you for your uploading valuable videos..
Thank you for your support. I'm glad you find the videos useful :)
@@LeilaGharani very useful ma
Thank you. Your tutorial guides me thru writing the custom DV formula.
Big thank-you, Leila! Superb channel on TH-cam.
Very happy you like the channel. Thank you for your support.
That was great...a big thumbs up!...exposing and using the clipboard that way was really great too.
Excellent.. as usual.. Great learning. Thanks for such a nice tutorial.
All of your videos are very useful, Leila.
Glad to hear that, Karen!
In your first example, a code like ";1234" would be valid. You should use a function like =AND(CODE(UCASE(letra))>=65,CODE(UCASE(letra))
i guess you also start some tips lessons.
Manuel, your example inspired me to try using the letter values more explicitly: =(LEFT(A5,1)>="A")*(LEFT(A5,1)
as always you're method of teaching is very awesome. thanks a lot for sharing your knowledge.
Thank you Mohammad and also for your support.
Really helpful step by step tutorial. Thanks a lot.
My pleasure, glad to help.
Every time your tutorial get me some useful ideas ! Thanks a lot !
I like that :) You're very welcome.
@@LeilaGharani Sirji i've created data validation list in excel sheet, the list is appear on my laptop, but i copy the same file on my pc the tag which is not display ! pl told me solution for appearing this list !
If any setting in excel, pl told me ! Your guidance is my learning point ! Mam pl reply
Leila always sorts me with Excel challenges
Thanks ma'am...its always worth watching your video.
You're very welcome Mohit :)
Ma, can I say you're awesome you seem to have solution to every excel problem I encounter. 👍 👍 👍
Waw mind blowing. This is the most awaited Data Validation formula which I am writing for this.
Thank you for this @Leila
You're very welcome :)
Found this really helpful. Thx.
you make all of this seem sooooo easy. thank you!!
Leila Gharani, Magnificent presentation. I don't see much of your VBA.
Hi - Thank you. Last week was VBA :) - I made a VBA playlist, ,make sure you check it out. I'm altering Thursday videos for now between Excel and VBA.
Thanks a lot for this informative video!
As always excellent step by step explanation
Thank you Vida.
hats off to your training - wow
Super great for Excel validation.
Just another great video!
You made me like Excel 👍.
Love you mam, you helped me a lot... I keep on watching your teaching...
It's my pleasure
Hi Leila...you are awesome in Excel and you are a great explanator...thanks for your handy tricks and tips...
It is videos of this type i always keep an eye on your channel... Thanks for ur beautiful video
Thank you! I'll make sure to add in more of these.
Great explanation! I just used it for 00/0000. 2 numbers "/" 4 numbers
=AND(LEN(A1)=7,ISNUMBER(VALUE(LEFT(A1,2))),MID(A1,3,1)="/",ISNUMBER(VALUE((RIGHT(A1,4)))))
Hi can you help me for format XX 000 000 its really urgent
i really appreciate your effort and wanna say thank you for this very informative and useful tutorial.
You're very welcome Abhishek!
Nice presentation of many text functions. But I really liked the clipboard functionality. In never used it, but I will give it a try. Very useful for nesting formulas.
Thank you Bart. Agree - that's a great functionality. Not just for Excel but for the other office applications as well :)
Yes, I have been doing this for 25 years, and I never saw that. Very clever.
That didn't work for me... I'm using Excel 2021
you are brilliant,amazing,fabioulus
FANTASTIC! THANKS!!!
Thank you so much! 🥰
You’re welcome 😊
Wow.. that's best...Leila
..
Nice.. thanks for toturial... 👍
Amazing. I didn't know that before this video. Thanks for good content
Wow .. amazing tutorial
Thank you! very useful & very intersting
You're very welcome Chaminda.
Very usefull this video, thanks for sharing
You're very welcome Miguel. Thank you for your support.
Good learning today
Happy to hear that :)
Nicely done!
Thank you Brandon
Exactly what I needed to know! Thank you so much!!
Great tip with Ctrl+C+C! 🙏 🙌🙌🙌
Thnx 4 this usefull developer tip to split the validation in simple steps and combine it via the clipboard since the validation window is so hiddious for editing :-|
Nice Vedio Leila....thank you so much for explaining very well 👍👍💐
You're very welcome. Glad you find the explanations easy to follow.
this one is superb. lovely
Very nice 👌 validation,
Thanks Leila. This was very interesting!!! :) :)
You're very welcome John :)
Just wonderful. So happy there are people like you on TH-cam. Is there a way to add validation for specific number of decimal places allowed in a cell?
This session is helpful, is it possible to get expand & collapse option in data validation list of we have multiple header & subgroup
Hi Leila, love your video. I would like to ask if Data Validation can be applied on a cell with formula. Example, I want to apply a data validation on A1, which has a formula=Sum(B1:D1), I want to restrict the value in A1 to be less than or equal to 10 with data validation. If not, then message will pop up.
good video
Excel-lent examples for the custom validation!
Thank you very much Doug.
Gud presentation.. Keep it up..
thanks for the insight.....very helpful....got u!!
Glad you like it Samuel.
Extraordinary Madam, Great
how can we conect different sheet that we can write data on first and if shifted on the rest of the sheets ?
Can this validation rules be applied on a table?
very helpful
Can i run data validation with past data?
5:30 What about symbols, that also would be non-numeric but would break the Data Validation?
Would you have to use Unicode or is there a more elegant way to deal with it.
I like this but do you have a tutorial using VBA to check values entered into column matches validation list? Thank you for your support
can you tell me where can i use this?
Great leason Leila, thank you! How can you prevent special characters using data validation?
You can probably use the ASCII function to check if input is greater than or less than the numeric ASCII char value.
mic drop! Brava! 👍
Is there a way to have it show a blank based off another cell and when that cell has a input it changes to the rest of the selection
Thank You
You are best 👍 is it possible to give reference in formal of other sheets? It's not letting me.
thank you)*(thank you) ** 1000 for all thinks you do it queen)
You're very welcome. Thank you *1000 for your support of my channel.
I am just a Fan of you
Really appreciate your support! Thank you.
Its great lesson Leila, thank you!, But, I looking to do same thing using VBA.
Data mining is such a pain. It has to do with lack of foresight in planning.
Thanks for teaching an old dog new tricks.
Thanks Leila. Love all your work! Is there a way to lock the range if the number had reach certain amount. For example I got a list, then after 100 the people can't put their name/tick/put 1. Thanks
Thanks. Alot!
Thank you very much I was just stuck at one place
And u slove that by "not'
You're very welcome. Glad the "not" solved that.
Thanks u so much
Keep it simple:
AND("A0001"
How do you use data validation to calculate sum total of multiple cells based on whichever drop down option you select?
You are beautiful and inteligent at the same. Thank you so much
Thanks madam nice one
You're very welcome.
Does it work for symbol?
Hello, nice tuts. Bought ur VBA tuts. Amazing. Can you do a tutorial on how to use a drop down to extract/filter out a set of Data
Hi Lil. I'm glad you like the tutorials. Thank you for your support of my courses. Do you mean the filter functionality like this? th-cam.com/video/VqQACB_69SQ/w-d-xo.html . I cover copy and pasting of visible cells inside the course as well - specially when we cover the 2nd milestone of the course where we build a regional sales tool.
Leila Gharani yes just like that but instead of typing the criteria it is in the form of a drop down. See the first few seconds of this th-cam.com/video/pCAXRLYr59E/w-d-xo.html
Anyway I hope everyone buys your VBA course. It is THAT good. Can't wait for more!
lil chill
You can make dropdown of your creteria
(It's simple )
Hi Leila, I used a data validation to proctite formula's in a row. Then I wanted to clear dat data (remove the validation). It did not work. It asked me to enter a formula.... weird because it did not allow me to go to the settings window in the first place. I did not save my work and started again.... Any idea?
Hi Leila can you make a videos of ribbons. I would like to see videos on each and every tab in detail (one tab one video).
I added it to my list. Thanks for your suggestion.
@@LeilaGharani welcome
Hello, how to validate if we have simbol. Example P01-DP01?
Thanks
Where are you from
When I used data validation have criteria as data
And type date manually in those cell it work perfectly
But when I copy paste the date from some were else that data validation of date does not work and it accept the value and does not show error
That the date u have enter is restricted by user
How to fix that BCS
Great video. You explain things well enough for your viewers to experiment and learn rater than just copying exactly what you're doing. I was playing around with the first example and found the first character could be a punctuation character and still pass validation. My solution was to replace the "=NOT(ISNUMBER..." argument with "=OR(LEFT(A5,1)="A",LEFT(A5,1)="B",LEFT(A5,1)="C")...LEFT(A5,1)="Z"). This worked, but, it seems like there should be an easier way to enforce the first character is a letter. How would you suggest handling this?
Hi Phillip. Thank you! Yes - you're right, anything that's not seen as a number is considered text for the 1st character. One way I can think of restricting it to the alphabet would be to use this formula ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")) so the entire formula would now be:
=AND(LEN(A5)=5,ISNUMBER(VALUE(RIGHT(A5,4))),ISNUMBER(SEARCH(LEFT(A5,1),"ABCDEFGHIJKLMNOPQRSTUVWXYZ")))
Leila Gharani Thanks.
@@LeilaGharani Well done Leila!
Looks like we definitely need a new built-in function called isalphabet() 😊
Hi Leila,
Thank you for another great video!
I am trying to use the VLOOKUP formula in the custom data validation and excel is not accepting it. Can you please suggest a workaround? Your help is highly appreciated!
HeY,
I also tried using Match and Index function but encountering the same problem again. Please let me know.
Hi Ma'am if I put a formula in a cell and I get the result is correct then showing 1,2,3,4,5 and if the result is not correct then Showing #N/A or 0
now I Want to put the data validation base on my result but I don't want to remove my formula is it possible ? if Possible then how can plz help