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

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

"Keith Howie" wrote in message ...

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

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

"Keith Howie" wrote:


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