We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question

Calc dynamic drop down list? [closed]

asked 2015-12-29 01:07:13 +0200

LibreGuy gravatar image

Hi guys,

Is it possible to create a dynamic drop down list in Calc?

I will try to explain what I mean. I want to create a spreadsheet and every cell in column A (except A1) should contain a drop down list which contains all of the values that have been previously entered in column A.

 2 red
 3 red
 4 blue

As you see there are 2 different values in column A: red and blue. Imagine that at first the spreadsheet was empty. In cell A2 I then manually typed "red". The value "red" automatically became part of the drop down list. In cell A3 instead of typing I could now use the drop down list to select the value "red". In cell A4 I manually typed "blue". In cell A5 I could use the drop down list (which now contained "blue" and "red") to select the appropriate value.

Is it possible to create something like that? Preferably without any "shadow" tables?

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 2021-05-11 14:32:42.801504

4 Answers

Sort by » oldest newest most voted

answered 2015-12-30 08:51:57 +0200

pierre-yves samyn gravatar image

updated 2015-12-30 13:18:34 +0200

Lupp gravatar image


DataValidity offers a solution with no macro or hidden column.


  • The list is defined for the entire column (which may not be good for performance)
  • Source: A:A. This therefore includes the title of the column. A space is inserted before the word for it is listed first. Also for performance reasons it could be "reasonable" to reduce source and range of application.
  • DataValidityError Alert tab▸Action set to Information. This allows the entry of a value not in the list (displays an information dialog). If you do not want the dialog, just uncheck Show error message...

See ValidityNew.ods


(Edited by @Lupp :) Attached the announced Example.

edit flag offensive delete link more



Yes, but source "A:A" will includ to list a header of column ("COLOR"). May be set source $A$2:$A2 for cell A2? When we pull this cell down the address will change to $A$2:$A3, $A$2:$A4 etc.

JohnSUN gravatar imageJohnSUN ( 2015-12-30 10:08:22 +0200 )edit

Hi @JohnSUN

Yes the header will be included (just like with selection list) and that is what I wanted to say with This therefore includes the title of the column, hence my advice to reduce range and source.

On the other hand, use the entire column is simple to implement. To our friend to review cost-benefit ..


pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-12-30 10:22:49 +0200 )edit

This is, in principle, the most advanced solution. It can be refined, however, to only offer for selection a part of the column if a certain complication and reduced efficiency are accepted. A demonstration of the way to do so will be attached to the answer by @pierre-yves-samyn, supposing he does not reject my editing his post this way.
Since the validity is handled as a format, and its formula (if any) will only be evaluated if the tool is used for a cell, performance should not be too bad.

Lupp gravatar imageLupp ( 2015-12-30 13:11:01 +0200 )edit

Wow ... thanks pierre-yves samyn, JohnSUN and Lupp.

This is EXACTLY the solution I was looking and hoping for.

You guys are GREAT!!!

LibreGuy gravatar imageLibreGuy ( 2015-12-30 14:38:53 +0200 )edit

@Lupp - thank you for the edit :)

pierre-yves samyn gravatar imagepierre-yves samyn ( 2015-12-30 14:47:38 +0200 )edit

answered 2015-12-29 19:22:10 +0200

Lupp gravatar image

updated 2015-12-29 19:24:11 +0200

(Ammending to the answer by @JohnSUN and replacing my comment there.)

The function (tool) JohnSUN pointed to is by default assigned to the key Alt+DownArrow.
If you open the dialogue > 'Tools' > 'Customise' you will find the 'Toolbars' tab where you can edit toolbars. By default 'Standard' should be selected.

  1. Select the toolbar you want to add a function to, say 'Tools'.
  2. Click 'Add...' to open a second dialogue titled 'Add Commands'.
  3. Select (left) the 'Category' 'Edit'.
  4. Select (right) the 'Command' 'Selection List'.
  5. Confirm clicking 'Add'.
  6. 'Close' / 'OK'
  7. Make sure that the toolbar you chose under 1. is visible.
  8. Start to use the command 'Selection List'.
edit flag offensive delete link more


Nice! Dear friend, you forgot to mention that this setting can be saved for the entire office or only for the current document (Save In)

JohnSUN gravatar imageJohnSUN ( 2015-12-29 19:36:53 +0200 )edit

Thanks, I get it and it worked ... however ... I'm still looking for a solution where the arrow appears in the cell itself, so when I click the cell, the arrow should appear. That's what I would really like.

LibreGuy gravatar imageLibreGuy ( 2015-12-29 21:15:29 +0200 )edit

answered 2015-12-29 08:33:32 +0200

JohnSUN gravatar image

You don't need any additional tricks - everything is already done. Just press in the cell Alt+DownArrow


edit flag offensive delete link more


Thank you. That works ... but I'd rather not have to use my keyboard. Instead I would like to click an arrow with my mouse which makes the drop down list appear, and then click the value with my mouse. Is that also possible?

LibreGuy gravatar imageLibreGuy ( 2015-12-29 14:33:19 +0200 )edit

If you are not afraid to use macros in your project - possible everything. This solution wait until next year?

JohnSUN gravatar imageJohnSUN ( 2015-12-29 15:20:55 +0200 )edit

@LibreGuy may add the tool 'Selection List' (from the 'Edit' catergory) to a shown toolbar seeming appropriate for the purpose to him.

Lupp gravatar imageLupp ( 2015-12-29 15:34:16 +0200 )edit

@JohnSUN: I'm not afraid to use a macro as long as I don't need a shadow table (I mean for example an extra column which stores the values of column A). Is there a tutorial somewhere maybe?

@Lupp: could you please explain a bit more. Sounds interesting, but I don't know exactly what you mean. Would this create a dynamic drop down list for each cell in column A, and if so how do I apply this 'selection list' to the cells in column A?

LibreGuy gravatar imageLibreGuy ( 2015-12-29 15:39:53 +0200 )edit

answered 2018-05-29 06:18:34 +0200

Read here with examples:

Data validation in a single dropdown from a list of values


Data validation using dynamic dropdown which depends on another dropdown


edit flag offensive delete link more

Question Tools

1 follower


Asked: 2015-12-29 01:07:13 +0200

Seen: 16,702 times

Last updated: May 29 '18