Let’s say you have an input range that covers the whole year. You only want users to make entries in the current month column. How can you limit the month entry? The answer is a custom Data Validation.
A simple example is shown in the image below.
Based on the month chosen in cell B1 we want the user to only be able to input entries in the correct column in the range B4:M8.
Note all the dates in row 3 are the first of the month. The example completed file can be downloaded at the button at the bottom of the post.
We can use a Conditional Format to highlight the current input column. You select the range B4:M8 before creating the Conditional Format. An example is shown below.
The resulting format is shown below.
The formula used was
=B$3=$B$1
The formula must return TRUE for the format to be applied.
Restricting entries
To restrict the entries we can use a Data Validation on the same range. Select the range B4:M8 and then press in sequence Alt A V V to open the Data Validation Dialog.
In the Allow drop down select Custom and use the same formula as above.
If someone tries to enter a value in another column the following dialog pops up and stops them.
You can customise this message by using the Error Alert tab in the Data Validation dialog. See example below.
Please note: I reserve the right to delete comments that are offensive or off-topic.