0
votes

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,

  1. 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.
  2. 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?

enter image description here

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.

enter image description here

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?

you will need a helper column in which you have the four named ranges stacked and then refer to that range.Scott Craner
Is it possible to not display this helper column in sheets (eg, only define it in the Name Manager)?SoftTimur
no, it must be in the sheet. But you could put it off in a column that is off the screen.Scott Craner
or you could put it on a very hidden sheet that the user cannot see.Scott Craner