Creating a Dropdown List in Calc That References a Range of Values Elsewhere in the Spreadsheet

I just started using feature this recently, for my LinuxWorld presentation. It's very nice. Anytime you can reference some text, rather than embedding it in the formatting, it's good.

Some background: under Data > Validity, you can control what people can enter in spreadsheets, and offer them help in the form of lists, help tips, etc.

I wrote about the Validity tools here

http://openoffice.blogs.com/openoffice/2007/01/openofficeorg_c.html

and elsewhere on my blogs. However, what I haven't talked about yet is how to make a dynamic list.

Let's say you've got a spreadsheet documenting the year's performance for the 120 people in your company, one sheet for each person. They're summarizing what they've done this year, and you want them to list the projects they've worked on. You would like to list the Official projects rather than having them type in whatever. So you want a dropdown list. Howeve, you don't want to have those projects typed in that dropdown list because then if you change it, or use this spreadsheet next year, you'll have to correct the list 120 times. (You can copy and paste, but still.)

So you make a list of the projects that references a cell range in a sheet called Projects, say, and all you need to do is update that one cell range to make all the lists update.

Here's how you do it.

1. Click in the cell where you want the list to appear. If you want the list in multiple cells, select multiple cells.

2. Choose Data > Validity.

3. Select Cell Range in the list.

4. Type the range. To type a range in the same sheet , type something like this:

$F$1:$F$20

You need the $ to make the reference absolute.

If the range is in a different sheet, add the absolute sheet reference in front, like this.

$Projects.$F$1:$F$20

5. Click OK.

Then if you need to, just change the contents of the cell range and the list updates. Previous entries in those spreadsheets containing entries no longer in the list do not change.

---

---

Comments

[David]

Simple, elegant and right before my eyes the whole time. I've just put this to good use in my current project. You've saved me quite a bit of work. Thank you.

[Elaine]

I just started using this sort of validation and want to ask a question. What is the difference between the name from "define name" and the name from "define range".

[Solveig]

Hi Elaine,

Define Name, under Insert > Names > Define, is used with calculations, so that you can use more meaningful names in formulas. If you define a range as Income and another range as Expenses, then you can type the formula =Income-Expenses and get the same result as for instance A5-A6.

Define Range is more for printing or for any reference.

Solveig

[Nathan]

Thanks for the tip!

Is it possible to use this to do one of the following things?

1. Have a multi-column drop down box. For instance, maybe having a project leader column in addition to the project name column.

2. Have a single column drop down box with adjacent cells on the entry form referring to adjacent cells on the "list" sheet. That way, if you select, say, the project name from the drop down box, the adjacent cell would automatically fill in the project leader.

I know that this kind of thing can be done with a database, but I was hoping that I would not have to set up a whole database just for a little bit of information.

Thanks,
Nathan

[Priya]

Thanks a lot! Was very helpful and very handy.

[Huss]

Hi. I was going crazy why my ranges did not work. I used the range directly like A1:A100, and the next row would pick the range A2:A101 and the next A3:A102. Thanks to your article I discovered that the absence of $ was the problem. Entering the range in $A$1:$A$100 format fixed the problem. Thanks a bunch!

[DJ]

In Microsoft Excel you can change the range of cells you want to appear in your drop down box and apply that change to all other validated cells by checking the box "Apply these changes to all cells with the same setting." Can this be done in Open Office without going through and changing each cell individually?

[Jesse]

Nice and simple tutorial. Much appreciated.

[David Sarnowski]

Can you refer to a named range for validity rather than a cell reference?

[Yovko]

Good tutorial!

And helpfull.
I just want to ask, if there is any way to select a range of sell like: $Shift2.$A$1:$A$100 and in the list to occur only non empty lines? If on the Sheet2 I have only 10 records, in the cell with dropdown menu to appear on ly this 10 choises, not 100, 90 of which are empty.

[Diego Rodríguez]

There's an example of cities and countries at http://user.services.openoffice.org/en/forum/download/file.php?id=333

[Rudy Baylor]

everytime i reopen my document, the drop down list dissapears and it says N/A. Any suggestions? Thanks so much!

[Dermot]

Hi
How do the other Data>Validity functions apart from List work? Im creating a household budget spreadsheet and have created a List of Expenses, i.e. Electricity, Gas, Phone. Im putting each month on a seperate sheet, and for each month I would like a drop down calendar-month so that I can select the date the bill arrived. Ive tried working with the Data>Validity>Date function but do not understand how to program it to show a calendar.
Can you help please?
Thanks