Forum Discussion
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.
Riny_van_Eekelen Thank you very much for your explanation. Finally I get the idea why it works that way. Actually the reason why I would want to put all the selection into a string in a cell is due to the tables that I built. Kindly refer to the attachment for better understanding for my explanation below.
I have 2 tables (incoming stock (blue) and outgoing stock(orange)), and I would like to reduce the human mistakes with manual input. Thus, the density and the lot columns in outgoing stock table are using drop down list. However, due to the density types and lot will be increasing over the time, I have limited the drop down lists to show those with balance qty (incoming table) >0.
Currently, the data validation source for Density column (column M) and Lot (column N) are from column I and column K respectively. I am using Offset, Match and Countif for the dynamic drop down list in Lot column. Actually, I was trying to use each cell in column R as the source for the drop down list for Lot column.
Is there any workaround to get this idea done? Thanks again.