linking cells to external refernce

[Harry73]

Hi,
Hoping someon can help as I've been trying to do this for a while. I
have a list of file names in cells in a workbook. This list is created
automatically when a new file is created. Each file has a static cell
that gives a total metres left on a roll. I would like to summurise
these totals and place them next to each file name in the main workbook.
But I need excel to look at the cell containing the name of the workbook
and report back the total from that file. The filles are on a local
server.

I have used the formula ='Z:\Stock\[Newstockcard.xls]Sheet1'!$E$8

All I need is for excel to replace the "newstockcard.xls" with the file
name in cell E9.

Greatly appreciated if anyone can help!

** Posted via: http://www.ozgrid.com
Excel Templates, Training, Add-ins & Business Software Galore!
Free Excel Forum http://www.ozgrid.com/forum ***

 

[Cesar Zapata]

If you refer to another workbook is easy with INDIRECT..... the problem
is that INDIRECT does not work if the other workbook is closed.

but there is another using VBA check this site.


http://j-walk.com/ss/excel/tips/tip82.htm



good luck.