Correction:
=INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&"$A$1")


"rmellison" wrote:

Try this in B1:

=INDIRECT(A1&"$A$1")

You will need to enter the correct path, file name and sheet name in cell
A1, using apostrophes and excamation marks where they would usually appear in
a normal link. This will link to 'S:\Projections\Fall
2005\[Nov05.xls]Projection'!$A$1 as per your example.

To elaborate you could use something like this in, say, D1:

=INDIRECT("'"&A1&"["&B1&"]"&C1&"'!"&$A$1)

Where A1 is your path, B1 is your filename and C1 is your sheet name.

HTH


"Matt S. R." wrote:

I want to be able to change the name of a file that is used to update data
without using edit links change source, for an existing link. I would
like to be able to just enter in the file name by hand in one cell and have
another cell use that new filename to complete it's formula. If I simply
link cell B1 in the file I am working in to cell A1 on a tab called
"Projection" in a file called "Nov05" on a network drive "S:", the formula in
the cell might look like:

='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

What I would like to be able to do is in Cell A1 of the file I am working
in, be able to hand write the file name (or full path if necessary) and have
the formula in cell B1 not link directly to the source file but link to cell
A1 in the same file, to know which source file to get the data from. For
example, I would want cell A1 to maybe have 'S:\Projections\Fall
2005\[Nov05.xls]Projection'! in it while cell b1 would have the cell location
of the data. Cell B1 would look to cell A1 to get the path/file/tab to go to
and might just look like:

=A1$A$1

instead of a direct link which would look like:

='S:\Projections\Fall 2005\[Nov05.xls]Projection'!$A$1

I've tried several ways to combine the data in different cells to get a
working formula and tried to use the Help function to no avail. If this is
even possible to do something like this, I would really appreciate any help.
Thanks.

Matt