0

I have a sheet with a dropdown menu in A1, and a cell A2, with values that will be the result originated from a script.

I want a certain option from the dropdown menu (the first one, to be precise) to be selected automatically when a certain value is present in A2.

Here's a simulation: https://docs.google.com/spreadsheets/d/1x-pmDmB6mbyjXFY0rHOkIzkpNagRJXif9dWNj3TJHkU/edit?usp=sharing

In A1, I want to write the formula: If A2 is equal or less than zero, I want option 1 to be force displayed/ automatically selected in A1. If A2 is more than zero, then I want the manually selected option to remain as it is.

jcodi
  • 39
  • 2
  • 10
  • You should be able to achieve this with dynamic dependent dropdowns. You'll find a post regarding this [link](https://stackoverflow.com/questions/21744547/how-do-you-do-dynamic-dependent-drop-downs-in-google-sheets) here and a pretty detailed explanation [link](https://infoinspired.com/google-docs/spreadsheet/dynamic-dependent-drop-down-list-in-google-sheets/) here as well. (I assume that you want to display different dropdown items depending on your value in A2) – bartinmader Jan 22 '19 at 16:03
  • Not really, I don't want to add new dropdown menus based on a selection on a first dropdown. I want the value from a cell to select automatically which of the items in a dropdown menu are being displayed in the cell with a dropdown. – jcodi Jan 22 '19 at 19:42
  • I just submitted an answer, trying to highlight the similarities with dynamic dependent dropdowns. I hope this makes things a bit clearer. – bartinmader Jan 22 '19 at 20:58

1 Answers1

0

I'm not completely sure if we're talking about the same thing, but I believe this has a lot in common with how dynamic dependent dropdowns are created in Google Sheets.

The only main difference is that you don't rely on a dropdown choice for your condition, but any cell value of your choice.

If the Value in F1 is negative, only option 'A' is shown in the dropdown.

If the Value in F2 is positive, all the other options are displayed as well.

So how does this work?

The value in G1 is:

=IF(F1>0,filter(B:B,A:A="POS"),filter(B:B,A:A="NEG"))

This basically means that if F1 is greater than zero, only the values in Column B, whose corresponding value in Column A is POS, show up in Column F.

If F1 is zero or less than zero, only the values of Column B, whose corresponding value in Column A is NEG, show up in Column F.

Accordingly, we can now use Column F (the range G1:G8) as a 'dynamic' reference for the data validation of a dropdown. In the example, this was done in F2.

This YouTube video explains the underlying filter mechanism pretty well. I just expanded it with an IF-statement.

bartinmader
  • 266
  • 3
  • 10