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

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.
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,228
Messages
6,170,871
Members
452,363
Latest member
merico17

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