HYPERLINK function combined with CELL/address function

[RobShorter]

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

 

[RobShorter]

Don't worry, I've worked it out.

I needed to add "#"& before the CELL function.

It now reads:

{=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")}