PDA

View Full Version : [SOLVED] Data Validation - Multiple Criteria



mykal66
05-18-2015, 12:43 AM
Hey everyone.

I am setting up a spreadsheet to try and force users to stop submitting it with missing information. I've set up a custom validation on many of the cells that will not allow data entry unless they put something in the preceding cell
=OFFSET(J3,0,-1)<>"" which works great until i come to a cell that already uses a list.

I've Googled and think i should be using If(AND... to get multiple criteria to work but just keep getting a formula error message. In the example above the Cell J3 also uses the named range YN to generate a drop down list so i need to combine the existing drop down list with the offset formula above but really stuck.

Could anyone help or tell me where I'm going wrong please?

Thank you

Mykal13422

Yongle
05-18-2015, 12:55 AM
Can you post the workbook, avoids having to re-create.
thanks

mykal66
05-18-2015, 01:14 AM
Hi Yongle - thank you.

I've added example to my original post. H2&I2 use the offset formula to check something has been entered in previous cells. I want to be able to do the same with J2 but retain the drop down list too

Thanks again

Yongle
05-18-2015, 08:47 AM
This is one way to achieve what you want
(with the caveat that this solution would not be elegant if the list contained a lot of possible values!!)

Test in attached workbook - borders included around the cells where new data validation is included.
See what happens if there are values in cell above in columns J and K

To achieve this:
1 In Cell J3, Data Validation with this formula as the "Source"

=INDIRECT(J2)

(which makes J3 dependant on the value of J2)

2 Create named ranges to match the ALL possible values for cell J2 and put the possible values for J2 in those named ranges

(You had named range YN which contains the List Values for J2, so I created named ranges "Yes" and "No", and pointed them at the same cells as named range YN )

mperrah
05-18-2015, 01:43 PM
Not sure how the submission is taking place, but you can fire this before that action"


Sub notDoneDontSubmit()
' run on key press of submit button or what ever sub that is run to submit
Dim lc, i As Long

lc = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lc
If Cells(2, i).Value = "" Then ' row value can be activeCell or variable
MsgBox ("Please fill in all cells before submitting.")
End If
Next i

End Sub

tag Yongle

mperrah
05-18-2015, 01:46 PM
This puts the focus on the cell missing a value.
Not sure the range of cells you want to police, I used column 1 to last in Row 1

Sub notDoneDontSubmit()
' run on key press of submit button or what ever sub that is run to submit
Dim lc, i As Long

lc = Cells(1, Columns.Count).End(xlToLeft).Column

For i = 1 To lc
If Cells(2, i).Value = "" Then ' row value can be activeCell or variable
MsgBox ("Please fill in all cells before submitting.")
Cells(2, i).Select
End If
Next i

End Sub

mperrah
05-18-2015, 01:56 PM
Checked original attachment
If your only looking at G to K
and from Row 2 to last row, try this:


Sub notDoneDontSubmit()
' run on key press of submit button or what ever sub that is run to submit
Dim lc, lr, c, r As Long

' lc = Cells(1, Columns.Count).End(xlToLeft).Column
lr = Cells(Rows.Count, 7).End(xlUp).Row

For c = 7 To 11 ' checking G to K only
For r = 2 To lr
If Cells(r, c).Value = "" Then ' row value can be activeCell or variable
MsgBox ("Please fill in all cells before submitting.")
Cells(r, c).Select
End If
Next r
Next c

End Sub

mperrah
05-18-2015, 02:23 PM
This will insert the validation drop downs in J and K
note: requires something in Column I, we can use another column to target for rows.count

Sub AddValidateLoop()

Dim i, lr As Long

lr = Cells(Rows.Count, 9).End(xlUp).Row

For i = 2 To lr
If Cells(i, 9).Value <> "" Then
With Range("J" & i & ":K" & i).Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:="Yes, No"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.InputMessage = ""
.ShowInput = True
End With
End If
Next i
End Sub

SamT
05-18-2015, 04:02 PM
Create a UserForm for all data input. In VBA right click on the ThisWorkbook in the Project Explorer and >> Insert >> UserForm. Then you can write code that validates a thousand things as each bit of data is entered, selected, computed.

Then never let the Users see the actual worskeets.

mykal66
05-19-2015, 01:48 AM
Hi everyone and thanks you for all of the options you have offered. I will have a play with them all at some point today - many thanks to all of you

Mykal