Open Office

Overview

When utilizing a spreadsheet you may find your self repeatedly entering the same set of  information row after row. Drop Downs is an easy way to make sure you enter the exact values you want without introducing typos, or variations.  This tutorial outlines methods for creating drop downs within Open Office Calc.

Method 1: A Static List

The first method to create a drop down is to define a Validator for a cell. What this will do is define what values are allowed in a cell. This cell can then be copied to other cells that should have the same value.

1. Select a cell in the spreadsheet
2. Select “Data” from the application menu
3. Select “Validity” from the sub menu
4. Select the “List” option from the Allow field of the Validity dialog box
5. Enter your values in the Entries text box one value per line
6. Click OK

Now if you select the cell you initily selected you will noticed an arrow to the right of it. Clicking on the arrow will give you a drop down to select from. If you want to restrict your input to strictly the values in your list you will have to add an alert to your validation.

1. Select the cell that has validation
2. Select “Data” from the application menu
3. Select “Validity” from the sub menu
4. Select “Error Alert” from the dialog menu tabs
5. Check “Show error message when invalid values are entered
6. Action input should be Stop
7. Enter a Title for the error dialog
8. Enter a Message for the error dialog
9. Click “OK”

Now if you input a value into the cell that does not match a value in your list you will see a error dialogue with your title, and message.  This method is a simple way to create drop downs from unchanging lists. However if you have a list that will change over time then I would suggest method2.

Method 2: A Dynamic list

If you find yourself using the same value across multiple sheets in a file and that you are adding new values as you continue to work then this method is for you.  Method 2 is done by defining a name to a set of cells in your spreadsheet. So if you define a column as the set then as you add a new row then all drop downs that use that named set will be updated. Outlined below are the steps to defining the named set and creating the drop down.

1. Create a list of values in a new sheet in one column 1 value per row
2. Select the Column – (click on the letter above the column)
3. Select “Insert” from the application menu
4. Select “Names – define” from the sub menu
5. Enter a name into the dialog box
6. Click “Add”
7. Close the Dialog box.  You have successfully created a named set
8. Select the cell you want to create a drop down in
9. Select “Data” from the application menu
10. Select “Validity” from the sub menu
11. Select “Cell Range” from the Allow Input item in the dialog
12. Enter the name from step 5 in the “Source” input
13. Click “OK”

You will now find that the drop down is pulling values from your values you entered in your spreadsheet.  Now if you add/edit/delete values in your list all drop downs that use that named set will be updated.  Please note that if you modify a value in your named set then any drop down cells that have that value selected will not be updated you will have to update them manually.

Conclusion

Drop Downs are an excellent way to consistently enter information.  If you find yourself working with financial data, maybe keeping track of your monthly finances then creating a drop down of your monthly bills can save you time and energy.

Tired with purchasing new licenses for Microsoft Office, I mean why pay for a word processor, spreadsheet, or presentation application. Switch to the Open Office Productivity Suite and you won’t be disappointed. The best part is it is free, and if that doesn’t want to make you jump on board then how about that it…