How to reference workbook name in hyperlink formula?

[Frank]

In order to simplify the maintenance of workbook formulas I need to
reference workbook name in hyperlink formula

=[myWorkbok.xls]mySheet!$E4

in this way

=[A1]mySheet!$E4

given the file name stored in cell A1

Cell A1 : myWorkbook

How do I write the formula to make it work?

Will it work even if the referenced workbook is closed?


Regards

Frank Krogh

 

[Dave Peterson]

The function you'd want to use is =indirect().

But =indirect() won't work if the sending file is closed.

Laurent Longre has an addin (morefunc.xll) at:
http://xcell05.free.fr/
or
http://xcell05.free.fr/morefunc/english/index.htm

That includes =indirect.ext() that may help you.

===
If you have trouble getting to the site, then search google for indirect.ext.

I found this alternative site:
http://download.cnet.com/Morefunc/3000-2077_4-10423159.html

I didn't look to see if it was the most current version.

I'd check the original site every so often to see if it's working.

In order to simplify the maintenance of workbook formulas I need to
reference workbook name in hyperlink formula

=[myWorkbok.xls]mySheet!$E4

in this way

=[A1]mySheet!$E4

given the file name stored in cell A1

Cell A1 : myWorkbook

How do I write the formula to make it work?

Will it work even if the referenced workbook is closed?


Regards

Frank Krogh