Create Dependent Data Validation List

[astrodon]

I am using Excel 365 on a Windows 11 laptop.

I have a lookup table having ~14,000 rows with 122 columns. What I am interested in two particular columns as shown below.

I have a data entry sheet that depends on these two columns in a table (tblNutrients)

From the data entry table I wish to have only the Food Name items to show in the drop down based on the Category selection. There are 14,000 food names to choose from in 22 different categories. Everything I have seen online for dependent validation lists is to create a new table for each possible category. That would add an additional 22 tables totaling ~685,000 items. Not very efficient.

Any help would be appreciated

TIA

doco

 

[Akuini]

That would add an additional 22 tables totaling ~685,000 items. Not very efficient.

It won't be that many, there are just 14,000 items divided into 22 columns.
Check out this tutorial from Mynda Treacy from My Online Training Hub:
Easy Dynamic Dependent Data Validation Two Ways
with this method the helper table will be created by formula.

Another option is using macro, you only need 1 column as helper table, check out this article:
3 or More Dependent Data Validation, with VBA, easy to set up

Additionally, you might want searchable functionality on the data validation, considering the size of your data. Here's an add-in that I've created to provide that functionality:
Excel add-in: “Search deList”

 

[astrodon]

Thanks. I will look into it. I did get a solution but it involved creating a new table with the 22 food groups as column headers. Then moving the foods for each group to the table. Pretty messy way in my opinion.
I don't understand why SQL has been abandoned by excel. A simple select food from table where group = group_dropdown order by food. Done deal.
Much to learn this one. Yoda