Hyperlink Formula Problem

[Keith Howie]

Hi,
I have a workbook with several worksheets. On my main sheet, in column a, I have a list of numbers formatted to text 0001,0002,0003 etc. that are generated automatically if the next column is populated. There are a series of other sheets named 0001, 0002, 0003, etc. I want to create a hyperlink to take the user to the corresponding sheet that is named the same as the current cell on the listing sheet. I have created this formula but it generates an error "Cannot open the specified file"


=IF(B23="","",HYPERLINK("file:///"&CELL("Filename")&" - "&ADDRESS(14,3,,,TEXT(ROW()-7,"0000")),TEXT(ROW()-7,"0000")))

can anyone tell me what I am doing wrong?

Thanks
Keith

 

[MRT]

try this ... sorry too long ...

=IF(B23="","",HYPERLINK("["&LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]"&ADDRESS(14,3,,,TEXT(ROW()-7,"0000")),TEXT(ROW()-7,"0000")))

HTH
--
MRT


Hi,
I have a workbook with several worksheets. On my main sheet, in column a, I have a list of numbers formatted to text 0001,0002,0003
etc. that are generated automatically if the next column is populated. There are a series of other sheets named 0001, 0002, 0003,
etc. I want to create a hyperlink to take the user to the corresponding sheet that is named the same as the current cell on the
listing sheet. I have created this formula but it generates an error "Cannot open the specified file"


=IF(B23="","",HYPERLINK("file:///"&CELL("Filename")&" - "&ADDRESS(14,3,,,TEXT(ROW()-7,"0000")),TEXT(ROW()-7,"0000")))

can anyone tell me what I am doing wrong?

Thanks
Keith

 

[MRT]

try this ... sorry too long ...

=IF(B23="","",HYPERLINK("["&LEFT(CELL("filename"),SEARCH("[",CELL("filename"))-1)&MID(CELL("filename"),SEARCH("[",CELL("filename"))+1,
SEARCH("]",CELL("filename"))-SEARCH("[",CELL("filename"))-1)&"]"&ADDRESS(14,3,,,TEXT(ROW()-7,"0000")),TEXT(ROW()-7,"0000")))

HTH