I’d like to create a dropdown menu in calc to reference a selection of cells. For example, a drop down menu might contain a list of foods at A1 and selecting one would result in A2-A5 to list calories, # on hand, etc.
Any help would be appreciated. Thanks.
There are two ways I can think of. The first is to create your table including all the data then invoke the Autofilter (Data/Filter/Autofilter). This will let you select the rows you want to see. Deselect All and select the rows you want. This option allows more than one food to be selected.
The second method elaborates on @marisov’s answer. Here is a tutorial on adding drop-down lists. Put your data table on one sheet (say Sheet1) and construct your drop-down list on another sheet. Add the drop-down cell to column A (say A2) on the second sheet then use lookup formulas to use the content of A2 to read the corresponding row in the data table and fill in cells B2, C2, etc.
For example if dropdown A2 is the food column and columns B is calories, then the formula in B2 would be =LOOKUP($A$2,Sheet1.$A$2:$A$7,Sheet1.B2:B7). Adjust the $A$2:$A$7 to include all of your food column in the data table.
The food column in the data sheet should be sorted for the lookup to work correctly. You can hide Sheet1 if you wish, and you can use named ranges in the formulas instead of hard-coding the ranges.
I think what you want is in Menu/Data/Validity, you can select a list to enter there the data list or select cell range with the data.