RobShorter
New Member
- Joined
- Jul 8, 2010
- Messages
- 18
Hi,
The following formula:
{=IF(LEN($C$10)=0,"",IF(ROWS(A$2:A3)<=F$9,CELL("address",INDEX(Sheet1!$F$2:$F$12000,SMALL(IF(Sheet1!$F$2:$F$12000=Sheet2!$C$10,ROW(Sheet1!$F$2:$F$12000)-ROW($A$2)+1),ROWS(A$2:A3)))),""))}
Returns the following result:
'[Book1.xls]Sheet1'!$F$236
What I would like is a hyperlink to the above result, but when I adjust the formula to:
{=HYPERLINK(IF(LEN($C$10)=0,"",IF(ROWS(A$2:A3)<=F$9,CELL("address",INDEX(Sheet1!$F$2:$F$12000,SMALL(IF(Sheet1!$F$2:$F$12000=Sheet2!$C$10,ROW(Sheet1!$F$2:$F$12000)-ROW($A$2)+1),ROWS(A$2:A3)))),"")),"Link")}
It doesn't work.
However, if I type the following into a cell:
=HYPERLINK("#'[Book1.xls]Sheet1'!$F$236","Link")
It works.
How do I combine the two?
Thanks in advance,
Rob
The following formula:
{=IF(LEN($C$10)=0,"",IF(ROWS(A$2:A3)<=F$9,CELL("address",INDEX(Sheet1!$F$2:$F$12000,SMALL(IF(Sheet1!$F$2:$F$12000=Sheet2!$C$10,ROW(Sheet1!$F$2:$F$12000)-ROW($A$2)+1),ROWS(A$2:A3)))),""))}
Returns the following result:
'[Book1.xls]Sheet1'!$F$236
What I would like is a hyperlink to the above result, but when I adjust the formula to:
{=HYPERLINK(IF(LEN($C$10)=0,"",IF(ROWS(A$2:A3)<=F$9,CELL("address",INDEX(Sheet1!$F$2:$F$12000,SMALL(IF(Sheet1!$F$2:$F$12000=Sheet2!$C$10,ROW(Sheet1!$F$2:$F$12000)-ROW($A$2)+1),ROWS(A$2:A3)))),"")),"Link")}
It doesn't work.
However, if I type the following into a cell:
=HYPERLINK("#'[Book1.xls]Sheet1'!$F$236","Link")
It works.
How do I combine the two?
Thanks in advance,
Rob