Does Excel allow a formula/variable error alert for data validation?

1
votes

I have a worksheet with a date column. Data in that column is validated based on the worksheet's name. For example, on the worksheet named "Jan 2016" only dates between January 1 and January 31, 2016.

Once a month, users copy a template in the workbook and name it for the current month.

I am curious to know if there is an automatic way for the validation error to tell the user something like "You must enter a date between [MONTH] 1 and [MONTH] [LAST_DAY_OF_MONTH], [YEAR]".

I could do this with a VBA trigger that checks for changes to the worksheet name and sets the validation parameters at that time, but I'm curious to know if I can do this without VBA.

I tried entering a formula in the "Error Message" field (Data Validation > Error Alert) but that displayed the formula literally giving me an error like this:

$A$1

Continue?

I have Excel 2016.

1
excelexcel-formula
I think your only option would be to put the message as a formula in a worksheet cell. - Tim Williams

1 Answers

2
votes

Here is a non-VBA method...

Use conditional formatting to fill the cell red (or any 'scary color') when the user enters a date that is not in the current sheet's month.

This example will be VERY EASY to setup for the next month... Just change the date. Mine would be cell C2 below.

How-It-Works:

  1. Month - Find the cell that displays the Month of the current sheet. Mine is in C2 below.
  2. FirstDate - Find the FIRST cell where the user would enter the FIRST date. Mine is in C6 below.
  3. Select the FIRST cell where the user would enter the FIRST date through the last cell where the user would enter a date. Mine is C6:C9.
  4. On the Excel Ribbon, navigate to Home > Conditional Formatting > New Rule
  5. Select 'Use a formula to determine which cells to format'
  6. Enter the following formula: =IF([FirstDate]="",FALSE,OR(EOMONTH([Month],0)<[FirstDate],EOMONTH([Month],-1)>=[FirstDate])) NOTE - replace [Month] with an ABSOLUTE$$ referenced cell from step 1 above. Replace [FirstDate] with a RELATIVE referenced cell from step 2 above.
  7. Click the Format button to format the invalid dates with red fill.
  8. Click OK and test. enter image description here