• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Data validation to restrict cell numerical values only past a certain date?

R

raemondo

New Member
Hi,

I am creating a spreadsheet to track monthly progress for a project. I want to be able to use data validation to restrict cell entry if a particular month is already passed, and if a particular month has not arrived yet. For instance, if we are in July, the user may only enter values for July. Values for June and prior as well as August and later, should be restricted or grayed out. How can I use data validation to make this work?

The values are numerical values that I also want restricted to as 1s or 0s, which I already currently use data validation for. As an additional question, am I able to layer on 2 levels of data validation?

Thanks in advance!
 
Hi,

Go to data validation. In setting>Date. In Data select the between option & enter the start & end date. By setting this condition only dates with the start date & end date can be entered by the user.
 
Hi,

To answer your 2 levels of data validation. Try the custom option in settings in data validation. Here type out the formula which satisfies all your condition. This should work.
 
Srinidhi, thanks for your replies. However, the values I have are numerical values, not date values. The selection of data validation as you described in your first post won't work because it is for date values only.

Let me clarify:

Imagine I have 12 columns, and each column is labeled for each month.
The current month is July.
I want the user to put a numerical value of either 1 or 0 in July.
I want to have data validation restrict the user to ONLY entering numerical values in July, because July is the current month.
How can I do this?
 
Hi raemondo,

I hope you are putting your column as real date like 1/1/2014 , 1/2/2014 and so on (dd-mm-yyyy format) and than custom formating them as mmmm to just show month say in row 1, just try below formula in Custom data validation.

=AND(MONTH(TODAY())=MONTH(B$1),OR(B2=1,B2=0))

I had my column headers starting from B1.

Regards,
 
You can lock other column apart from the current month, where you want the users to enter the data.
 
You must log in or register to reply here.
Back
Top