Dependent Drop Down List

MikeW952

New Member
Joined
Apr 15, 2022
Messages
3
Office Version
  1. 2007
Platform
  1. Windows
This is probably pretty basic, but I've been struggling with constructing (what according to internet examples is) a pretty simple function. I want to construct a dependent drop down list to allow selection of foods from various food group tables. Independent food group tables have been constructed for each (Fruit, Vegetable, etc.) food group. Tables are each defined as 'tables' in Excel, and are (single word) named. Additionally, a separate ('FoodGroup') table is constructed listing the 'names' of the various food tables. These tables are maintained on an unique ('Data Tables') sheet in the workbook. I want to 'pull' individual food information from the data tables into a ('Calorie Calculator') spreadsheet.
Simplistic internet examples indicate that dropdown lists and dependent dropdown lists may be constructed with direct reference to established 'tables' by 'table name'. Using 'Data Validation' and,
  • Setting Allow: 'List' and Source: 'FoodGroups' results in a list containing the single item 'FoodGroups',
  • Setting Allow: 'List' and Source: '"FoodGroups"'results in a list containing the single item '"FoodGroups'",
  • Setting Allow: 'List' and Source: '=Foodgroups' results in error (The forumla you typed contains an error...),
  • Setting Allow: 'List' and Source: '="FoodGroups"' results in error (The list source must be a delimited list, or a reference to single row or column.),
  • Setting Allow: 'List' and Source: '=INDIRECT("FoodGroups"' seems to provide the desired list. This seems inconsistent with my understanding of the 'INDIRECT' function.
Is there a 'trick' I am missing in 'Data Validation' to reference tables located on separate sheets?
The above result (e.g., 'Fruit') is stored on the 'Calorie Calculator' sheed in cell B16. I've attempted to construct the dependent drop down list using the result by,
  • Setting Allow: 'List' and Source: '=INDIRECT($B$16)'.
These parameters produce a dependent drop down list containing only the value from B16, not the anticipated list from the designated 'Fruit' Excel Table. I've tried numerous Source configurations without success. I'm sure the (presumably operator) error is simple, but I fail to see it. Can anyone provide direction?
Thanks
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Additional thoughts...
My 'FoodGroups' table (listing the Excel names for the various individual food group tables) was constructed 'by hand'. That is, they are simply text in General format. For the purpose of constructing dependent dropdown lists, is there a requirement that these table elements be entered (and recognized) as references to tables?
 
Upvote 0
OK, I've stumbled onto (at least) part of the problem. I was attempting to generate the dependent dropdown list from an Excel 'Table'. I had defined/named the entire table. I only need column one to generate the dependent dropdown list. It is likely there is a way to 'call' a specific column using Data Validation/Source. I would be interested to know how that might work. Currently, my 'work around' is to define/(and name) only column one in my data tables. In this manner, calling the named range from Data Validation/Source using =INDIRECT() works.
I think it would be better to define my data as Excel 'tables'. The dynamic feature would be preferable, as I'll be routinely adding data to the tables.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top