Creating a dropdown list in OpenOffice Calc that references a list of values (repost)

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

[KJ Loh]

Thanks for the tips. after trying it out. I realized after changing the source. the selected value at the individual is not changed even though the value of the list is changed. Not sure if that is right but just feel it strange. Could be their design rationale, but It does mean we could only add additional value than changing the existing one.

[Solveig]

Hi KJ,

yeah, you could make an argument either way for what would be appropriate. Leaving the original value in the field is probably more safe but less convenient.

[Florian]

I found this article while searching for a way to create a dynamic dropdown list, but not in the sense described here.

Use case would be like this: I've got a long list (~500 entries) of options to choose from. I'd like to add a user defined list at the beginning of the long list to enable users to quickly select often used choices.

After some tinkering, I got it to work as follows (you might want to take a look http://wikisend.com/download/698376/DynamicDropdownOpenOffice.png while reading on.)

I have three lists, let's call them longList (B1:B500), customList (A1:A10) and actualList (C1:C510).

I first determine the number of entries in customList by using =COUNTIF(customList;"<>")
Let's suppose I put that in E1

Next to each entry in actualList, I put an index value, starting with 1 (you could also use ROW(C1)instead). So D1:D510 is filled with the numbers 1 to 510

I then calculate indexValue - lengthCustomList. If the result is 0, the entry comes from longList.

So the formula for an entry in actualList reads like
=IF(result<=0;entry from customList;entry from longList)

Now, entry from customList is easy, as these entries form the first entries of actualList. So if customList goes from B1:B10, first entry of actualList reads
=IF(result<=0;B1;entry from longList)
and then you just drag it down so next entry refers to B2 and so on.

An entry from longList is a bit more difficult since we can't refer directly to longList. But from the result of indexValue - lengthCustomList, we can build the cell reference to the right entry from longList and then INDIRECT this reference.

The formula to build the cell reference is like
=CONCATENATE("A";indexValue-lengthCustomList)
or without the range names for the first entry in actualList
=CONCATENATE("A";D1-$E$1)
which would result in A1 if you have no entries in customList (D1 is 1 and E1 is 0). Drag that down along the length of actualList to get cell references for all entries.

Suppose you put the formulas for the cell references in F1:F510, then the formula for the first entry in actualList reads
=IF(D1<=0;A1;INDIRECT(F1))

CU
FLo

PS: The picture will be only up for 90 days, so maybe Mrs Haugland can put it somewhere more permanent?

[Solveig]

Hi Florian,

This sounds like an interesting project; a little on the big side for a blog reply. My thought initially is that the COUNTIF or other calculations could copy the results to another part of the spreadsheet and the dropdown list would reference that.

Solveig