Dropdown lists in OpenOffice.org Calc

Dropdown lists are a mainstay of many spreadsheets and forms. They help you control what people can enter, as well as giving them ideas for what types of things they might say.

You can make dropdown lists in a couple ways, at least, in OpenOffice.org. The simplest way is to use the tools accessed under Data > Validity. The lists are easy to make, plus you can easily use the values in formulas. In the following illustration, the value in B2, selected from a dropdown list, is referenceable in another cell.

Another way you can use the values in the dropdown list (among many) is to concatenate them. Click the following illustration to see it larger. The formula

=CONCATENATE(B2;" is an excellent source of information.")

sucks in the text in the dropdown list in B2.

So how do you make this list? I'll tell you.

1. Click in the cell where you want the list.

2. Choose Data > Validity.

3. In the list at the top, choose List as the type of information.

4. Select the various options you want.

- Allow blank cells: just what it sounds like. This means you don't have to select a value from the list, but if you type even one blank space, you can get the error message. More on that later.
- Show selection list: you definitely want this. What's the point of a list if you don't see it? Plus, then it's hard to figure out which cell has the list, to go back and modify it. (To modify an existing list, just select it and choose Data > Validity again.)
- Sort entries ascending: alphabetical order sorting. If you don't mark this, the items appear in the order you entered them.

5. Type the items you want in the list. Just press Enter to get to each new line.

6. Click OK. You'll see the list. Select something from the list. However, note that you can still type something that's not in the list, in addition to selecting something in the list.

7. If you want to require that people only enter something from the list, you need to do one more step. (You didn't have to click OK at that point; I just had you do that to show you the effect of not setting an error alert.) Select the cell where the dropdown list is and choose Data > Validity again.

8. Select the Error Alert tab.

9. You need to select the Show Error Message checkbox, select an error type (Stop is what you want to enforce selection of an item from the list), and optionally type the error title and error content.

10. Now click OK again.

11. Now when you try to type something different in the list, you get the error message you created.

---

---

Comments

[Diona Kidd]

Thanks! This was just what I needed. The steps are very easy to follow and understand. Thanks for taking the time to write this tutorial on Open Office and drop down lists.

[an1ta]

Hi,

Do you now how to make the drop down list depend on another one?

As if I have a drop down list: fruits,veggies, dairy

And for each of those options I have, for example for fruits: oranges, bananas, tangerines..

Thanks

[Solveig]

Hi an1ta,

I'm afraid this feature doesn't have couhat capability. You use a dropdownlist for the first one, then perhaps use the IF function if you have some logic that you can display directly.

[Larry]

Or..

On another sheet type up your list.
Data -> Define Range, and name the range something easy to remember.
Then on your first sheet, highlight the column you want to use the list lookup.
Data -> Validity, set Allow to Cell Range, for Value set it to the name of the Range you created earlier.

[jud]

i need to ADD the single elements contained in a single cell, placing the outcome in
another cell.

i have named from a to z, 26 cells. Each cell contains a number from 1 through 26.

Then in a column (column a named "Words") i have input words (eg "word", without the quote marks) composed of various letters established in the 26 cells named above.

The objective is to aggregate the numbers associated with the specified letters composing a given word (eg "word") that is located in a cell.

My question is how does one create a formula that captures the number of letters in the cell containing the word, then interpolating a plus sign ("+") between each letter in the word, then dropping the sum in the formulating cell.

regards

[Mike L]

Thanks for this, it was exactly the information I needed.

[kArLaLa]

Big help ^^ Thankies!

[DaFoot]

Just a quick note to say thanks :)

[satheeshr]

Sir,
Can I create a drop down list with list item in any language (preferable malayalam) in open office calc?

[solveigh]

Hi Satheeshr,

Absolutely, just type it in whatever language you want, or else reference a range of cells typed as you like.

[Real Exam]

Can we add dropdown in several fields or in one or two only?if i want to add more?

[Kathleen]

I've been having an issue with the drop down lists-- anything with more than 15 or 16 entries, after it's been saved, the entries seem to disappear. Is there a limit on the amount of items in a drop down list? I need to be able to do about 40 or so...

[chris]

thanks, i didnt even need to read the whole thing... you pointed me exactly where i need to go. thanks!!

[slowne]

Thank you very much for this tutorial. I was stumped.

[waleed]

Can i select drop down list values from any other column in the same sheet. For eg: I need to keep create a template of all the cities in a country and a drop down will be that way. I dont want to add one by one, All i need is there is a column with all the names of the cities, I just need to get those values for the drop down list.

Thanks

[Solveig]

Hi Waleed,

Yes, instead of list, use the range of cells option.

[Ali]

Is There A way for the drop down list to get data from a database table?

[Solveig]

Hi Ali,

Not using this approach. (Unless you used a data pilot to get the data in from the database, then referenced that section.) More traditionally you would use a form in Writer (create a new Writer doc, use the Form toolbars to add a dropdown list, then link to a database table).

I don't think I have a specific blog entry about that but this will get you started on just creating the dropdown list. Then instead of typing data from the dropdown list you'd reference a field with an appropriate array (?) of data...which I'm a little fuzzy on currently.
http://openoffice.blogs.com/openoffice/2008/07/how-to-create-data-entry-forms-from-scratch-and-make-them-into-pdfs-in-openofficeorg.html

[Elias]

Great help, thanks for all your effort.

[Sterling Clifton]

I'm unclear if this question has been asked/asnwered, so I'll post it.
Problem: I need to created a spread sheet with drop down menus that will reference other parts of the file. For example, Sheet 1, "Unit Data," needs pull down menus for rank, and each piece of equipment the unit member has.
Sheet 2 list the various ranks and info about them.
Sheet 3 list uniform/clothing data.
Sheet 3 list side arm data.
Sheet 4 list primary weapon data.
Sheet 5 list secondary weapon data.
Sheet 6 list other equipment data.

Question: Can you tell me how to make it so that the drop down menus in sheet 1's options will be connected to the items on the other sheets?

[Ravin]

What is the keyboard shortcut for the dropdown created in validity? Like we have Alt+down arrow key in excel.

[Solveig]

Ravin, the standard down arrow works for me.