I have several values placed in several cells as follows. I have already defined named ranges: name1
for D3, name2
for F3, name3
for J3, name4
for N3.
Now, I would like to make a cell with data validation; values in its dropdown list are v1
, v2
, v3
and v4
.
I would like the source of this data validation to be based on named ranges name1
, name2
, name3
and name4
. As a result,
- Even though the location of the cells changes, as long as the named ranges are correct, we are sure that the dropdown list could contain correct values.
- We could modify the values in the named ranges, and the values in the dropdown list are automatically updated.
Does anyone know how to achieve this?
Edit 1:
To make this question less hard, let's assume that all these named ranges are always in Row 3
, and Row 3
does not have other data. I tried to define another named range all
with =FILTER($3:$3,$3:$3<>"")
, As a result, =all
in a cell did return all the values.
However, I wrote =all
in the source of data validation, it returned The Source currently evaluates to an error. Do you want to continue?
. Clicking on Yes
returned an empty dropdown list.
I guess maybe it is because Source of data validation does not work well with dynamic array functions like FILTER
. Does anyone have another formula (to remove blanks in a list) with traditional functions to try?