This really is doing my head in!
Posted by Ian on August 10, 2001 5:17 AM
All
for three weeks i've tried all i can think of and zip, i wasn't going to use this board for this as it was MY problem, the kind that niggles away until you have it. so i concede ARRGH!
the problem is this:
if i have a formula in a cell that returns a number (it doesn't matter what the formula is, but it's a match within a row()). I then want to use that number as a reference and make Excel see it as NUMBER : NUMBER, another match so something like
=match(What_I_Want,REF&":"&REF,0)
this doesn't work, i did ask re: this before but for the life of me i can't find the file i used it on.
This is only the begining of the porblem, my personal challenge (so i can't post it all, as a percentage is being (hopefully) fixed)
hope i've explained it correctly
Ian
Posted by Evan G. on August 10, 2001 5:36 AM
I'm not sure I understand the problem, but the indirect command may be what you're looking for.
If you're trying to build a reference from a value in a cell, you will need to use it.
example:
Match("South",a1:a20)
Returns the row number that South is in.
But, if you don't know to use a20, and instead want to get the last cell from a variable (let's say the value of B1) you would use:
Match("South",indirect("a1:"&b1))
to get the answer.
Hope this helps
Posted by Joe Was on August 10, 2001 5:38 AM
Ian,
This may help you can change the settings to give a number address and/or you can change the result to a number from text. It seems that you will have better luck with VB or create your own function to work the way you want.
Ues:
Option Explicit
Function yourName(ByVal MyData)
your VB code
End Sub
For a worksheet function solution this may help but it is not a true solution.
Use this for data in a ROW it flags the Column.
=ADDRESS(1,MATCH("Yes",A1:G1,0))
the 1 before the MATCH is the ROW, if C1 is the Yes, then the above gives $C$1.
Use this for data in a COLUMN it flags the ROW.
=LEFT(ADDRESS(COLUMN(A1:A30,1),3) & MATCH("Yes",A1:A30,0)
If A21 is the Yes then the above gives $A$21.
To work right your formula range needs to start in A for the column formula (the first one) and the range needs to start in 1 for the row formula (the second one) even if you do not have data in some of the cells.
If you show me what you want to do I may know of a solution as I use exotic references in my applications as well. JSW
Posted by Ian on August 10, 2001 6:09 AM
All of the above and:
this in q18
=ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1))
this will return me the number of the row that p15 occurs in, in this case 23, is in (it's only in once)
i want to use the cell p15 as reference for a match in that row
I.e. =match(p16,q18:q18,0)
the p15:p15 is not what i've been typing but more i want the value() of q18 colon : value() of q18
e.g. =VALUE(Q18)&":"&VALUE(Q18)
you see!?
what i really want is
=ADDRESS(ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),COLUMN(INDEX(A1:M27,ROW(INDEX(A1:M27,MATCH(P15,A1:A27,0),1)),MATCH(P16,23:23,0))))
where the column part (23:23) is the formula for the row &":"& the formula for the row
to eventually offset to the field heading.
It's daft, nay, stupid i know, there are lots of different/easier solutions, I just KNOW it can be done this way. As i said it's driving me MAD and when i get the answer i'll throw it away knowing IT CAN BE DONE and use an easier 1 (does this make sense)
Ian
