Linking workbooks data via INDIRECT

INDIRECT doesn't work on closed workbooks. You've discovered that. If this is a MUST, then you'll have to install more functionality into your Excel program. Only people with the same installed ADD-IN will be able to use your sheet.

The ADD-IN is called MoreFunc...read all about it here:
http://www.ashishmathur.com/tag/morefunc/

See step #4 of that page for instructions on downloading and installing

Go into TOOLS > ADDINS and activate MoreFunc.

Now you have many, many new functions available to you. Any place you used INDIRECT, now use INDIRECT.EXT instead and it will work on closed workbooks.

morefunc.xll can be embedded directly into the workbook of interest thereby negating the need to install the add-in on multiple clients.
(I *believe* there are issues embedding the add-in in XL2007)