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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.
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
Berkson WeinTech FreelancerCommented:
I agree, that's a much better solution.
HTML5 and CSS3 Fundamentals

Build a website from the ground up by first learning the fundamentals of HTML5 and CSS3, the two popular programming languages used to present content online. HTML deals with fonts, colors, graphics, and hyperlinks, while CSS describes how HTML elements are to be displayed.

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
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
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
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.
zorvek (Kevin Jones)ConsultantCommented:
What dates did you enter?

Kevin
Frank FreeseAuthor Commented:
in A12 2/28/2011
in F12 2/28/2011
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
zorvek (Kevin Jones)ConsultantCommented:
It works at my house.

See attached.

Kevin
Q-26856092.xlsx
Frank FreeseAuthor Commented:
I also have a conditional formatting that also applies to G12 - could that be the problem
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
unchecking the ignore blanks was the difference - great - I'll close this and open a continuation
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.