restricting data entry

Experts,
In my spreadsheet I use the following ranges for a specific purpose:
A12:A21 - Date Entered
F12:F21 - Invoice Date
G12:G21 - Invoice Amount

I would like to keep data from being entered in F12:F21 and G12:G21 unless there is a date in the corresponding range A:12:A21
Frank FreeseAsked:
Who is Participating?
 
zorvek (Kevin Jones)ConsultantCommented:
That should not interfere.

Can you post your workbook? If not, try to reproduce in another workbook with dummy data and, if the problem still exists, post that workbook.

Also, I forgot to mention that your must uncheck the "Ignore blanks" checkbox.

Kevin
0
 
Berkson WeinTech FreelancerCommented:
To have conditional locking / unlocking of cells, you'll need to use VBA.

Here's an example that should be able to be adapted to your needs:
http://answers.google.com/answers/threadview/id/736450.html

Hope this is a good start and helps you out.
0
 
zorvek (Kevin Jones)ConsultantCommented:
You don't need VBA. Select cell F12. Navigate to tab Data. Click "Data Validation" in the "Data Tools" group.

Select "Custom" and enter this formula:

   =AND($A12>=DATE(2000,1,1),$A12<=DATE(2099,12,31))

Copy the formatting in cell F12 to the other cells.

Kevin
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Berkson WeinTech FreelancerCommented:
I agree, that's a much better solution.
0
 
zorvek (Kevin Jones)ConsultantCommented:
You can restrict the range of dates that must be entered in column A. For example, to restrict the dates to today to 31 days from today:

   =AND(A1>=TODAY(),A1<=TODAY()+31)

Kevin
0
 
Frank FreeseAuthor Commented:
i goofed - all i really need to do is make sure that I have a date in A12 and F12 before I can enter data in G12
0
 
zorvek (Kevin Jones)ConsultantCommented:
Select cell G12. Navigate to tab Data. Click "Data Validation" in the "Data Tools" group.

Select "Custom" and enter this formula:

   =AND($A12>=DATE(2000,1,1),$A12<=DATE(2099,12,31),$F12>=DATE(2000,1,1),$F12<=DATE(2099,12,31))

Copy the formatting in cell G12 to the other cells.

Kevin
0
 
Frank FreeseAuthor Commented:
I entered in the formula in G12 for data validation, entered in valid dates in A12 and F12 and an amount in G12 and I was kept from putting in an amount - it did not work.
0
 
zorvek (Kevin Jones)ConsultantCommented:
What dates did you enter?

Kevin
0
 
Frank FreeseAuthor Commented:
in A12 2/28/2011
in F12 2/28/2011
0
 
Frank FreeseAuthor Commented:
I treid something like this in if $A12 = "" and $F12 =""

 =AND($A12=””,$F12=””)

But I got an error stating range could not be found
0
 
zorvek (Kevin Jones)ConsultantCommented:
It works at my house.

See attached.

Kevin
Q-26856092.xlsx
0
 
Frank FreeseAuthor Commented:
I also have a conditional formatting that also applies to G12 - could that be the problem
0
 
Frank FreeseAuthor Commented:
unchecking the ignore blanks was the difference - great - I'll close this and open a continuation
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.