Solved

Maintain data integrity

Posted on 2011-03-01
13
229 Views
Last Modified: 2012-06-22
Experts,
In my spreadsheet, A12 is date entered, F12 is invoice date and G12 is invoice amount.
If either A12 or F12 are blank you cannot enter data in G12 - OK, that works.
However, the user can go back and delete the date in A12 or F12 or both afterwards. I would like to prevent that from happening such that if there is data in G12 you must have data in A12 and F12.
0
Comment
Question by:Frank Freese
  • 7
  • 6
13 Comments
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011490
Would this solution work for you?

Id the data is deleted in A12 or F12 then G12 is cleared automatically?

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011512
Something like this?

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A12")) Is Nothing Then _
    If Len(Trim(Range("A12").Value)) = 0 Then Range("G12").ClearContents

    If Not Intersect(Target, Range("F12")) Is Nothing Then _
    If Len(Trim(Range("F12").Value)) = 0 Then Range("G12").ClearContents
End Sub

Open in new window


Sid
0
 

Author Comment

by:Frank Freese
ID: 35011648
interesting idea - can I do this

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A12:A21")) Is Nothing Then _
    If Len(Trim(Range("A12:A21").Value)) = 0 Then Range("G12:G21").ClearContents

    If Not Intersect(Target, Range("F12:F21")) Is Nothing Then _
    If Len(Trim(Range("F12:F21").Value)) = 0 Then Range("G12:G21").ClearContents
End Sub
0
 

Author Comment

by:Frank Freese
ID: 35011714
tried that - it did not work.
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011716
Well not exactly.

You want to clear the entire range G12:G21 if say just F14 is empty? Maybe not.

Is this what you want?

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Rng As Range, acell As Range
    Set Rng = Union(Range("A12:A21"), Range("F12:F21"))
    If Not Intersect(Target, Rng) Is Nothing Then
        For Each acell In Rng
            If acell.Value = 0 Then Range("G" & acell.Row).ClearContents
        Next
    End If
End Sub

Open in new window


Sid
0
 

Author Comment

by:Frank Freese
ID: 35011870
real close -
for example, the user can enter data in other cells such as A14:G14. When we clear data can we just go ahead and clear that range A14:G14, as example?
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 30

Accepted Solution

by:
SiddharthRout earned 500 total points
ID: 35011967
You mean this?

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    Dim Rng As Range, acell As Range
    Set Rng = Union(Range("A12:A21"), Range("F12:F21"))
    If Not Intersect(Target, Rng) Is Nothing Then
        For Each acell In Rng
            If acell.Value = 0 Then Range("A" & acell.Row & ":G" & acell.Row).ClearContents
        Next
    End If
    Application.EnableEvents = True
End Sub

Open in new window


Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35011974
But there is a hitch.

If the other cell is empty and you type anything in A12, it will still clear the row.

Sid
0
 

Author Comment

by:Frank Freese
ID: 35012418
woops - by clear the row - does that include formulars in H12...H14, which are protected
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35012424
No. Only cells from A to G

Sid
0
 

Author Comment

by:Frank Freese
ID: 35012443
perfect - thats what we wanted - can we try it?
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 35012447
Sure :)

Sid
0
 

Author Closing Comment

by:Frank Freese
ID: 35037304
thank you
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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

911 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

20 Experts available now in Live!

Get 1:1 Help Now