Ok, managed to download the file and so far, it is working (the source file
is closed and a long way away.

Will the add-on still work if other people on other computers access the file?



"Dave Peterson" wrote:

Try googling for morefunc. You'll find it.

After Laurent Longre's addin is loaded, that is the easy solution.

Stevep4 wrote:

unfortunately the download link does not work.

It seems very strange that I can change cell contents and the cell will take
the data from the new source without issue.

e.g
Change
='G:\Shared\vital data\[1000339.xls]Sheet1'!$B$16
to
='G:\Shared\vital data\[1007777.xls]Sheet1'!$B$16
and the cell will look to file 1007777 for its info.

But it wont let me enter the required file name into another cell so I could
have something like
='G:\Shared\vital data\[& A5 &.xls]Sheet1'!$B$16
There must be an easy solution.

"Dave Peterson" wrote:

The function you'd want to use that's built into excel is =indirect(). But that
function returns an error if the sending workbook is closed.

If that's a problem, then 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.

Stevep4 wrote:

OK. That works good.
The next thing is how to get the link to work if the source file is closed.
Is there another worksheet function instead of INDIRECT which could be used?

"Gary''s Student" wrote:

=INDIRECT("'C:\test\[" & A1 & ".xls]Sheet1'!$B$3")

--
Gary''s Student - gsnu200826

--

Dave Peterson


--

Dave Peterson