What it does
Uses a picker prompt to quickly apply common data validation settings – numbers/ranges, dates/ranges, lists, etc.  Allows you to save up to 5 of your favorite data validation settings to apply with just a couple of keystrokes.

When to use it
When you want to quickly apply common data validation settings across one or more selected cells within a sheet, this is an efficient way to do so.

Why to use it
It’s an efficient way to apply common data validation settings, including up to 5 of your favorite data validation settings.

Default shortcut
None

Other Details

• Category: Data / Content
• Difficulty: 2/5
• Usage/frequency: 2/5
• Automation factor: 4/5 (estimated 15 seconds saved each time used)
• Type: Shortcut
• Date added: 3/3/2020
• Tags: Data validation, picker, list

Related Macros
Bulk Data Validation
Search Data Validation List
Add Data Validation From Named Range

Other Articles
None

None

Prerequisites
Select the cells(s) for which you want to apply data validation.

Instructions
After you have selected the cell(s) for which you want to apply data validation, run the Data Validation Picker macro.  If the data validation settings you want to apply have been saved, simply enter 1, 2, 3, 4, or 5.  You can even set the default for the data validation settings you run the most frequently, which will be displayed by default so that all you have to press is the Enter key.  Instructions for other options are as follows:

• Option A – this will allow any value (removing any data validation settings).
• Option W – this will restrict values to whole numbers within a range.*
• Option D – this will restrict values to numbers (including decimals) within a range.*
• Option E – this will restrict values to dates within a range.*
• Option T – this will restrict values to times within a range.*
• Option N – this will restrict values to text with the text length within a range.*
• Option L – this will restrict values to a defined list, either directly or as a referenced range address or range name.  To specify list values, add a space, then the list values, separated by a comma.  To specify a reference, add a space, then a range address/name, starting with the = sign.  Example: “L USD,CAD,MXN” or “L =Currency_List” would apply a data validation list.
• Option I – this will toggle input messages on/off.
• Option R – this will toggle error messages on/off.
• Option P – this will display the data validation prompt.  Use this to review data validation settings, including specifying input or error messages.
• Option B – this will run the Bulk Data Validation macro, allowing you to apply various data validation settings across many different sheets and/or ranges.

To configure one of the five available custom data validation settings, enter the code (and operator/numbers if applicable), then a space, then the word DEFAULT, then a number between 1-5 for the custom number you want to use.

*To specify the range for most data validation options, add a space, then the operator (=, <>, <, <=, >, >=, B), then the number/date/time.  For operator B (between), separate values with a < character.  For example, “W B100<10000” would restrict to whole numbers between 100 and 10,000.