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

Finding multi matches/Referencing a cell not using a letter.

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


Joined: 21 Jan 2012
Posts: 68

PostPosted: Tue Feb 07, 2012 10:00 pm    Post subject: Finding multi matches/Referencing a cell not using a letter. Reply with quote

I am trying to reference a cell address by not using a letter. I know this but that braincell has died.
Big picture I am trying to see if a row has more than one x in it.
I am currently using the Match but that only pulls out the first x.
I was thinking of using a formula that will change the second match range to Start after the column the first one found.
For example
=MATCH("X";B24:P24;0)
gives a result of 5
I then want
=MATCH("X";B24+column of the match:P24;0)

I think I have used this function before but I cannot grasp it.
How do I tell if there is more than one value I am searching for in a line and ( if I implement it) get the related value from the row or column (depending on orientation)

I am sure in the morning I will read this question and realize how little sense it makes.

PS how do I upload a file?
_________________
OO 3.3 XP
Back to top
View user's profile Send private message
ken johnson
Super User
Super User


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

PostPosted: Tue Feb 07, 2012 11:38 pm    Post subject: Reply with quote

Attached doc (Where the Xs are.ods) has three possible ways of determining which cells in B24:P24 have an "X"...
http://www.mediafire.com/view/?bz3iw7cnpieyy77
A26:A40 uses the contracting cell range idea. The formula in A26 is not similar to the rest of the formulae in the other 14 column A cells.
B26:B40 uses an array formula. Array formulae must be entered using the Ctrl+Shift+Enter key combination and the Ctrl key must be held down when dragging the fill handle as you fill the array formula into adjacent cells.
D26:E40 uses a standard formula referring to a helper formula.

To attach a file I use one of the free file sharing websites (Mediafire). There are others such as 4shared, etc.

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