Note: An Input Message for cell F12 could have been used instead of a comment: www.teachexcel.com/excel-tutorial/excel-data-validation-limit-what-a-user-can-enter-into-a-cell_1297.html#input_help
Thank you. Thank you. Like many tutorial creators, you know how Excel works. Unlike most, you know how offices work. This makes it so much more useful.
I really like the way you deliver salient information so clearly! This is EXCELLENT. Other youtubers should take note. Your pace and clear speach is very helpful to elucidate what you are teaching. You are a MODEL teacher.
Great tutorial! Is there any way of implementing a calendar date picker for the date input field? i know Excell had one implementet before which was pretty nice.
I do like your method of building complex nested formulas and will use that, however; once built, why would you not copy & paste the formula directly into the Data Validation box?
First thanks for this, I am learning a lot, second I am trying to make a validation link to a formulat like you are doing here, but I want the cell to only allow 9 numeric characters and keep all my zeros in the front. I am getting it to work with the exception that if I ‘accidentally’ add another character at the end, it kicks off a zero at the front. How can I fix this? Thank you! [formula I used: AND(ISNUMBER(cell+0),LEN(cell)
Quick question: When placing a comment on a cell (to help the user know the format), for me it also shows my name and today's date. Maybe newer versions of Excel do this? Any way to just have a simple comment only, like you have in the video above (starting at 19:08)? Thanks!
I have a question. Is it possible to create a very large entry form with many fields (items) following the instructions presented..... I mean a form that would be 2 or 3 pages (A4) long?
This was very, very helpful. However, I am having problem in one area of the tutorial. For the Sector validation formula, I cannot figure how you copied The right(E13,1) part and pasted it into the (K14) part of the NumberValue formula. When I tried to do that, I pressed Ctrl C and it created the ant trails but I could not select the NumberValue formula to paste the data there. I watched the video a bunch of times and could not figure out how you did that.
I think that you are selecting the cell and copying it, BUT, what you need to do is to double-click the cell and then select all of the formula in it and then hit Ctrl+C; this will copy all of the characters in the formula without using any of Excel's copy/paste relative/absolute cell reference magic, that would mess this up. Try doing it like this and let me know if that solves the issue.
For the sector field, is there a way to use the Proper function to automatically capitalize the first letter if the used inputs the value as lower case?
Not without using VBA to do that. However, you can require that the first letter is a capital by using the EXACT() function with the PROPER() function. It could look something like this: =EXACT(A1,PROPER(A1))
Under the Special tab in number format, there is no available list when the locale is set up United Kingdom. The list only appears if set to United States which is not helpful when based in the UK! Help please
There might be an addon or localization pack you need to install, otherwise you can use Conditional Formatting and Custom number formats instead. The layers of conditional formatting can get a little complicated when you're dealing with phone numbers of different lengths. For example here in NZ, mobile phone numbers can be between 8 and 11 digits long (excluding leading zeros) and landline numbers are 8 digits long (excluding leading zeros). The conditional formatting has to determine the prefix code of the number to know whether it is a two-digit area code, or a three-digit mobile number prefix. I've worked it out for NZ numbers, but I don't know the telephone number formats in the UK.
How would you program the sector field if you want a capital "A" to give a "TRUE" and a lower-case "a" to give a FALSE? I tried EXACT but it won't work.
@Larkin Calendar, I was able to get a FALSE for "a" and instead of using the VALUE function, I used a -- in front of the RIGHT formula, as a "--" will force text to become a number. Hope that helps. =AND(LEN(F12)=3,LEFT(F12,1)="a",MID(F12,2,1)="-",ISNUMBER(--RIGHT(F12,1)),EXACT(LEFT(F12,1),"A"))
Note: An Input Message for cell F12 could have been used instead of a comment: www.teachexcel.com/excel-tutorial/excel-data-validation-limit-what-a-user-can-enter-into-a-cell_1297.html#input_help
Thank you. Thank you. Like many tutorial creators, you know how Excel works. Unlike most, you know how offices work. This makes it so much more useful.
I really like the way you deliver salient information so clearly! This is EXCELLENT. Other youtubers should take note. Your pace and clear speach is very helpful to elucidate what you are teaching. You are a MODEL teacher.
I have watch a lot of hours of excel tutorials, but in Your videos is is always something absoulutely new things or new concepts. Great job man!
Thank you so much! I really appreciate your comments :)
I learn so much by following your tutorial! You have no idea how much this helps! Thank you~
Very clear. Not squiggly. Thanks
Thanks a ton for the interesting tutorial. Best regards
Great video and a lot of good ideas. I love making forms in Excel.
Beautifully presented - yet again!
Thanks so much :))
Great tutorial!
Is there any way of implementing a calendar date picker for the date input field? i know Excell had one implementet before which was pretty nice.
Hi, thanks for the magnificent tutorial. Is there a way to make a list of sectors based on the department selected?
I do like your method of building complex nested formulas and will use that, however; once built, why would you not copy & paste the formula directly into the Data Validation box?
First thanks for this, I am learning a lot, second I am trying to make a validation link to a formulat like you are doing here, but I want the cell to only allow 9 numeric characters and keep all my zeros in the front. I am getting it to work with the exception that if I ‘accidentally’ add another character at the end, it kicks off a zero at the front. How can I fix this? Thank you! [formula I used: AND(ISNUMBER(cell+0),LEN(cell)
ممنون
Quick question: When placing a comment on a cell (to help the user know the format), for me it also shows my name and today's date. Maybe newer versions of Excel do this? Any way to just have a simple comment only, like you have in the video above (starting at 19:08)? Thanks!
I have a question. Is it possible to create a very large entry form with many fields (items) following the instructions presented..... I mean a form that would be 2 or 3 pages (A4) long?
There is no name manager in the mac version...any tips? especially on how to do the data validation in the form for lists?
This was very, very helpful. However, I am having problem in one area of the tutorial. For the Sector validation formula, I cannot figure how you copied The right(E13,1) part and pasted it into the (K14) part of the NumberValue formula. When I tried to do that, I pressed Ctrl C and it created the ant trails but I could not select the NumberValue formula to paste the data there. I watched the video a bunch of times and could not figure out how you did that.
I think that you are selecting the cell and copying it, BUT, what you need to do is to double-click the cell and then select all of the formula in it and then hit Ctrl+C; this will copy all of the characters in the formula without using any of Excel's copy/paste relative/absolute cell reference magic, that would mess this up. Try doing it like this and let me know if that solves the issue.
Great tutorials... when is Part 3 being released?
Next Tuesday. If you are an email subscriber, the weekly email goes out on Wednesday, but the tutorial itself will be available on youtube on Tuesday.
Thank you
For the sector field, is there a way to use the Proper function to automatically capitalize the first letter if the used inputs the value as lower case?
Not without using VBA to do that. However, you can require that the first letter is a capital by using the EXACT() function with the PROPER() function. It could look something like this: =EXACT(A1,PROPER(A1))
Thank you!!!
Under the Special tab in number format, there is no available list when the locale is set up United Kingdom. The list only appears if set to United States which is not helpful when based in the UK!
Help please
There might be an addon or localization pack you need to install, otherwise you can use Conditional Formatting and Custom number formats instead. The layers of conditional formatting can get a little complicated when you're dealing with phone numbers of different lengths. For example here in NZ, mobile phone numbers can be between 8 and 11 digits long (excluding leading zeros) and landline numbers are 8 digits long (excluding leading zeros). The conditional formatting has to determine the prefix code of the number to know whether it is a two-digit area code, or a three-digit mobile number prefix. I've worked it out for NZ numbers, but I don't know the telephone number formats in the UK.
I am working on the Sector section of this form. However, i do not believe numbervalue is available in 2010. how do I get around this?
This is probably late, but just use =VALUE() instead, it works just fine.
How would you program the sector field if you want a capital "A" to give a "TRUE" and a lower-case "a" to give a FALSE? I tried EXACT but it won't work.
@Larkin Calendar, I was able to get a FALSE for "a" and instead of using the VALUE function, I used a -- in front of the RIGHT formula, as a "--" will force text to become a number. Hope that helps.
=AND(LEN(F12)=3,LEFT(F12,1)="a",MID(F12,2,1)="-",ISNUMBER(--RIGHT(F12,1)),EXACT(LEFT(F12,1),"A"))
Sir you make this educational video very lethargic
I tried to add strippers, but TH-cam got bitchy about it! :D
Eliminate email link in excel