Create Custom Data Validation Checks (Advanced Rules) ✔️for better Excel Data Entry

Поделиться
HTML-код
  • Опубликовано: 4 дек 2024

Комментарии • 16

  • @EXCELWIZARDINMINUTES
    @EXCELWIZARDINMINUTES  3 месяца назад +3

    For more content like this one, subscribe: ruclips.net/user/excelwizardinminutes?submission=1

  • @dineshvemula3900
    @dineshvemula3900 3 месяца назад +1

    Very helpful.

  • @ramiaboujaoude701
    @ramiaboujaoude701 3 месяца назад +1

    I thought I knew everything about excel, but this is new to me...

  • @sabrinajones6867
    @sabrinajones6867 3 месяца назад +1

    Wow! really cool!

    • @EXCELWIZARDINMINUTES
      @EXCELWIZARDINMINUTES  3 месяца назад

      U can also check the video in the description about basic data validation

  • @eugenienseir9370
    @eugenienseir9370 3 месяца назад +1

    Amazing Amazing

  • @bandita445
    @bandita445 Месяц назад

    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.

    • @EXCELWIZARDINMINUTES
      @EXCELWIZARDINMINUTES  Месяц назад +1

      @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
      @EXCELWIZARDINMINUTES  Месяц назад

      @@bandita445 ie len(txt)>=6 instead of a specific num of char

    • @bandita445
      @bandita445 Месяц назад

      @@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?

    • @EXCELWIZARDINMINUTES
      @EXCELWIZARDINMINUTES  Месяц назад +1

      @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...

    • @bandita445
      @bandita445 Месяц назад

      @@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?