Ask Your Question

How do you make a referd dropdown menu in LibreOffice calc [closed]

asked 2014-10-08 17:36:37 +0200

wigwam gravatar image

updated 2020-07-27 11:23:50 +0200

Alex Kemp gravatar image

How do you make a referd dropdown menu in LibreOffice. i have the names in columm C2:C112 and the values to use in E2:E112 now i need a dropdown menu in cell A1 that hes the names of C2:C112 and uses the values of columm E2:E112? is this posible ..?

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-03-04 20:17:07.638677

1 Answer

Sort by » oldest newest most voted

answered 2014-10-08 19:14:01 +0200

Regina gravatar image

No, you need two cells, one cell for the drop-down list and a second cell for the value. If you wish, you can hide the value by merging these two cell. You can setup a drop-down list via Data > Validity. Use Criteria > Allow > Cell range with your range C2:C112. Do this for example for cell A1. In B1 write the formula to get the value. If your data in C2:C112 are not sorted use =VLOOKUP(A1;C2:E112;3;0). If your data are sorted ascending, then you can use =LOOKUP(A1;C2:C112;E2:E112). After you have set the validity rule and entered the formula in B1, you can merge A1 and B1 to hide the value. For getting the value more formulas are possible, e.g. function INDEX after function MATCH.

It is possible to use a form control 'list box' bound to A1 instead of the validity, but you would still need a second cell to get the value. It might be possible to get the value into the cell A1, if you have your data inside a registered database and use the spreadsheet as form, but I have not tested it.

edit flag offensive delete link more


Awesome !! thanks allot for this wonderful answer, it works like a charm, ony the murging of the 2 cells does not work very well, as it will not find the values anymore after that, but this is no prob as i can hide the cell or use it :)

wigwam gravatar imagewigwam ( 2014-10-08 20:07:29 +0200 )edit

thanks, that one is working

escalator gravatar imageescalator ( 2016-03-18 09:48:02 +0200 )edit

Question Tools



Asked: 2014-10-08 17:36:37 +0200

Seen: 9,197 times

Last updated: Oct 08 '14