Macros for validating data in excel

To get the current date, use the TODAY function, and then add the desired number of days to it to compute the end date.

For example, to limit the data entry to 6 days from now (7 days including today), we are going to use the built-in Date rule with the formula-based criteria: For starters, copy your validation formula into some cell to make sure it does not return an error such as #N/A, #VALUE or #DIV/0! If you are creating a custom rule, the formula should return the logical values of TRUE and FALSE or the values of 1 and 0 equating to them, respectively.

So, for weekdays (Mon to Fri) the result of the formula should be less than 6, and for weekends (Sat and Sun) greater than 5.

In many situations, if you select the Ignore blank box when defining the rule (usually selected by default) and one or more cells referenced in your formula is blank, any value will be allowed in the validated cell.To pin down existing entries that do not meet your data validation criteria, use the Circle Invalid Data feature as shown in How to find invalid data in Excel.Surprisingly, none of the inbuilt Excel data validation rules cater for a very typical situation when you need to restrict users to entering only numbers in specific cells.To create the data validation rule, execute the following steps. Input messages appear when the user selects the cell and tell the user what to enter.

Leave a Reply