Added apologies for all spelling errors! It's 1am here and I'm still working!!

"Ed (from UK and useless with computers!)" wrote:

Hi guys,

Ok, I'm confused! I want to do pretty much the same thing as Ralph I think
which is pick the same cell from a load of files in a folder. So far I've
got something that looks like this in my calculation cell....

...='C:\Documents and Settings\excel test\[BANANA.xls]Sheet1'!$D$16......

....which is obviously taking cell D16 from Sheet one of file BANANA D16 in
the specified folder.

However, instead of naming the file BANANA directly in the calculation I'd
like to be able to pick it from cell A1 of the sheet I'm working in. Then I
can simply list all the names in A1, A2, A3...etc and just have the same
formula but with the corresponding cell values!

I've tried....

.....='C:\Documents and Settings\excel test\[A1]Sheet1'!$D$16......

.....but all is does is look for a file named A1! Not what I want!

Don't know if that makes any sense but if anyone can help would be rerally
grateful!

Cheers,

Ed

"Bernie Deitrick" wrote:

Ralph,

Sorry, I slightly misread your post.

Try something like the macro below, that will create a summary from all the
user-selected files. Change the sheet name and range to suit.

HTH,
Bernie
MS Excel MVP

Sub MakeLinkToMultipleUserSelectedFiles()
Dim filearray As Variant
Dim i As Integer
filearray = Application.GetOpenFilename(MultiSelect:=True)
If IsArray(filearray) Then
For i = LBound(filearray) To UBound(filearray)
Workbooks.Open filearray(i)
ThisWorkbook.Worksheets(1).Range("A65536").End(xlU p)(2).Formula = _
"='[" & ActiveWorkbook.Name & "]" & "Sheet1" & "'!" & "A3"
ActiveWorkbook.Close False
Next i
End If
End Sub

"Ralph" wrote in message
...
I want to pull data from about 100 different Excel files. They are located
in
the same folder and drive and the data I want is in consistent cell
locations. I would to find a fast way to reference the files. something
like
S:\Folder\January\CaseNumber1.xls that will allow me to pull-down the
folumula and copy the month and case number from two separate fields on my
spreadsheet. So I would specify the month and case numbers and have them
automatically populate my link formula. Any suggestions???