Forum Discussion
Dropdown list: my function with filter & Index does not work
I want to make a dynamic dropdownlist.
The function i want to use is a first filter which give two columns as output, embedded in a Index function because I only want the second column.
=INDEX(FILTER(_Resources;_Company_in_Resources='Cost data v2'!A19);;2)
if i put this formula in a cell it gives exactly what I want (a list of names of people linked to the company specified.).
So, I went to the cell and via Data Validation, selected 'List' in the validation criteria dialog and copy-pasted the formula in "Source". When I click on OK i get an error saying that the source evaluates to an error. What is going wrong? (The formula on its own merit is ok since it works when using it in a cell.) Is INDEX/FILTER not allowed?
Thanks for your help.
The Source of a Data Validation list must be a range, but FILTER returns an array, not a range. So you cannot use FILTER in Data Validation.
You can use the formula in a cell, say Sheet1!W2, then use =Sheet1!$W$2# as Data Validation formula,
4 Replies
- will not print
Please start a new discussion for your question.
The Source of a Data Validation list must be a range, but FILTER returns an array, not a range. So you cannot use FILTER in Data Validation.
You can use the formula in a cell, say Sheet1!W2, then use =Sheet1!$W$2# as Data Validation formula,
- Many thanks for your confirmation. I suspected it but had hoped via the index formula to have found a workaround by choosing 1 column only) 🙂
