Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 224
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 6
  • 6
  • 2
2 Solutions
 
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
 
Berkson WeinTech FreelancerCommented:
I agree, that's a much better solution.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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
 
Frank FreeseAuthor Commented:
unchecking the ignore blanks was the difference - great - I'll close this and open a continuation
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now