Forum Discussion
Data Validation with a string in a cell
I have used Textjoin to create a string with comma delimiter in cell C1, lets say the string is "a,b,c,d,e" in the cell. I tried to refer $C$1 as the source for the list option in data validation, but the drop down list showed one selection with "a,b,c,d,e" string instead of 5 separate selections. I wonder why this works when "a,b,c,d,e" was directly keyed in into the source of data validation but not if we refer to a cell.
8 Replies
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.
Dongda I have attached a slightly revised workbook where the filter in J2 takes both the "Density" and the "Balance Qty" into account. In order to make it work, I needed to "step outside" the structured table references (Sorry!) and insert a helper formula in J1 that determines the last entry in column L.
And then I also changed the validation rules for the outgoing quantity to make sure that it's negative AND that the "Balance Qty" will not go below -zero-
Please let me know is this resolves your issue.
Why don't use A1:A5 in data validation directly?
