Build a dynamic data validation list from several named ranges-CodePudding

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 =TRANSPOSE(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?

CodePudding user response：

I would create a seperate location (maybe like a 'system' tab) to 'store' the temp list data. It can be done like this:

Use a filter to remove the empty cells like this:

=FILTER(D3:N3,D3:N3<>""))

Then transpose that output to make it a vertical list:

=TRANSPOSE(FILTER(D3:N3,D3:N3<>""))

This is now a dynamic list of your values. Now think about how large this list could be in the future. Currently it is 4 values, so let's assume 10. Reserve up to 10 cells for this dynamic list to allow it to grow. Now use these 10 cells as input for your dropdown-list.

CodePudding user response：

A named range needs to refer to a physical range. In other words you need to set the filter somewhere in your file (hidden, or different sheet) and refer to the spilled range in your named range.