| View previous topic :: View next topic |
| Author |
Message |
qaz1qaz1qa Power User


Joined: 21 Jan 2012 Posts: 68
|
Posted: Tue Feb 07, 2012 10:00 pm Post subject: Finding multi matches/Referencing a cell not using a letter. |
|
|
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 |
|
 |
ken johnson Super User

Joined: 23 Apr 2009 Posts: 1849 Location: Sydney, Australia
|
Posted: Tue Feb 07, 2012 11:38 pm Post subject: |
|
|
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 |
|
 |
|