Excel - Data validation rules

Question

Hi All,

How can I make a cell mandatory based on an answer in another cell?

For example, I want cells in Column K to be mandatory IF the users replied "To be Assigned" in Cell C.

Thanks in advance!!

Answer 1

Hi，

If you enter the "To be Assigned" in Column C, you can try this method:

1. Select Column C.
2. Go to Data- select Data Validation.
3. Select Custom and enter Formula ="To be Assigned"
   
4. Under Error Alert tab select Information style and enter Title/Error message: Enter in Column K
   
5. Then you will get the pop up dialog box:
   

If the Column C is a dropdown list, you cannot set 2 Data Validation in the same column or cell, I suggest you use the Conditional Formatting as a workaround:

1. Select Column K.
2. Go to Home- Conditional Formatting- select New Rule...:
   
3. Select Use a formula to determine which cells to format and enter the formula: =C1="To be Assigned"
   
4. Select the Format... option and set the format for Column K (fill the background color for Column K):
   

1. Then if you select the To be Assigned from Column C drop down list, the cell in Column K will change automatically:
   

Hope the information will help.

---

If the response is helpful, please click "Accept Answer" and upvote it.

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.