| View previous topic :: View next topic |
| Author |
Message |
marshallc
Newbie
Joined: 21 Jun 2012
Posts: 2
|
 Posted: Sun Jun 24, 2012 4:26 am Post subject: Combo Box Source Cell Query |
 |
|
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 |
|
 |
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sun Jun 24, 2012 5:32 am Post subject: |
|
|
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 http://forum.openoffice.org |
|
| Back to top |
|
|
marshallc
Newbie
Joined: 21 Jun 2012
Posts: 2
|
| Posted: Sun Jun 24, 2012 2:19 pm Post subject: |
|
|
| 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 |
|
|
ken johnson
Super User
Joined: 23 Apr 2009
Posts: 1844
Location: Sydney, Australia
|
| Posted: Sun Jun 24, 2012 4:19 pm Post subject: |
|
|
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 |
|
|
|
