How can I dynamically change a file link in a formula?

[Guest]

Our users at the call center here track their calls via an Excel Sheet. They
may name the Excel sheet whatever they want, and then at the end of each week
they E-Mail the Excel Sheet to me, and I check call volumes, types of calls,
etc...

In each Excel sheet they have there's a page that automattically tracks
certain data. I'd like to create an Excel sheet that will allow me to simply
type in the filename (such as "bob.xls") into, let's say, Sheet 1, A1. In
Sheet 1, A2, an example formula I would have would be
=SUM([bob.xls]Sheet1!$A$1:$A$10). However, in that formula, if I were to
change the "A1" on my tracking list, the formula would remain the same. What
I want to do is be able to change A1 to, say, "joe.xls", the forumula would
AUTOMATICALLY change to =SUM([joe.xls]Sheet1$A$1:$A$10).

Any ideas?

 

[Bernard Liengme]

Try =SUM(INDIRECT("["&A1&"]Sheet2!$A$1:$A$10"))
Or in case they have spaces in the file name
=SUM(INDIRECT("'["&A1&"]Sheet2'!$A$1:$A$10"))
That is: .....INDIRECT( double-quote single-quote [ .......
best wishes

 

[Bernard Liengme]

Ooops, of course you need Sheet1 not Sheet2 in these formulas