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.
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.
Riny_van_Eekelen You're a lifesaver! This is exactly what I've been looking for.
In order to make this table more user friendly, I was thinking if there is such a chance that the user might key in wrong information in previous records in the outgoing table, although the user could amend the density, but there is no way the user can select the correct Lot as it's restricted with the data validation. Though it can be resolved by VBA coding, but this will be the last resort that I would try because I would like to keep this file as simple as possible. Appreciate if there is any good ideas from you.
Last but no least, thanks a lot for the suggestion in validation rules for the outgoing quantity. It makes this file one step closer to mistake free.
Why don't use A1:A5 in data validation directly?
