data validationgoogle sheets
On a Google Sheet, I want to have a dropdown list of options to choose from. However, these options are split into different sections and so I would need to select multiple sets of data. I can get the dropdown to work if I just do
A3:A54
But this is a problem because then the dropdown includes some titles which I don't want as options. However, If I try to do something like
A3:A14 A19:A23
It doesn't work. So Is there a way I can get a dropdown to use multiple sets of data?
Thanks!
Maybe this has changed in new Google Sheets because in the second and subsequent Forms it seems possible to select Responses, Choose response destination:
New sheet in an existing spreadsheet..., Choose:
find the Response Sheet for the first Form, Select and for this to work.
Use query instead of filter for this:
=query(importrange("URL","A:J"), "select * where Col3 = 'Yes'")
The command query can refer to the columns of the array that it receives in an abstract way: Col1, Col2, and so forth, without being tied to their placement within the sheet. So, it filters the thing "in the cloud" and then puts the result into the sheet.
In contrast, filter can only refer to columns by the cell names like C:C. This means that the array must first be placed into the sheet, and then be filtered in place according to its own values. This runs into circular dependency.
Best Answer
This used not to be possible, and I suspect still isn't. A workaround of sorts may be to enter:
in a spare area, and then apply whatever range that occupies to the list range for your dropdown.