Data Validation - Multiple Criteria

[mykal66]

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

MykalBook1.xlsx

[Yongle]

Can you post the workbook, avoids having to re-create.
thanks

[mykal66]

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]

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]

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]

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]

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]

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]

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]

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