Hello. I need to move three identical cells that are in 2000 separate
excel files into one sheet for analysis. A few months ago someone had
told me to copy the filenames into an excel spreadsheet and then to
use a formula to have the program extract the cell data from the
closed files into the opened worksheet.

I have done all of that but I am now getting a formula error that I
cannot figure out.

When I opened a new spreadsheet, I have put the file names in the A1
column and the formula below in the B1 column.

The formula that I am using is:

='c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\[" & a1 & "]Sheet1"'!"$A$1


Here is what I believe each part of the formula is trying to do.

c:\Documents and Settings\Owner\My Documents\Subject Profile Summary
LBJ\ - This is the file directory path.

[" & a1 & "] - I assume that this tells the program to search in the
directory path AND in the specific file name in column a1, and then
to...

Sheet1"'!"$A$1 - ... look in Sheet1 in all the specific files. The
last part of the formula tells the program which cell to copy into the
B1 column.

This is where my problem lies. I am getting a formula error where
Excel is highlighting this portion of the formula. "$A$1

I am very inexperienced with Excel formulas. Can someone explain to me
where my error is? I am wondering if there is an error within any
other part of the formula.

Thank you very much. If I can get this formula to work, it will save
me hours of work.

Steven