Add drop-down lists to LibreOffice spreadsheets

原文转载至：dhttp://www.techrepublic.com/blog/diy-it-guy/diy-add-drop-down-lists-to-libreoffice-spreadsheets/

Add drop-down lists to LibreOffice spreadsheets

By Jack Wallenin DIY IT Guy,February 21, 2012, 4:49 AM PST

• Email Alert
• RSS

• 5 Comments
• Save
• Facebook 0
• Twitter 0
• Linkedin 0
• 
  
• More

The addition of drop-down lists can make your LibreOffice Calc spreadsheets more flexible and powerful. Get instructions on adding them in Calc.

If you want to be a LibreOffice Calc power user, you should follow my step-by-step tutorial on two possible ways to add drop-down lists to the program for faster spreadsheet creation and easier data entry.

Approach #1

This approach is rather limiting. One of the things you can do with Calc's Data Validity tool is create drop-down lists by following these steps.

1. Open the spreadsheet you want to add the drop down to and select the cell where you want the drop-down to be placed.

2. Open the Data Validity tool by going to Data | Validity. Once this new window opens, select List from the Allow drop-down ( Figure A). Figure A

There are multiple ways of getting the data needed for the drop-down.

3. In the text area, enter the choices that will be displayed in the drop-down list ( Figure B). You should enter these choices one entry per line. Figure B

I'm creating a drop-down for hardware that can be used within my spreadsheet.

4. Click OK. Make sure all of the check boxes are selected before you continue.

Your drop-down list should be available in the cell you selected before you began the creation process ( Figure C). Figure C

These entries should look familiar -- we just created them.

You can copy and paste this drop-down list throughout your spreadsheet as needed. If you want to edit the contents of that drop-box, you have to go back into the Data | Validity tool and edit the list manually.

Approach #2

This alternative is as simple as the first approach, though it does require an extra step. Also, this option is the more flexible method.

1. On a separate sheet, create the list of items you want to use for the drop-down list ( Figure D). Figure D

We're creating the same drop-down we did with the first method.

2. Define the range you just created. Now we need to create a name for the range of cells. First, highlight the cells with the new data you just created and then go to Data | Define Range. When the new window pops up, enter a descriptive name (such as Hardware) in the text box ( Figure E). Figure E

When you enter the name, the cell range should appear. (Click the image to enlarge it.)

3. Click OK.

4. Go back to the original sheet and go to Data | Validity.

5. In the Allow drop-down, select Cell Range and then type the name of the Cell Range you defined above (in this case Hardware).

6. Click OK.

The drop-down should now be available to the cell selected.

The nice thing about this second method is that it's easy to change the items in the drop-down list. To do so, go to the other sheet (where you entered the items in cells) and modify the items you want to change. One caveat is that you cannot add to this list; the only way you can manage this is to include extra cells when you make your selection. You should remember how many extra cells you added, because you cannot go beyond that number when adding data for the drop-down.