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

Autocomplete Text From/To Multiple Cells At Once

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


Joined: 27 Oct 2005
Posts: 23

PostPosted: Tue Dec 19, 2006 3:21 pm    Post subject: Autocomplete Text From/To Multiple Cells At Once Reply with quote

Hi,

I'm using Calc to compose plant lists for landscape design. We have a template Calc file of the plant list, with dozens of rows of hidden cells with scientific and common names of plants to use for the Autocomplete function. It would really speed things up if I could type say 'Que' in A1, and have 'Quercus agrifolia' popup there AND the common name, 'Coast Live Oak', automatically popup at the same time in A2. Anyone able to help out?

Thanks,
Steve
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 19, 2006 3:49 pm    Post subject: Reply with quote

Well, it seems to me, that you are going to misuse a spreadsheet as a database.
I assume, you have some table describing unique species somewhere.

Column A .............ColumnB
LatinName ........... CommonName .... < other properties of species >
Quercus agrifolia Coast Live Oak
< more species >

In the table you where talking about:
Select A1
Menu:Data>Validity
Allow: "Cell Range"
Source: Species.A2:Axxxx [first column below header until row xxxx on table "Species")
Sort ascending
A2: =VLOOKUP($A$1;$Species.$A$2:$A$xxxx;2;0) [lookup A1 in first column of List and return the value in 2nd column]
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Wed Dec 20, 2006 3:43 pm    Post subject: Reply with quote

Thanks for your respone. I need just a bit of clarification.

In reality, my latin name is in column B, and the common name in column C. I adjusted the values in your formula, but all I get is an error message.

I was wondering if it is possible to put the master list on another sheet in the same document (bc we have different numbers of rows in each completed plant list, I thought this would avoid the defined master list row # changing). I named a sheet "MasterList", and moved all the plants to this sheet, same columns latin=B, common=C starting at row 1. I tried to substitute "MasterList" in your formula for "Species" and adjust the rows accordingly, but again this only returned an error message.

Also what is the ";2;0) at the end of your formula specifying?

Thanks for your help,
Steve
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 Dec 20, 2006 4:27 pm    Post subject: Reply with quote

See help on VLOOKUP and http://www.oooforum.org/forum/viewtopic.phtml?t=50344&highlight=vlookup
Quote:

In reality, my latin name is in column B, and the common name in column C. I adjusted the values in your formula, but all I get is an error message.

This makes a difference since vlookup(value;search_range;return_column;isSorted); searches value in the first column of search_range and returns the value of return_column of search_range.
Try this:
=INDEX($Species.$B$1:$B$65536;MATCH($A$1;$Species.$C$1:$C$65536;0))
Match gets the position in column C, wich is the row-index of the value taken from column B.
The zero in match has the same meaning like the zero in VLOOKUP.
LOOKUP(Search criterion;Search vector;result_vector) always implies a sorted search_vector, so we can not use it here unless you keep your list sorted by column C.
In a database-form it would be as simple as:
SELECT Latin FROM Species WHERE Common Like <what you type>%
Quite fast with millions of species.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Thu Dec 21, 2006 2:56 pm    Post subject: Reply with quote

Pasted the above formula into common name cell C4, and tried to type a latin name in column B4 and get returned "Invadid value".

Thanks for your patience, I'm really new at this. I looked up VLOOKUP, INDEX, and your other post for some extra help, changed a few things, and still couldn't get it to work. Being able to break apart your formula into pieces will help me compose them in the future.


I understand your formula to mean this:

=INDEX

...value for cell will be found in an index


($Species.$B$1:$B$65536;

...index is called "Species", find matching latin name in column B between row 1 and 65536


MATCH($A$1;$Species.$C$1:$C$65536;0))

...find value in column C of same row as matching name in column B, contained in index "Species", I'm confused as to why here your formula has column A instead of column B. Also, per your other post, shouldn't your "0" be a "2" representing column C?


I defined a database range called "Species" on a seperate sheet called "MasterList" in the same document. Below are the coordinates of the database range.

Database range: $MasterList.$B$1:$C$1000


Again thanks for your help in this.
Steve
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 Dec 21, 2006 3:44 pm    Post subject: Reply with quote

sdritchey wrote:
Pasted the above formula into common name cell C4, and tried to type a latin name in column B4 and get returned "Invadid value".

Thanks for your patience, I'm really new at this. I looked up VLOOKUP, INDEX, and your other post for some extra help, changed a few things, and still couldn't get it to work. Being able to break apart your formula into pieces will help me compose them in the future.


I understand your formula to mean this:

=INDEX

...value for cell will be found in an index

No, Index($A$10:$A$19); 5) returns value in 5th row of A10:A19, which is the value of A14.
Index(Array ; row_number)
With an optional column-index Index($A$10:$B$19; 5; 2) returns value in 5th row, 2nd column of A10:A19, which is the value of B14.
Obviously INDEX($A$10:$B$19; "Malus domestica";0) makes no sence. We need a number rather than a string-value.
MATCH(PI(); $A$1:$A$5; 1)
2.8
2.9
3.0
3.1
3.2
Finds the *position* rather than value of smallest match of pi (~3.14) in an ascending sorted list A1:A5. The 1 indicates the ascending sort-order. The function returns 4, which is smallest match at position 4.
MATCH(PI(); $A$1:$A$5; 0) finds the position of the exact match and will return error #NA in this case. Error #NA indicates that there is nothing wrong with your formula, but the value is simply Not Availlable. With 0 as last argument the list does not need to be sorted.
Split up my previous formula into 2 simple formulas:
C4: =MATCH($A4; $Species.$C$1:$C$65536; 0)) where A4 is the latin name in the same row, to be matched exactly (0) in entire column($C$1:$C$65536) of a sheet named Species.
If C4 returns a number, then
D4: =INDEX($Species.$B$1:$B$65536; $C4) gets the value in entire columnB of a sheet "Species" at the row-position, specified by C4.
Now you see if the MATCH fails or the INDEX.
I'm still 95% shure that a spreadsheet is a sub-optimal tool for this task. Things will get really complex, slow and prone to errors when you start to combine plants with customers and/or suppliers of seeds, quantities, prices and rabates. Every serious business software is based on some kind of relational database. In any case you should create backups on a daily base.
_________________
Rest in peace, oooforum.org
Get help on https://forum.openoffice.org
Back to top
View user's profile Send private message
sdritchey
General User
General User


Joined: 27 Oct 2005
Posts: 23

PostPosted: Tue Dec 26, 2006 3:35 pm    Post subject: Reply with quote

Thanks for all your help.

I agree with you that this is a pretty complicated way to achieve a very simple goal. I'd hate to move our whole template file over to a database and I didn't want to add the extra column to get the formulas to work, though I understand much more now. So I just added the common name to the cell with the scientific name so theres only one column. I appreciate your time. Have a great new year.

Thanks,
Steve
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