Data Validation with a string in a cell

Dongda When you key in the letters in the Source field for the data validation list, you are entering individual values (which may be texts) separated by commas. When the source is a single cell, you are populating the drop-down with the value of that one cell, even when it is a text string with commas.  Suppose you would want to populate a drop-down from a list containing persons names like "Last Name, First Name". You wouldn't want Excel to see this as two separate options in data validation for each entry in the list, just because there is a comma between the names.

But, if the source cells for your TEXTJOIN function are in a range of cells (for example A1:A5) you could write the text "A1:A5" in cell C1 and then use INDIRECT($C$1) as the source for your validation list.