Is there a way that I can require the user to input a certain entry (like a list) in just the first 6 characters, but the total number of digits can be anything? For example, first 6 digits must be R08842 or R55457, but the remaining characters can be anything (eg. R088424259AZ). I know that one way around this is to make 3 columns, set up a list for the first column, no criteria for the user on 2nd column, and combine them in 3rd column, but I'd rather keep it to 1 column for user input if possible.
@bandita445 u need to use custom validation rule like the video. 1 rule is the total num of characters. U can put a min or an exact num of char. Then u have to put a formula for the first 6 char. For example lett(txt, 6)= ...... and u combine the formulas. Have a look at the video and practice. Am sure u ll get the right formula
@@EXCELWIZARDINMINUTES Thanks! I was able to figure it out, using the same concept as your video with combining them (except I used Or instead of And). My next question, how do I apply that same formula for the whole column? If I select a big range and then set up my Data Validation, they all refer to the same cell (eg cell A2). Instead of having to set up a Data Validation for each cell one by one, is there a way I can do all of them at once, but for it to update the cell?
@bandita445 u must have a prob with the formula u r using. For example a formula like this :=AND(LEN(A1)>=6, LEFT(A1,6)="ABC123") Can be applied on a whole range. Now if the abc123 is in a diff cell for each data validation, i think u can refer to the top cell and it will work for all. Try...
@@EXCELWIZARDINMINUTES I ended up making my report as a Table first, before doing the Data Validation, and that worked. One of the problems I ran into though is that if I copy/paste my formula into the Data Validation, I get an Error saying that there's a problem with my formula, but if I manually type it the exact way, then it works. Any idea why that is?
For more content like this one, subscribe: ruclips.net/user/excelwizardinminutes?submission=1
Very helpful.
Thank you Dinesh! :)
Wow! really cool!
U can also check the video in the description about basic data validation
Amazing Amazing
Thank u :)
I thought I knew everything about excel, but this is new to me...
There are sooo many things in Excel, it never ends
Is there a way that I can require the user to input a certain entry (like a list) in just the first 6 characters, but the total number of digits can be anything? For example, first 6 digits must be R08842 or R55457, but the remaining characters can be anything (eg. R088424259AZ). I know that one way around this is to make 3 columns, set up a list for the first column, no criteria for the user on 2nd column, and combine them in 3rd column, but I'd rather keep it to 1 column for user input if possible.
@bandita445 u need to use custom validation rule like the video. 1 rule is the total num of characters. U can put a min or an exact num of char. Then u have to put a formula for the first 6 char. For example lett(txt, 6)= ...... and u combine the formulas. Have a look at the video and practice. Am sure u ll get the right formula
@@bandita445 ie len(txt)>=6 instead of a specific num of char
@@EXCELWIZARDINMINUTES Thanks! I was able to figure it out, using the same concept as your video with combining them (except I used Or instead of And). My next question, how do I apply that same formula for the whole column? If I select a big range and then set up my Data Validation, they all refer to the same cell (eg cell A2). Instead of having to set up a Data Validation for each cell one by one, is there a way I can do all of them at once, but for it to update the cell?
@bandita445 u must have a prob with the formula u r using. For example a formula like this :=AND(LEN(A1)>=6, LEFT(A1,6)="ABC123")
Can be applied on a whole range. Now if the abc123 is in a diff cell for each data validation, i think u can refer to the top cell and it will work for all. Try...
@@EXCELWIZARDINMINUTES I ended up making my report as a Table first, before doing the Data Validation, and that worked. One of the problems I ran into though is that if I copy/paste my formula into the Data Validation, I get an Error saying that there's a problem with my formula, but if I manually type it the exact way, then it works. Any idea why that is?