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: ua-cam.com/video/FRiFfKb_B_A/v-deo.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)))))
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.
Just wonderful. So happy there are people like you on UA-cam. Is there a way to add validation for specific number of decimal places allowed in a cell?
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.
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")))
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 :-|
What if the data validation cell is initially empty? I tried this method and the individual formulas return T/F as necessary, but data validation returns no error. I didn't know there were excel gurus like you out there. Lets me know that I know nothing. I appreciate you videos
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? ua-cam.com/video/VqQACB_69SQ/v-deo.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 ua-cam.com/video/pCAXRLYr59E/v-deo.html Anyway I hope everyone buys your VBA course. It is THAT good. Can't wait for more!
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
I have a complex workbook that multiple people input data and the file is shared on SharePoint. I’ve noticed that the data validation settings do not function if they open the workbook in the browser but the validation will work if “open in app” is selected. I’ve encouraged everyone to open in the app but they’re not as a compliant as I’d like! Are there settings I am missing that will allow the data validation to work either way? Or, is there a setting that requires a workbook to be opened in the app rather than the browser? Thanks for you help!
Hello Leila, could you do a video on how to create a function to validate MAC addresses using Custom Data Validation or VBA? A MAC address is written as XX:XX:XX:XX:XX:XX where X can only be numbers 0-9 and the letters A-F. Thanks.
hi.....thx for your helpful tutorial....i have a case hear need for your help on it .....if i have 2 cells have different Data validation ....for Example: A cell (A4) with data valid are characters (W,X,Y,Z)"Drop list", and on-other hand cell (B4) with data valid are characters(A,NA,NAH,ARS)............i need to make data in Cell (B4) as i mentioned above, BUT with a condition.....that the characters(A,NA,NAH) are only valid when the characters at cell (A4) are (Y)Or (Z) only, and others case free from condensations....Thankful for your valuable time :)
Hi, is there way to do data validation (error message) for the cell that containing formula based on the end result? I see data validation works only on cell where the input is manual. Is it possible to do on the cell which doesn't has manual entry that is cell result of some formula.
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!
Stay ahead with our weekly newsletter. Get the latest insights and updates straight to your inbox 👉 link.xelplus.com/yt-c-newsletter
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.
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.
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!!!
Leila always sorts me with Excel challenges
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)
Ma, can I say you're awesome you seem to have solution to every excel problem I encounter. 👍 👍 👍
Full of value! As always! Will never see Data Validation the same way!
Glad it is helpful! It can be a powerful tool.
Thank you. Your tutorial guides me thru writing the custom DV formula.
This is the best I have ever seen thank you very very much, I recommended this for my students
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: ua-cam.com/video/FRiFfKb_B_A/v-deo.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...
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.
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
All of your videos are very useful, Leila.
Glad to hear that, Karen!
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
And I thought I know data validation quite well...but this something different and innovative..good work Leila
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.
Really helpful step by step tutorial. Thanks a lot.
My pleasure, glad to help.
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
Big thank-you, Leila! Superb channel on UA-cam.
Very happy you like the channel. Thank you for your support.
i really appreciate your effort and wanna say thank you for this very informative and useful tutorial.
You're very welcome Abhishek!
Found this really helpful. Thx.
you make all of this seem sooooo easy. thank you!!
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.
Thanks ma'am...its always worth watching your video.
You're very welcome Mohit :)
You made me like Excel 👍.
This session is helpful, is it possible to get expand & collapse option in data validation list of we have multiple header & subgroup
As always excellent step by step explanation
Thank you Vida.
Love you mam, you helped me a lot... I keep on watching your teaching...
It's my pleasure
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!
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.
Super great for Excel validation.
Thank you so much! 🥰
You’re welcome 😊
Just another great video!
Great tip with Ctrl+C+C! 🙏 🙌🙌🙌
FANTASTIC! THANKS!!!
Just wonderful. So happy there are people like you on UA-cam. Is there a way to add validation for specific number of decimal places allowed in a cell?
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
Keep it simple:
AND("A0001"
you are brilliant,amazing,fabioulus
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 :)
Data mining is such a pain. It has to do with lack of foresight in planning.
Thanks for teaching an old dog new tricks.
Wow.. that's best...Leila
..
hats off to your training - wow
Nice Vedio Leila....thank you so much for explaining very well 👍👍💐
You're very welcome. Glad you find the explanations easy to follow.
Nicely done!
Thank you Brandon
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...you are awesome in Excel and you are a great explanator...thanks for your handy tricks and tips...
Thank you! very useful & very intersting
You're very welcome Chaminda.
Amazing. I didn't know that before this video. Thanks for good content
Nice.. thanks for toturial... 👍
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 :-|
Good learning today
Happy to hear that :)
What if the data validation cell is initially empty? I tried this method and the individual formulas return T/F as necessary, but data validation returns no error. I didn't know there were excel gurus like you out there. Lets me know that I know nothing. I appreciate you videos
Very usefull this video, thanks for sharing
You're very welcome Miguel. Thank you for your support.
Wow .. amazing tutorial
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
good video
how can we conect different sheet that we can write data on first and if shifted on the rest of the sheets ?
very helpful
You are best 👍 is it possible to give reference in formal of other sheets? It's not letting me.
mic drop! Brava! 👍
Very nice 👌 validation,
Thank You
Thanks Leila. This was very interesting!!! :) :)
You're very welcome John :)
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? ua-cam.com/video/VqQACB_69SQ/v-deo.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 ua-cam.com/video/pCAXRLYr59E/v-deo.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 )
thanks for the insight.....very helpful....got u!!
Glad you like it Samuel.
Exactly what I needed to know! Thank you so much!!
Can this validation rules be applied on a table?
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
this one is superb. lovely
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!
How do you use data validation to calculate sum total of multiple cells based on whichever drop down option you select?
Excel-lent examples for the custom validation!
Thank you very much Doug.
can you tell me where can i use this?
Hi Leila, is there any VBA code for 1 letter fixed & 4 number. The datavalidation will be replace if user copy&paste into cell
Thanks u so much
I am just a Fan of you
Really appreciate your support! Thank you.
I have a complex workbook that multiple people input data and the file is shared on SharePoint. I’ve noticed that the data validation settings do not function if they open the workbook in the browser but the validation will work if “open in app” is selected. I’ve encouraged everyone to open in the app but they’re not as a compliant as I’d like! Are there settings I am missing that will allow the data validation to work either way? Or, is there a setting that requires a workbook to be opened in the app rather than the browser? Thanks for you help!
Hello Leila, could you do a video on how to create a function to validate MAC addresses using Custom Data Validation or VBA? A MAC address is written as XX:XX:XX:XX:XX:XX where X can only be numbers 0-9 and the letters A-F. Thanks.
Its great lesson Leila, thank you!, But, I looking to do same thing using VBA.
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
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.
If we Sum data of multiple columns or rows that should be equal to 100 not less or more
How to create this function?
Thanks
I have a question. As you are checking for 'non-numeric' will it allow non-alpha numeric characters?
I just dont know how do you know which the numbers you have to use when doing a formula?
hi.....thx for your helpful tutorial....i have a case hear need for your help on it .....if i have 2 cells have different Data validation ....for Example: A cell (A4) with data valid are characters (W,X,Y,Z)"Drop list", and on-other hand cell (B4) with data valid are characters(A,NA,NAH,ARS)............i need to make data in Cell (B4) as i mentioned above, BUT with a condition.....that the characters(A,NA,NAH) are only valid when the characters at cell (A4) are (Y)Or (Z) only, and others case free from condensations....Thankful for your valuable time :)
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.
Gud presentation.. Keep it up..
Question: how about any whole number greater and equal to 0, and only allow letter X to be input? example: x or 123 thanks
Hello, how to validate if we have simbol. Example P01-DP01?
Thanks
Hi, is there way to do data validation (error message) for the cell that containing formula based on the end result? I see data validation works only on cell where the input is manual. Is it possible to do on the cell which doesn't has manual entry that is cell result of some formula.
Can i run data validation with past data?
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.