« Using HLOOKUP and RANDBETWEEN to create random sets of things from two or more sets in OpenOffice.org Calc spreadsheets | Main | Oracle Buys Sun »

April 16, 2009


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.

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.

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
or without the range names for the first entry in actualList
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


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

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.



I have the following problem: I want at the end one letter-code to be filled and i refere to a 2 column selection with a letter + its explainaition.
This does not work.

Also the dropdown will be as brought as the column itself... so way to small




I have a question.
In cell A1, I have an amount. Thee cell B1 is expected to be in the date format.
I want to restrict entry in cell B1 based on the values in cell A1 such that unless the value in A1 is greater than 0, I cannot enter any date in B1. I should get an error message "IF A1 <= 0 AND I TRY TO MAKE AN ENTRY IN B1". Please help.

Many of us don't know about this event. Your post is helpful in this case. it will help people know about such nobel events and will create awareness.

Hi, I have a similar question to the one in first comment. Lets say you defined the list of project options as in the example above, and you realized you made a mistake in one of the options, such as: instead of " Tom's Tavern ", you want to correct it with " April's Tavern ". Instead of going back to the main sheet and trying to replace " tom's tavern " option one by one with "April's tavern" option, you just want it automatically updated in the main sheet cells where it is the chosen option. how do we do that?
Second question, if I save the document as .xls extension and open it in MS excel XP or excel 2007, does the file function(drop down menus etc.) just like in calculator?
Thank you

Many of us don't know about this event. Your post is helpful in this case. it will help people know about such nobel events and will create awareness.

I have a document which involved a list format similar I think to what Florian mentioned although I'm pretty sure it was a lot simpler to set up. I now intend to use the set up in a new spreadsheet but I can't remember for the life of me how I set it up.

I consisted of a drop down list which contained words, in this case it was originally an rpg so the first list was male, female or neuter and a second list presented the available titles based upon the selection from the first. I can't see a way I can use numbers for referencing the lists in the new document and I was wondering if anybody might know how to set it up or how to check my old speadsheet to find out the set up. Any attempt at finding what I had input so far has just turned up the current list.

The comments to this entry are closed.

GetOpenOffice Consulting

Get Book Resources

Search This Blog