How to create drop down that only functions when another cell is not blank?

Thanks Chris.
However, I cannot use your solution since I can only have one data validation condition in a cell, and since I am already using the dependent drop down in A22, I cannot also add your suggested formula for ensuring S17 in not blank. Is there a way to do both?

Hi @Scott R Pelton,     

Thank you for reply and for clarifying your scenario. 

We can achieve both requirements: the dependent drop-down list in A22 and ensuring that S17 is not left blank using a single, combined data validation rule. The key is to make the drop-down in A22 conditional: it will only allow input when S17 has a value. If S17 is empty, A22 will effectively block any input by referencing an empty list. 

We’ll modify the data validation on A22 to use a formula that checks S17. The logic is: 

• If S17 is blank, the formula returns an empty list, preventing input in A22. 
• If S17 has a value, the formula uses INDIRECT(S17) to pull the corresponding named range for the drop-down. 

Steps to implement: 

1/ Select cell A22. 

2/ Go to Data > Data Validation. 

3/ In the Settings tab: 

• Set Allow to List. 
• In the Source box, enter a formula like: 
  =IF($S$17="", "", INDIRECT($S$17)) 

This mean if S17 is blank, the formula IF($S$17="", "", …) returns a blank reference for the list, essentially meaning “no valid entries.” If S17 has a value (say “20044290” or “20044297”), the formula uses INDIRECT($S$17) to pull the corresponding named range for the drop-down items (just like your current dependent list setup). So when S17 is filled, A22 will show the appropriate drop-down list; when S17 is empty, A22’s validation has an empty allowed set (so any non-blank entry fails validation). 

4/ Uncheck the Ignore blank option.  

With “Ignore blank” turned off, Excel will not allow A22 to be filled when S17 is blank. (If it were checked, Excel might ignore the validation when S17 is empty)Enable Data Entry in a Cell only if a Dependent Cell is Filled in Excel 

5/ Click OK to apply the new validation settings. 

Test: 

• If S17 is blank, try clicking on A22. You should see that the drop-down either shows no options or doesn’t activate. If you try to type a value into A22, Excel will immediately prevent it (you’ll get the error alert message as feedback). Essentially, A22 won’t accept any input except a blank (which maintains it empty) until S17 has something selected. 
• If you select a value in S17, A22 will once again show the dependent list as expected. You can pick an item from the drop-down or type one of the allowed values. The data validation will accept entries that match the list tied to S17’s value, just like before. 

This approach ensures that A22 remains dependent on S17 and enforces the requirement that S17 must be filled before any input is allowed in A22. 

For reference: How to Apply Data Validation Based on Another Cell in Excel - Excel Insider 

Note: Microsoft is providing the above information as a convenience to you. Some of the websites are not under Microsoft's control. Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. Please make sure that you completely understand the risk before retrieving any suggestions from the above link    

Please try it out and let me know if you run into any issues or need assistance.