Validate comma-separated list of values

I am already working on this for quite a few hours. Since I assume it to be a standard use case, I would like to ask for the help of the forum to give me a hint…

I have a list of “allowed values” in a given range on my worksheet.

I would like to use the Data Validation function to guarantee, that a certain cell contains a comma-separated list containing only values from this list.

In an example:
Cells with allowed values contain: “Val1”, “Val2”, “Val3”, … (each value in a separate cell, let’s say A1:A3).
The data validation should allow the value “Val1; Val3” but not “Val2; Val4; Val3”.
I already did some macro writing (using the Split+Trim function), but wasn’t successful in the end. Furthermore, it seems a bit of an “overkill” in my eyes… Isn’t there an easier way to realize this that does not require the use of macros?

Many thanks and best regards,
Dominik

Sorry if my description wasn't fully understandable.

Please notice the attached Excel file. It contains a list of possible values in the left-hand side and a set of cells with comma-separated entries (like "V1,V2,V3") to be validated.
I used the following small VBA snippet to do the job:

Please Login or Register  to view this content.

Using this formula in a second column and referencing its value in the "data validation/custom"-formula does the job. Still it requires VBA macros which I would like to avoid.
Isn't there some kind of clever "array formula" or such that unfolds the comma-separated string and does the item-by-item-validation in one nice formula?

Many thanks and best regards
Dominik

The only thing that bothers me is the necessity for activating VBA macros. I would prefer to have some kind of formula that does not require VBA.
Anyone sees any possibility?