creating text pick list in Calc

tonymoloney · Newbie Joined: 29 May 2007 Posts: 2

I know what I want to do, but I don't know the technical jargon for it, so I can't find what I'm looking for in the Help files.
In a spreadsheet that i have set up, when i click(right click?) on a certain text cell, I wish to have a drop-down list of all available legitimate entries that I can put into that text cell.
I also need to be able to add extra entries from time to time.
Can anyone point me in the right direction, please?
_________________
Tony

acknak · Posted: Tue May 29, 2007 8:43 pm Post subject:

In the cell where you want the list to appear, do Data > Validity > Criteria
Allow = List
Make your settings; enter the list items, one item on each line.
OK.

If you have a lot of items, you can use Allow=Cell Range to have the list take the items from cells somewhere in the spreadsheet.

tonymoloney · Newbie Joined: 29 May 2007 Posts: 2

Acknak
That's exactly what I was looking for. Thank you so much.
_________________
Tony

palazzo · General User Joined: 08 Mar 2006 Posts: 10

I have a similar problem. I want to populate this list of allowed entries from another spreadsheet in the same document, without having to retype manually every value. I managed to do this by creating a form combo box (a pull-down menu), but then how can I access the value selected? I want the choice from this list to reflect in another cell via a search function.
_________________
MacBook Pro Intel Core Duo 2.0/1.5 GB RAM
MacOS X 10.4.9
OOo 3b2

acknak · Posted: Wed May 30, 2007 9:43 am Post subject:

As far as I know, the form controls (from the "Controls" toolbar) cannot connect with the spreadsheet except by writing some BASIC code to copy the control's value to a cell.

Is there a reason you can use the Data > Validity > Criteria / Allow=Cell range ? dropdown list. It sounds like that should work for you and won't need any code.

Villeroy · Posted: Wed May 30, 2007 10:33 am Post subject:

palazzo · General User Joined: 08 Mar 2006 Posts: 10

Thanks acknack, that solution was just what I needed. Villeroy, is there an easy way to get the value of a checkbox into a cell formula such as IF([the checkbox is checked];do this;do that)?
_________________
MacBook Pro Intel Core Duo 2.0/1.5 GB RAM
MacOS X 10.4.9
OOo 3b2

Villeroy · Posted: Thu May 31, 2007 7:41 am Post subject:

Try to follow the steps I described here
http://www.oooforum.org/forum/viewtopic.phtml?t=57783
It will take about 10 minutes or so. Then you should know one alternative way how to do this.
In general this kind of lookup is a typical job for a database. Spreadsheets can not handle relations at all. Append a duplicate of an entry and you'll see what I mean. You end up in rather complex set of formulae without having the reliability, speed and ease of a database.

Further tutorial on lookups in spreadsheets:
http://www.oooforum.org/forum/viewtopic.phtml?t=57670
_________________
Rest in peace, oooforum.org
Get help on http://forum.openoffice.org

palazzo · General User Joined: 08 Mar 2006 Posts: 10

Thanks!
_________________
MacBook Pro Intel Core Duo 2.0/1.5 GB RAM
MacOS X 10.4.9
OOo 3b2