Can I have a drop-down list appear in a cell only if another cell contains a specific value?

Question

What I mean by this is that I want a cell to show a drop-down list (the only kind I know of is from Data Validation) only if another cell (NOT containing Data Validation/drop-down list) has a specific value.

Specifically, I want for example cell B2 to show a drop-down list (for example based on cells C2:C10) only if A2 is not empty AND not equal to 0.

In non-formula terms, this would be something like:

IF A2 is empty 
THEN B2 is empty
ELSE   IF A2=0
  THEN B2 is empty
  ELSE B2 shows drop-down list (with data from C2:C10) 

I haven't found a formula allowing me to do this. Is there any? To my understanding, Data Validation is not registered as a formula, so is there any way to show a drop-down list only if another cell has a specific value?

ETA: My main objective is to have B2 be absolutely empty if A2 is also empty (or equal to 0). This means I don't even want a drop-down arrow to show if A2 is empty or 0, but I do want the arrow to show if A2 is a value different than 0. Is that feasible?

Thanks in advance!

Answer 1

Data validation dropdowns only are shown when the valid values is set as list of values or range. The only way to automatically add/remove the data-validation dropdown button is by using a Google Apps Script trigger.

If you are looking for a no-Google Apps Script solution, and open to have the drop-down button show all the time, do the following:

1. add a formula that returns the values that you want to show in your dropdown to a cell. This formula should return an array of values
2. Set the range to the one having the formula.

Based on the rules stated in the question,

IF A2 is empty 
THEN B2 is empty
ELSE   IF A2=0
  THEN B2 is empty
  ELSE B2 shows drop-down list (with data from C2:C10) 

try the following formula, let say that you add it on D2.

=ArrayFormula(IF(OR(ISBLANK(A2),A2=0),,{C2:C10}))

Then on the data validation settings set the range for the data validation options as D2:D10

Answer 2

In B2 add formula... =IF(A2>1,"Default_Value","") ...the default value should be whatever you want to automatically show when A2 has value.

Add Data Validation Rule to B2 Dropdown--> Make sure first item is blank, you'll need to just add a single space for it to be blank. Second item in the list needs to be your default value that you put in the formula for B2. Then add all your other items.

You will not get a dropdown until A2 has value.

Hope this helps!