Solved

Excel Columns Validaton

Posted on 2011-03-02
16
174 Views
Last Modified: 2012-05-11
Hi all,

How can I validate columns in my spreadsheet based on values in other columns?

For example,

1. If a User Enter a Value in Column A, then columns B, C, D MUST also be completed(cannot be left blank)

2. If the value is "Yes" in Column N, then Column O should also have a value

3. If Column "N" is Blank, then Column "O" must also be blank.

Your prompt response is much appreciated.
varvoura
0
Comment
Question by:varvoura
  • 6
  • 5
  • 3
  • +1
16 Comments
 
LVL 11

Expert Comment

by:MajorBigDeal
Comment Utility
1. =OR(LEN(A1)=0,AND(LEN(B1)>0,AND(LEN(C1)>0,LEN(D1) > 0)))

2. =OR(N1<>"Yes",LEN(O1)>0)

3. =OR(LEN(N1)>0,LEN(O1)=0)
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Is this what you had in mind.

The code prompts the user when invalid entries are made.

I have attached a test file.

Michael
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Range.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Range.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Range.Row
      End If
   End If
End Sub

Open in new window

test.xls
0
 
LVL 11

Expert Comment

by:MajorBigDeal
Comment Utility
Each above formula returns true if the validation is met, false otherwise.

1. If A has no value then it is always true, but if there is a value in A then B, C, and D must also have values or it will return false.

2. If N is not "Yes", it will always be true, otherwise, O must contain a value for it to be true.

3. If N is not blank, it will always be true, otherwise, O must be blank for it to be true.
0
 

Author Comment

by:varvoura
Comment Utility
Hi guys,

Either one of those maybe a solution but I am not a vb or excel experts so here's how I went about testing them:

1. MajorBigDeal - I click the "fx" which I believe the function, then I copied & pasted your first line.
Then, I deleted values in column B where column A still have values, I was able to save & close the spreadsheet which is not right. I should have been prompted with an error message that B should also have a value.
I am not sure what I am doing wrong, but are you are able to give me detailed step on how to implement/test this.



2. Michael74 - Your code is also great, however, didn't work for me.
How I tested this:
1. I opened the Excel worksheet
2. Went to Tools - Macro - Visual Basic Editor
3. Copied and paste your code above there
4. Save & closed the worksheet
5. Went back in and tried to remove data from column B where A is not blank but I was still able to save the worksheet without errors.

If this has worked, then I should've been prompted with an error message. Is this correct?

Thanks again guys
0
 
LVL 5

Expert Comment

by:Pabilio
Comment Utility
Hi varvoura,

You'll need two different codes in order to work as you need.

1) To clear values in column O when there is an empty value in Column N you should use this code in the Sheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ran As Range
Set ran = Range("N:O")
If Intersect(Target, ran) Is Nothing Then Exit Sub
If Range("N" & Target.Row) = "" And Range("O" & Target.Row) <> "" Then
Range("O" & Target.Row) = ""
End If
End Sub


2) To avoid the saving and prompt the user when the conditions you said are not met, then this code must go in the Thisworkbook module:
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
 Dim rng As Range, cell As Range, rng2 As Range, lrow As Long
 
 With Sheets("Hoja1")
    lrow = Cells(Cells.Rows.Count, "A").End(xlUp).Row    '<-- Finding last row basis of F Column
 
    Set rng = Range("B1:D" & lrow)
    Set rng2 = Range("N1:N" & lrow)
   
    For Each cell In rng
        If Range("A" & cell.Row).Value <> "" And cell.Value = "" Then
             MsgBox "Cell " & cell.Address & " is Empty... Please type a value"
   SaveAsUI = False
Cancel = True
        End If
        Next
   For Each cell In rng2
        If Range("N" & cell.Row).Value = "YES" And Range("O" & cell.Row).Value = "" Then
        MsgBox "Cell " & cell.Address & " Need an asociate value in column O "
   SaveAsUI = False
Cancel = True
   End If
   Next
   End With
End Sub

This will avoid saving unless your conditions are met.

Hope it works for you.
Roberto.
P.S. See attached file.
Libro1.xls
0
 
LVL 11

Expert Comment

by:MajorBigDeal
Comment Utility
In order to create a msgbox, you will need to use a VBA approach that is being proposed by others.  But when would you want this to happen?  Is the user going to press a button in order to run the validation?  Are you really going to try to stop them from saving and closing the spreadsheet?  What if they have entered a bunch of data but it is not yet correct?

I use formulas like the ones I showed you to validate code, for example, by using conditional formatting, cells that do meet the requirements can be highlighted in different colors.  Or a message can be displayed in a cell. It is simple and doesn't require VBA.  BTW, I am good at VBA and writing "bullet-proof" VBA applications in excel requires a lot of code similar to what is being offered by others.  It is very labor-intensive.  You see how much code is being generated just to do a few simple validations.

I think we might want to take a step backwards and discuss the application that you are actually trying to create.  Are you trying to collect data from a large number of users and then consolidate it? Or is this a "single-user-at-a-time" application? Will the data in a spreadsheet have many similar rows, or is it essentially a single form  with each field only being used once?   Depending on what you are trying to do, it may be that excel is only part of the solution that you are looking for.
0
 

Author Comment

by:varvoura
Comment Utility
Hi guys, you all are amazing, great code all over. I am working with some of your examples at the moment.

MajorBigDeal - Single user & used for data compilation & import - awsome code, don't know where to include it to make it useful.

Michael74 - awsome code, however, error on compilation even with your test file.

Pabilio - awsome code as well & I am working through some of it as we speak

One more question for everyone though:

Is there a way using VBA to highlight the errored cell in yellow. This is a lot simpler to the user to identify the cells which have the problem as the cell.Address is not too informational for novice users.

If highlighting is not possible, how can I then get the cell label rather than the address. For example, if $B$2 is blank whenever it is not suppose to, how can I display B2 in the error message box instead of $B$2.

thanks
0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:varvoura
Comment Utility
Hi again,

1. Can I make an entire column changed to upper case in VBA?

For example, where the user enters "y" to change it to "Y"

2. Can I force the user to enter a certain value in a column. For example, in some of the columns I want the users to enter "Yes" or "No". but I don't want the user to accidently enter "Y" or yes or "N" for No.

0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
sorry I have corrected the fault in my example. will have a look at your other requests tomorrrow.

To force a user to select an option use data validation. Sheet 1 column B has been setup with this.
http://support.microsoft.com/kb/211485

Note if copying the code into your workbook it needs to go into the sheet which validation is to be done eg in my example it in sheet 1

Michael
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Target.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Target.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Target.Row
      End If
   End If
End Sub

Open in new window

test.xls
0
 

Author Comment

by:varvoura
Comment Utility
Hi Michael/everyone,

For sheet 1 in test.xls column B is exactly what I need, however, i need it to start on row 2 as my data starts on row 2 and row 1 is only for column titles. The last thing i want is a drop down to appear on column title for users to select from because they will try.

Any suggestions everyone?

Thanks
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Just remove the validation on the first cell of column B.

I have updated the test file to ignore row 1 and remove the validation on these cells

Michael
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
   If Not Intersect(Target, Columns(1)) Is Nothing And Target.Row <> 1 Then
      If Range("B" & Target.Row).Value = "" Or _
            Range("C" & Target.Row).Value = "" Or _
            Range("D" & Target.Row).Value = "" Then
         MsgBox " You must have entries in columns B, C and D for row  " & Target.Row
      End If
   ElseIf Not Intersect(Target, Columns(14)) Is Nothing And Target.Row <> 1 Then
      If Target.Value = "YES" And Range("O" & Target.Row).Value = "" Then
         MsgBox " You must have entries in column O for row " & Target.Row
      ElseIf Target.Value = "" And Range("O" & Target.Row).Value <> "" Then
         MsgBox "Column O must be empty for row " & Target.Row
      End If
   End If
End Sub

Open in new window

test.xls
0
 

Author Comment

by:varvoura
Comment Utility
Thanks very much Michael.

I have already worked this out today and implement it at work.

0
 

Author Comment

by:varvoura
Comment Utility
Hi Micheal,

the new text.xls is not compiling, there's an error.
0
 
LVL 23

Expert Comment

by:Michael74
Comment Utility
Here is the file again

Michael
test.xls
0
 
LVL 23

Accepted Solution

by:
Michael74 earned 500 total points
Comment Utility
I believe that I provided a valid working solution to this question

Michael
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

What is a Form List Box? (skip if you know this) The forms List Box is the alternative to the ActiveX list box. If you are using excel 2007, you first make sure you have a developer tab (click the Orb)->"Excel Options"->Popular->"Show Developer tab…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
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…
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.

728 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

13 Experts available now in Live!

Get 1:1 Help Now