Solved

restricting data entry

Posted on 2011-03-01
14
211 Views
Last Modified: 2012-05-11
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
0
Comment
Question by:Frank Freese
  • 6
  • 6
  • 2
14 Comments
 
LVL 15

Assisted Solution

by:weinberk
weinberk earned 50 total points
ID: 35010390
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010452
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
 
LVL 15

Expert Comment

by:weinberk
ID: 35010492
I agree, that's a much better solution.
0
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010554
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
 

Author Comment

by:Frank Freese
ID: 35010627
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010645
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
 

Author Comment

by:Frank Freese
ID: 35010827
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010835
What dates did you enter?

Kevin
0
 

Author Comment

by:Frank Freese
ID: 35010877
in A12 2/28/2011
in F12 2/28/2011
0
 

Author Comment

by:Frank Freese
ID: 35010943
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
 
LVL 81

Expert Comment

by:zorvek (Kevin Jones)
ID: 35010956
It works at my house.

See attached.

Kevin
Q-26856092.xlsx
0
 

Author Comment

by:Frank Freese
ID: 35011000
I also have a conditional formatting that also applies to G12 - could that be the problem
0
 
LVL 81

Accepted Solution

by:
zorvek (Kevin Jones) earned 450 total points
ID: 35011034
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
 

Author Comment

by:Frank Freese
ID: 35011093
unchecking the ignore blanks was the difference - great - I'll close this and open a continuation
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

INDEX and MATCH can be used to great effect to replace HLOOKUP and VLOOKUP as it does not have the limitation of needing the data to be sorted so that the reference value is in the first column or row. It also has the ability to perform a bi-directi…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

929 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now