On Jul 10, 12:25 pm, Steven wrote:
On Jul 10, 12:10 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote:



Oops, I forgot to include the other macros that are called:

Sub TurnOff()
With Application
.ScreenUpdating = False
.Calculation = xlCalculationManual
.EnableEvents = False
End With
End Sub

Sub TurnOn()
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
.EnableEvents = True
End With
End Sub

These assume that you use automatic calculation - rare to not use it, especially for newer users.

HTH,
Bernie
MS Excel MVP

"Bernie Deitrick" <deitbe @ consumer dot org wrote in . ..

Oh, and to get the formula to work, you would need to have used:

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

But then you would have needed a macro to convert the result of the formula into a true formula.
With the cells with the above formula selected:

Sub TransformToFormula()
Dim myCell As Range
TurnOff
On Error Resume Next
For Each myCell In Selection
myCell.Formula = myCell.Text
Next myCell
TurnOn
End Sub

HTH,
Bernie
MS Excel MVP

"Steven" wrote in message
ups.com...
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

Thank you Bernie. My problem is that I don't know how to add these
other macro's. Looks very complicated.
If I open up 100 files at a time, is there a quick way for me to
transfer some of the cells into a new worksheet?

Thank you again.

Steven

Thank you Bernie. Much appreciated.

Steven