CELL function within HYPERLINK

[VadimGor]

Can I use a CELL function for extracting a workbook name for further
using in the HYPERLINK function.
Something like this HYPERLINK("[CELL("filename")]June!A1").
It is always gives an error or jsut build a wrong link.

Thanks

 

[Bob Phillips]

The problem is the [ & ] and worksheet name that gets brought in.

Try this

=HYPERLINK(SUBSTITUTE(LEFT(CELL("filename",[data99.xls]journal!A1),FIND("]",
CELL("filename",[data99.xls]journal!A1))-1),"[",""))

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[VadimGor]

Thanks,

it works fine with excel saved on the disk.
I still falling into original problem,
when an excel report opened from WEB. The file saved in the "Temporary
Internet Files" and a name is changed from "report_XXXX.xls" to
"report_XXXX[1-N].xls". Excel raise a security warning concerning a
document referenced to an internet
space, either it treats a name as report_XXXX(1-N).xls.

Any ideas, how possible to get a "clean" workbook name ?

Thanks