Video nenà dostupné.
OmlouvĂĄme se.
Excel Custom Data Validation (Use formulas to check for text, numbers & length)
VloĆŸit
- Äas pĆidĂĄn 23. 05. 2018
- Join 400,000+ professionals in our courses here đ link.xelplus.com/yt-d-all-cou...
Enhance your Excel skills with this detailed tutorial on creating complex data validations. Ideal for ensuring accurate data input in your Excel templates, this video is a must-watch for those needing to enforce specific text and number combinations in cells.
đ Key Learning Points:
- Custom Data Validation: Learn how to use Excel's custom data validation feature for complex checks.
- Formula-Based Validation: Discover how to construct formulas to validate specific data formats, such as a text character followed by numbers.
- Practical Examples: Get step-by-step guidance on setting up validations for different cases, like ensuring a specific character count or a fixed text prefix.
- Advanced Formula Techniques: Understand the use of functions like LEN, RIGHT, ISNUMBER, and MID to create robust validation criteria.
- Error Prevention: See how custom validations can prevent incorrect data entries and maintain data integrity.
- Input and Error Messages: Learn to add helpful input and error messages for users, enhancing the usability of your Excel file.
đ Practical Applications:
- Create templates that ensure users enter data correctly.
- Apply complex validations for data consistency in professional Excel reports.
- Enhance data integrity in templates shared within teams or organizations.
We also cover Excel's LEN, RIGHT, LEFT and MID functions.
Full Article: www.xelplus.com/excel-custom-...
â Links to related videos: â
Basics of Data Validation: âą Create SMART Drop Down...
â My Online Excel Courses â
Courses âș www.xelplus.com/courses/
âĄïž Join this channel to get access to perks: / @leilagharani
đâ Get the Official XelPlus MERCH: xelplus.creator-spring.com/
đ Not sure which of my Excel courses fits best for you? Take the quiz: www.xelplus.com/course-quiz/
đ„ RESOURCES I recommend: www.xelplus.com/resources/
đ©Letâs connect on social:
Instagram: / lgharani
LinkedIn: / xelplus
Note: This description contains affiliate links, which means at no additional cost to you, we will receive a small commission if you make a purchase using the links. This helps support the channel and allows us to continue to make videos like this. Thank you for your support!
#excel
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!!!
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)
Full of value! As always! Will never see Data Validation the same way!
Glad it is helpful! It can be a powerful tool.
Ma, can I say you're awesome you seem to have solution to every excel problem I encounter. đ đ đ
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
Thank you. Your tutorial guides me thru writing the custom DV formula.
That was great...a big thumbs up!...exposing and using the clipboard that way was really great too.
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 :)
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
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.
All of your videos are very useful, Leila.
Glad to hear that, Karen!
Excellent.. as usual.. Great learning. Thanks for such a nice tutorial.
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
Great tip with Ctrl+C+C! đ đđđ
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: czcams.com/video/FRiFfKb_B_A/video.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...
Big thank-you, Leila! Superb channel on CZcams.
Very happy you like the channel. Thank you for your support.
Thank you so much! đ„°
Youâre welcome đ
Found this really helpful. Thx.
Really helpful step by step tutorial. Thanks a lot.
My pleasure, glad to help.
You made me like Excel đ.
Thanks a lot for this informative video!
Super great for Excel validation.
Thanks ma'am...its always worth watching your video.
You're very welcome Mohit :)
you make all of this seem sooooo easy. thank you!!
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 another great video!
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.
i really appreciate your effort and wanna say thank you for this very informative and useful tutorial.
You're very welcome Abhishek!
hats off to your training - wow
As always excellent step by step explanation
Thank you Vida.
FANTASTIC! THANKS!!!
Love you mam, you helped me a lot... I keep on watching your teaching...
It's my pleasure
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.
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
Just wonderful. So happy there are people like you on CZcams. Is there a way to add validation for specific number of decimal places allowed in a cell?
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
Hi Leila...you are awesome in Excel and you are a great explanator...thanks for your handy tricks and tips...
Amazing. I didn't know that before this video. Thanks for good content
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 :)
Nice Vedio Leila....thank you so much for explaining very well đđđ
You're very welcome. Glad you find the explanations easy to follow.
This session is helpful, is it possible to get expand & collapse option in data validation list of we have multiple header & subgroup
Thank you! very useful & very intersting
You're very welcome Chaminda.
Nicely done!
Thank you Brandon
Thank You
Exactly what I needed to know! Thank you so much!!
Very usefull this video, thanks for sharing
You're very welcome Miguel. Thank you for your support.
Thanks u so much
Wow .. amazing tutorial
Good learning today
Happy to hear that :)
Nice.. thanks for toturial... đ
Keep it simple:
AND("A0001"
Very nice đ validation,
Thanks Leila. This was very interesting!!! :) :)
You're very welcome John :)
this one is superb. lovely
Its great lesson Leila, thank you!, But, I looking to do same thing using VBA.
thanks for the insight.....very helpful....got u!!
Glad you like it Samuel.
I am just a Fan of you
Really appreciate your support! Thank you.
very helpful
thanks thanks thanks.
Data mining is such a pain. It has to do with lack of foresight in planning.
Thanks for teaching an old dog new tricks.
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
Thanks. Alot!
Data validation for date and time of the cell
Excel-lent examples for the custom validation!
Thank you very much Doug.
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.
Extraordinary Madam, Great
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.
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 just dont know how do you know which the numbers you have to use when doing a formula?
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.
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
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() đ
You are best đ is it possible to give reference in formal of other sheets? It's not letting me.
How do you use data validation to calculate sum total of multiple cells based on whichever drop down option you select?
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
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
Can this validation rules be applied on a table?
Nice
Great
You are beautiful and inteligent at the same. Thank you so much
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!
1 query. If certain cell is say "abc" then data validation is only numeric else any text
Gud presentation.. Keep it up..
Hello, how to validate if we have simbol. Example P01-DP01?
Thanks
Hey Leila can you help me in making a data validation that can help me in getting numbers between "1-10" or "AB" or "ML".
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? czcams.com/video/VqQACB_69SQ/video.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 czcams.com/video/pCAXRLYr59E/video.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 your tutorials are really awesome and really useful but one question. how come i get an E instead of a 4 when i type =MID(A17,2, 1) ?
I have been looking and looking but can not find the information I need. All I can find is how to "SET" a cell for a specific formatted input or to "EXTRACT" and place in another cell. All that is good. However, "WHAT IF....." what if you are importing information from another source. You format the cell for only 50 characters. But the imported information has over 50 characters (example:51 to 100 characters). All the characters beyond the 50 character limit are not needed. So when you copy and paste the information, how do you get rid of (delete) the rest of the characters beyond the set limit of 50?
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.
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
How to prevent Entry of simmilar words that we not want to do repeat in the Verylong list.
Example : A book contains many words and i want to make a list of so many important words and those words might be repeating in further chapters ..so how can i prevent to repeat them ????
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.