I have a table where I want users to fill values off a list using data validation. The content of the validation list should be based on values entered in two other tables which are defined as dynamic named ranges (say list1 and list2). I need my validation list (say listAll) to update automatically whenever values are changed or added in list1 or list2.
For example: I could easily achieve this with a macro triggered by changes in list1 or list2, but in this particular case using VBA is not allowed. I can define a named range which simply REFERS TO: =list1, list2. This does indeed create a virtual combination of the lists but only usable in functions like count or sum. Using the combined list in data validation results in an error message:

The list source must be a delimited list, or a reference to single row or column.

From similar questions

CodePudding user response：

This is the solution I found – in three steps (did I say cumbersome?):

Step 1:
I call each of the lists, both in one column (D in example), one well below the other – to make sure the top one has enough place to "evolve".

Step 2:
I use the filter function on the original column to have the two lists appear in a column stacked on top of each-other:
=FILTER(D:D,D:D<>"") I tried entering this as the definition of a new name, but it is unusable for a data validation list ("evaluates to an error"). Therefore –

Step 3:
EDIT - following excellent improvement suggested by @Harun24hr:
I can now refer to the cell from step 2 directly in the data validation window, like this: =$F&1#.

Alternatively I can create a new name with the same reference, which I can use in data validation or for other uses. The new name is dynamically based on list1 and list2.