How to set Multiple DATA validations in a single cell in Excel

Using Data Validation in Excel, I generated the drop-down lists below.

In the row "Delivery" under the Column Status, I have a dropdown list that says: Delivered, Not Delivered.

I also have a dropdown selection for the row "Shipping" under the status column that says "Shipped" and "Not Shipped"

What I want to do is to establish data validation in Custom again, such that if the delivery status is "Delivered," the user should not be allowed to select any list from the Row "Shipping" under the status column

Could someone help me how to accomplish this in excel?

Excel

Details          Status
Delivery         (in Dropdown "Delivered", "Not Delivered")

Shipping         (in Dropdown  "Shipped", "Not Shipped")

CodePudding user response：

1. Anywhere in your Sheet write in 2 cells "Delivered" and "Not Delivered" 1a) Name this Range = "Delivered"

2. Use the Data-Validation in your Status Column for only choosing the o.m. values

3. Use the Data-Validation in your Shippling Column with the formula

   =indirect(A3)

in my example A3 is the correspondenting cell.

As result, if you click on a dropdown in Shipping-Column you can choose the values if Status-Cell is filled, if Status-Cell is not = "Delivered", the dropdown will not open.