OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

creating text pick list in Calc

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc
View previous topic :: View next topic  
Author Message
tonymoloney
Newbie
Newbie


Joined: 29 May 2007
Posts: 2

PostPosted: Tue May 29, 2007 7:52 pm    Post subject: creating text pick list in Calc Reply with quote

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
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Tue May 29, 2007 8:43 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
tonymoloney
Newbie
Newbie


Joined: 29 May 2007
Posts: 2

PostPosted: Tue May 29, 2007 11:43 pm    Post subject: Reply with quote

Acknak
That's exactly what I was looking for. Thank you so much.
_________________
Tony
Back to top
View user's profile Send private message
palazzo
General User
General User


Joined: 08 Mar 2006
Posts: 10

PostPosted: Wed May 30, 2007 8:59 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
acknak
Moderator
Moderator


Joined: 13 Aug 2004
Posts: 4295
Location: ~ 40°N,75°W

PostPosted: Wed May 30, 2007 9:43 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Wed May 30, 2007 10:33 am    Post subject: Reply with quote

acknak wrote:
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.

That's not true except for "special controls" like date fields. A listbox as well as a combo box on a spreadsheet has two properties "Linked Cell" and "Source Range".
acknak wrote:

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.

Yes, this is the method which allows you to do much more without the need for coding. You can display different types of messages which allow to reject the entry or ask with default button "Yes" or "No".
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
palazzo
General User
General User


Joined: 08 Mar 2006
Posts: 10

PostPosted: Thu May 31, 2007 6:27 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu May 31, 2007 7:41 am    Post subject: Reply with quote

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 https://forum.openoffice.org
Back to top
View user's profile Send private message
palazzo
General User
General User


Joined: 08 Mar 2006
Posts: 10

PostPosted: Thu May 31, 2007 7:56 am    Post subject: Reply with quote

Thanks!
_________________
MacBook Pro Intel Core Duo 2.0/1.5 GB RAM
MacOS X 10.4.9
OOo 3b2
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Calc All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group