Can we add mutiple data validations to same cell in excel?

Question

I need to have multiple data validations on a cell in excel.

1. have a drop down list to that cell with the values yes/no.
2. have a custom data validation that locks the cell as per the value in the cell A1 ie. is A1 has value "open" the cell with dropdown should get unlocked, A1 value is "closed" then the cell with dropdown should get locked.

How to achieve this?

Answer 1

Hi @Dixan Thomas,

It is not supported to apply two data validation on one cell. As a workaround, you can create a cascading dropdown list in Excel. Froe example, when A1="open", choices "yes" and "no" are available in another cell; when A1="closed", no choice is available in that cell. Take a reference to this article to create a cascading dropdown list: Make a dependent (cascading) drop down list in Excel.

Please let me know if you still have any question.

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi,

Yes, You can apply multiple data validations to the same cells.

Here's how you can do that-

1. You can create the drop-down list by Selecting the Cells range and going to Data > Data Validation. Choose "List" as the validation criteria and specify the source (Yes and No).
2. For the 2nd requirement, Go to Data > Data Validation. Choose "Custom" as the validation criteria.
3. In the "Formula" field, enter a formula that checks the value in cell A1 and allows input in C1 only if A1 is "open." If A1 is "closed," the input will be blocked. Here's the formula - =IF(A1="open", TRUE, FALSE)
4. To make this setup work, you need to protect your worksheet. Go to Review > Protect Sheet. Set a password if desired and specify any options you want for sheet protection. Ensure that "Select locked cells" is unchecked.

Best Regards.

Answer 3

You can store the following code in the VBA of your spreadsheet.

When a cell in column B is selected, it checks whether the value in field A1 = "open".

If this value matches, the drop-down list "Yes,No" is stored in column B.

Otherwise, the worksheet protection is removed, the column is locked, and the worksheet is protected to prevent input.

When you leave column B, the table protection is removed.

You may need to adjust the sheet name "sheets1" and the column "B:B".

Best Regards.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect(Target, Range("B:B")) Is Nothing Then
        If Range("A1").Value = "open" Then
            ActiveCell.Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:="Yes,No"
            Else
            Worksheets("sheets1").Unprotect
            Worksheets("sheets1").Range("B:B").Locked = True
            Worksheets("sheets1").Protect
        End If
    End If

    If Intersect(Target, Range("B:B")) Is Nothing Then
        Worksheets("sheets1").Unprotect
    End If
End Sub