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

How to search for cell data from a list and output exact m.

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


Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan

PostPosted: Thu May 17, 2012 6:42 pm    Post subject: How to search for cell data from a list and output exact m. Reply with quote

Hi, I want to create a function which will search for the value of a cell in each cell of a list.
If the value is found, I want the value to the column to the right to be returned in an output sheet with exactly the same format.
This will be done for each cell in the "Template" sheet.

I am not familiar with spreadsheet functions, so any help would be much appreciated.
I can assume it will use a SEARCH function and IF it finds an exact match it will copy that to the Output sheet., but I am not familiar with which functions to use.

Please can anybody help. Any help at all would be much appreciated.
Thanks

I have attached images below of each sheet.

EXAMPLE (using images attached):
- Search for sleipnir_mobile_for_android from Template sheet(A34) in Data sheet.
- Should find sleipnir_mobile_for_android in A8.
- Then should return B8 in A34 in Output Sheet (if no match should use sleipnir_mobile_for_android)


Data sheet:


Output sheet:


Template sheet:

_________________
Translator and English teacher in Japan.
Please check out my blog.
Back to top
View user's profile Send private message Visit poster's website AIM Address
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu May 17, 2012 7:26 pm    Post subject: Reply with quote

If I understand you correctly I think this is what you want
In cell A34 of Output sheet dragged down and across as necessary
Code:
=INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0))

_________________
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
damiengolding
General User
General User


Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan

PostPosted: Thu May 17, 2012 7:31 pm    Post subject: Reply with quote

Thanks so much!
That is probably the best reply I have ever received from a forum. Sorry for being such a newbie and not trying myself much to solve it myself. I have some experience with coding, but never understood spreadsheet functions. I wish I could make it up to you.

I have just one question. I have never seen $ used in functions, what do they represent?

Thanks
_________________
Translator and English teacher in Japan.
Please check out my blog.
Back to top
View user's profile Send private message Visit poster's website AIM Address
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu May 17, 2012 7:43 pm    Post subject: Reply with quote

oops forgot about your last point
Code:
=IF(ISNA(INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0)));Template.A34;INDEX($Data.$B$1:$B$45;MATCH($Template.A34;$Data.$A$1:$A$45;0)))

_________________
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
ozzie
OOo Advocate
OOo Advocate


Joined: 29 Jul 2010
Posts: 400
Location: victoria

PostPosted: Thu May 17, 2012 7:49 pm    Post subject: Reply with quote

oops again hadn't seen your reply
The $ locks the reference its attached (either the row,column or sheet name) to so that it wont change when dragged to different cells.
You will note in the formula that "Template.A34" is not locked and that this refernce changes as the formula is moved
_________________
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
pitonyak
Administrator
Administrator


Joined: 09 Mar 2004
Posts: 3655
Location: Columbus, Ohio, USA

PostPosted: Thu May 17, 2012 8:05 pm    Post subject: Reply with quote

damiengolding wrote:
Thanks so much!
That is probably the best reply I have ever received from a forum. Sorry for being such a newbie and not trying myself much to solve it myself. I have some experience with coding, but never understood spreadsheet functions. I wish I could make it up to you.

I have just one question. I have never seen $ used in functions, what do they represent?

Thanks


Ozzie is certainly a brilliant one..... Laughing

Welcome to the forum. Learning takes time and effort. The forums help much. You may also find useful information here:

http://www.odfauthors.org/
_________________
--
Andrew Pitonyak
http://www.pitonyak.org/oo.php
Back to top
View user's profile Send private message Send e-mail Visit poster's website AIM Address
damiengolding
General User
General User


Joined: 17 May 2012
Posts: 5
Location: Osaka, Japan

PostPosted: Tue May 29, 2012 11:47 pm    Post subject: Reply with quote

ozzie wrote:
oops again hadn't seen your reply
The $ locks the reference its attached (either the row,column or sheet name) to so that it wont change when dragged to different cells.
You will note in the formula that "Template.A34" is not locked and that this refernce changes as the formula is moved


Thank you very much.
I will try it out and let you know if there are any problems.

Razz
_________________
Translator and English teacher in Japan.
Please check out my blog.
Back to top
View user's profile Send private message Visit poster's website AIM Address
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