I would like to use data validation to ensure someone has entered a valid date, but I'd also like them to be able enter TBD – just those two options. Is this at all possible without VBA? Is there a custom formula I could enter into the data validation tool?
Excel – How to set up data validation to allow only a date or a specific string
data validationmicrosoft excel
Related Solutions
You can do this with Excel's built-in Data Validation feature. It will work as an in-cell dropdown which will also allow you to type in a value not included in the dropdown. You'll need to do the following:
- Somewhere in the workbook, enter the data you want to appear in the dropdown. In my example, it's on the same sheet as the dropdown, but it doesn't need to be.
- Select the cell where you want to place the dropdown. Then, on the 'Data' ribbon, select 'Data Validation'. From there you will need to choose to validate your data from a list. Enter the address of the source data you created in Step 1.
- Click the 'Error Alert' tab in the Data Validation dialog. Then, untick the 'Show error alert after invalid data is entered' checkbox. Click 'OK'.
The simplest method would be to just use Conditional Formatting. Especially if you can use tables:
If you're using a table you can simply select the column and set the conditional format to formula
=[column]2=""
Then format to fill in red.
-The upside of this is that table ranges are dynamic, so as new rows are added the formula will be retained.
-The downside of this is that there's nothing to stop them ignoring the warning.
You could use VBA, but many users will deny macros in their workbooks, disabling the function.
EDIT
A simple way to have conditional formatting show up if a cell is blank in a row that is being used, where you can't guarantee any particular cell will be populated:
=AND($[CurrentColumn]2="",COUNTA($[FirstColumn]2:$[LastColumn]2)<>0)
This formula will activate the conditional formatting if the cell you want highlighted is blank and any other cell in the row is not blank (for those who don't know, a COUNTA formula counts all cells in a range that are not empty). Be sure to exclude any cells containing formulas from the COUNTA as they will be counted as not blank.
Best Answer
You can do this by setting the cell format to
Textand then setting up a custom data validation rule.To set the cell format, right-click the data entry cell and choose
Format Cells.... Then set the category toText.To set up custom data validation, select the data entry cell, click
Data Validationon the Data ribbon. Set the Allow box toCustom. Then enter the formula below, whereA1is the address of your data entry cell.This will allow entry of "TBD" or anything that Excel automatically recognizes as a date. This may allow some irregular formats you did not have in mind, such as
mm/dd-yyyyorm-d. You'll need to take a different approach if you want only certain date formats to be allowed.