Validate the cell is not empty and the value either 'Yes' or 'No' in Excel

Question

How can I give multiple validation to a cell in excel. I have already given the validation to the cell value as

List Value 'Y','N' . at the same time the if there is value  in the column A, the cell should  not be blank , it should be filled with either 'A' or 'N' how can I give the validation. If there is value , the column B should  not be skipped without Y or N

for example  if the column A 

A                    Status

XXXXX           Y

YYYYYY          N

ZZZZZ          Y

AAAAA        

CCCC            N

Answer 1

Data Validation cannot prevent cells from being blank, so just use Data Validation of type List, with "Y" and "N" as values.

We'll use VBA to prevent cells from being cleared:

Right-click the sheet tab.

Select 'View Code' from the context menu.

Copy the following code into the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cel As Range

    If Not Intersect(Range("B:B"), Target) Is Nothing Then

        For Each cel In Intersect(Range("B:B"), Target)

            If cel.Value = "" Then

                Application.EnableEvents = False

                Application.Undo

                Application.EnableEvents = True

                MsgBox "Clearing cells in column B is not allowed!", vbExclamation

                Exit For

            End If

        Next cel

    End If

End Sub

Switch back to Excel and don't forget to save the workbook as a macro-enabled workbook (.xlsm).

Answer 2

How can I apply the Listvalidation and custom validation at the same time for the column B of an excel. The column B should be poppulated with Y or N values and also it should not be blank. Any help would be very appreciated.

Pol

Answer 3

Please can you give the example how to be given the value Y or N without accepting empty in column B

Answer 4

Hi Polachan,

Regarding the multiple validation query, let us know if you need further assistance.

Regards,

Tina

Answer 5

Hi Polachan,

Thanks for the query.

To apply multiple data validation in one cell, we will need to use the OR, AND operators. Please check the example 2 and example 3 inthis article to get some idea.

Disclaimer: Microsoft provides no assurances and/or warranties, implied or otherwise, and is not responsible for the information you receive from the third-party linked sites or any support related to technology.

Regards,

Tina