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.
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.
Posted by: KJ Loh | April 10, 2008 at 08:26 AM
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.
Posted by: Solveig | April 15, 2008 at 06:10 AM
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?
Posted by: Florian | May 03, 2008 at 12:32 PM
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
Posted by: Solveig | May 05, 2008 at 09:10 AM
Hi,
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
Bastiaan
Posted by: Bastiaan | August 28, 2008 at 06:04 AM
http://www.gamegoldme.com/
http://www.wowgold-powerleveling.com/
http://www.wowgold-wow.com/
http://www.wowpowerleveling.me
http://www.watchrolexshop.com
http://www.wowgold-wow.com/wow-power-leveling
http://rs-runescapegold.com/
http://www.watchrolexshop.com/wow-power-leveling/
http://www.cheap-lotrogold.com/
http://www.globalsale.me/Aion-gold-083.aspx
http://www.cheap-gamegold.org
http://www.gamegoldvip.org
http://www.globalsale.me/
Posted by: wow power leveling | July 05, 2009 at 10:44 PM
Hi,
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.
Posted by: Venkatesh | September 04, 2009 at 02:13 PM
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.
Posted by: Club Penguin Cheats | January 08, 2010 at 08:54 PM
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
Posted by: kapone | January 14, 2010 at 09:51 PM
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.
Posted by: bridal wedding gowns | January 19, 2010 at 01:25 AM
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.
Posted by: Mat | January 27, 2010 at 06:47 AM