Combine Hyperlink formula with Index and Match possible?
Posted by Connie on July 13, 2001 7:53 AM
Thanks to Aladin, I have a formula in one cell that performs a lookup, and returns a name (it's a name drawn from having a "Top 15" score) and I'm wondering if it would be possible to add a hyperlink function to this formula, to where I could click on the name and be taken to a separate document to read more about that person. The tricky thing is, as scores change, so will the names of the people, so if Jones is the top scorer today (in cell A1), he may be replaced by someone else tomorrow who gets a higher score. The way this was being treated was to have a hyperlink formula in an adjacent cell, but my boss thinks it could all be combined into one formula, to where I could click on the name Jones and be taken to his document. I can't imagine how this could be done. Any thoughts?
Here is the formula that looks up the Top 15 scorers:
=IF(ROW()-1<=15,INDEX('ALL NAMES'!A$6:A$26,MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0)),"")
and here is the hyperlink formula that currently sits in an adjacent cell:
=HYPERLINK(VLOOKUP(A16,NamesTable,2,0))
Does anyone think these could be combined into one cell?
Thanks,
Connie
Posted by Aladin Akyurek on July 13, 2001 8:50 AM
Connie,
Try the following wrapping :
=IF(ROW()-1<=15,IF(ISNUMBER(MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0))),VLOOKUP(INDEX('ALL NAMES'!A$6:A$26,MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0)),NamesTable,2,0),"")
I hope I did the parens right.
Aladin
========
Posted by Aladin Akyurek on July 13, 2001 9:06 AM
I see I omitted the HYPERLINK part in the mega formula. This should do it:
=IF(ROW()-1<=15,IF(ISNUMBER(MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0))),HYPERLINK(VLOOKUP(INDEX('ALL NAMES'!A$6:A$26,MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0)),NamesTable,2,0)),"")
My caveat about parens still holds. ;-)
Aladin ,
Posted by Connie on July 13, 2001 9:28 AM
Good Job, Aladin! Almost there! Excel seems to have a problem with seeing one too many parentheses in part of the ISNUMBER argument (where there are 3 in a row):
...ISNUMBER(MATCH(ROW()-1,'ALL NAMES'!$J$6:$J$26,0))),...
If I remove one, and add another parenthesis to the very end of the whole formula, I no longer get an error msg and the link works! The only trouble is, the text that is being displayed is the path leading to the person's Word document. I know that's because I have the lookup table set up so that the name is in col. 1 and the path is in 2. Of course, if I have the Lookup formula look in column 1, then the name is displayed, which is good, but the link will no longer work.
Any way around that aspect? I'm impressed (very impressed!) that you've got it to work thus far!!
Thanks!
Connie ,
Posted by Aladin Akyurek on July 13, 2001 9:46 AM
You want to see the name I guess. I see 2 possible solutions to that: (a) You add to the path/link the name of the person concerned; (b) expand further the mega formula such that it adds the person's name to the path it retrieves.
I'd suggest trying the first option.
PS. I'm usually not bad at parens given my Lisp-experience, a programming language that scares some people because of its use of parens as begin and end delimiters. This time I've lost the count of parens though, whence the caveat.
Aladin
====== Good Job, Aladin! Almost there! Excel seems to have a problem with seeing one too many parentheses in part of the ISNUMBER argument (where there are 3 in a row):
Posted by Connie on July 13, 2001 11:00 AM
Aladin, thanks for your help! (n/t)
I'd suggest trying the first option. PS. I'm usually not bad at parens given my Lisp-experience, a programming language that scares some people because of its use of parens as begin and end delimiters. This time I've lost the count of parens though, whence the caveat. ====== : Good Job, Aladin! Almost there! Excel seems to have a problem with seeing one too many parentheses in part of the ISNUMBER argument (where there are 3 in a row):
