Replaceable parameter in formula

[Laramie]

I need to build a spreadsheet that will reference the same cell in about 300 workbooks (all Sheet1). I would like to build a list of the workbook names in the first column, then write a formula, that I could copy down, which would pick up the worksheet names.
For example:

File Name Formula
8801.xls =[ ]Sheet1!$a$1
8802.xls
8803.xls

How can I get the file name in the first column into the formula in the second column, without all of it converting to text? In effect, I'm trying to set up links with external files without opening each of the files and manually linking.

Any ideas would be greatly appreciated!

[MrShorty]

The INDIRECT function can be used to generate the formulas. However, in theory, the INDIRECT function can only retrieve values from an open workbook. I once downloaded an add-in from somewhere that had an updated INDIRECT function that claimed to be able to retrieve data from open and closed workbooks, but I don't remember where I got it from.

HTH

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>

[bj]

try
if book names are in Column A starting row 3
=indirect("[" & A3 & "]Sheet1!A1)

"Laramie" wrote:

>
> I need to build a spreadsheet that will reference the same cell in about
> 300 workbooks (all Sheet1). I would like to build a list of the
> workbook names in the first column, then write a formula, that I could
> copy down, which would pick up the worksheet names.
> For example:
>
> _File_Name_ _Formula__
> 8801.xls =[ ]Sheet1!$a$1
>
> 8802.xls
> 8803.xls
>
> How can I get the file name in the first column into the formula in the
> second column, without all of it converting to text? In effect, I'm
> trying to set up links with external files without opening each of the
> files and manually linking.
>
> Any ideas would be greatly appreciated!
>
>
> --
> Laramie
> ------------------------------------------------------------------------
> Laramie's Profile: http://www.excelforum.com/member.php...o&userid=24191
> View this thread: http://www.excelforum.com/showthread...hreadid=378062
>
>