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
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.
Reference: http://openoffice.blogs.com/openoffice/2009/04/creating-a-drop.html
0 comments:
Post a Comment