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

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?

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

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 V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"GTX" wrote in message
...
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?

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

Ooops, of course you need Sheet1 not Sheet2 in these formulas
--
Bernard V Liengme
Microsoft Excel MVP
www.stfx.ca/people/bliengme
remove caps from email

"GTX" wrote in message
...
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?