Ask Your Question

Can you create dropdown menus in Calc? [closed]

asked 2014-02-11 20:04:27 +0100

LatexSpikes gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2016-02-19 10:03:53.587745

2 Answers

Sort by » oldest newest most voted

answered 2014-02-11 20:41:12 +0100

m.a.riosv gravatar image

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.

edit flag offensive delete link more

answered 2014-02-11 21:10:14 +0100

w_whalley gravatar image

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.

edit flag offensive delete link more

Question Tools

1 follower


Asked: 2014-02-11 20:04:27 +0100

Seen: 25,306 times

Last updated: Feb 11 '14