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

Dixan Thomas 100 Reputation points
2023-09-07T10:04:21.14+00:00

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?

Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,448 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. AllenXu-MSFT 15,926 Reputation points Microsoft Vendor
    2023-09-08T07:36:06.9466667+00:00

    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.

    1 person found this answer helpful.

  2. Tanay Prasad 2,105 Reputation points
    2023-09-11T06:39:58.67+00:00

    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.

    0 comments No comments

  3. Marco Urban 0 Reputation points
    2023-09-11T16:21:35.24+00:00

    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
    
    0 comments No comments