2

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!

3
2

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

3
  • Thank you for your helpful answer. Unfortunately, there are a few problems with the method you recommended: 1) My main objective, which I was maybe not clear enough about, 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. I don't know if this is feasible. I guess I wish there was a =DROPDOWN(X;Y;Z) function that just creates a drop-down list with X Y and Z, but it seems there isn't.
    – Nathan96
    Oct 31 '21 at 23:06
  • 2) I plan to extend this to a whole column of cells in my sheet, which if I was to use your method would mean I would have to reserve a whole D2:D10 equivalent area for each cell of my column to put each array formula in. This is quite inconvenient with a lot of cells to apply the formula to...
    – Nathan96
    Oct 31 '21 at 23:09
  • Thanks for your comment. I suggest you to edit the question. Put your goal at the top and edit the rest accordingly.
    – Rubén
    Oct 31 '21 at 23:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.