Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Excel Columns Validaton

Posted on 2011-03-02
16
Medium Priority
?
184 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
ID: 35023868
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:Michael Fowler
ID: 35023873
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
ID: 35023892
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:varvoura
ID: 35024046
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
ID: 35024374
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
ID: 35024976
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
ID: 35025477
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
 

Author Comment

by:varvoura
ID: 35025655
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:Michael Fowler
ID: 35025745
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
ID: 35055267
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:Michael Fowler
ID: 35062671
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
ID: 35067255
Thanks very much Michael.

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

0
 

Author Comment

by:varvoura
ID: 35067826
Hi Micheal,

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

Expert Comment

by:Michael Fowler
ID: 35073835
Here is the file again

Michael
test.xls
0
 
LVL 23

Accepted Solution

by:
Michael Fowler earned 2000 total points
ID: 35320044
I believe that I provided a valid working solution to this question

Michael
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Microsoft's Excel has many features that most people will never need nor take advantage of.  Conditional formatting is one feature that you may find a necessity once you start using it.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

916 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