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

Combo Box Source Cell Query

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


Joined: 21 Jun 2012
Posts: 2

PostPosted: Sun Jun 24, 2012 4:26 am    Post subject: Combo Box Source Cell Query Reply with quote

First of all, im new in this software and im seeking advise to the following.

The idea was to list out the entire column B in another worksheet which named "DB"
Im currently using 'DB.B2:B65536' on Combo Box properties Source Cell, this works but it takes awhile to finish populating the list.

Im looking for a way which
1. Does not require Macro
2. List out the entire column B on worksheet 'DB'
3. Stop populating the list when encounter empty cell value

Your thank is greatly appreciated!


Working on OOo ver2.2 on window XP
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Sun Jun 24, 2012 5:32 am    Post subject: Reply with quote

Combo boxes list up to 32000 entries.
Combo boxes are simple list boxes with auto-type feature. You can type any text you like into a combo box. Normally, it is filled with something like
SELECT DISTINCT "Column Name" FROM "Table" ORDER BY "Column Name"
selected from a databse (no, a spreadsheet is NOT a database nor can it replace a database).
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
marshallc
Newbie
Newbie


Joined: 21 Jun 2012
Posts: 2

PostPosted: Sun Jun 24, 2012 2:19 pm    Post subject: Reply with quote

So by using the SELET DISTINCT method empty cell will not be placed in the combobox? Is there any other alternatives other than using a database instead of a spreadsheet? because im just coming up with a simple program to do basic task.
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


Joined: 23 Apr 2009
Posts: 2032
Location: Sydney, Australia

PostPosted: Sun Jun 24, 2012 4:19 pm    Post subject: Reply with quote

Perhaps you could use Data Validity instead of Combo Box.
To populate the Data Validity selection list with the values in DB.B2:B65536 you could select "Cell range" in the "Allow" box and use a formula like the following in the "Source" box...
Code:
$DB.$B$2:INDEX($DB.$B$2:$B$65536;COUNTA($DB.$B$2:$B$65536))


Note that above formula will not work if the blanks below the last non-blank cell in DB.B2:B65536 are empty strings ("") returned by formulae. In that case the COUNTA formula also counts the 'blank' cells because they each contain a formula.
If this is the case then instead you could try...
Code:
$DB.$B$2:INDEX($DB.$B$2:$B$65536;SUMPRODUCT($DB.$B$2:$B$65536<>""))


Ken Johnson
_________________
If your problem has been solved please add "[Solved]" to the beginning of your first post title (edit button).
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