Calc dynamic drop down list?

English
, , ,
#1

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.

    A
 1 COLOR
 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?

#7

(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’.
#8

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)

#9

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.

#2

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

DropDownList

#3

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?

#4

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

#5

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

#6

@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?

#10

Hi

Data▸Validity offers a solution with no macro or hidden column.

Explanations

  • 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.
  • Data▸Validity▸Error 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

Regards

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

1 Like
#11

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.

#12

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 …

Regards

#13

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.

#14

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

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

You guys are GREAT!!!

#15

@Lupp - thank you for the edit :slight_smile:

#16

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

#17

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.