Ed,

Try something like the macro below.

HTH,
Bernie
MS Excel MVP

Sub MakeLinksToFilesInColumnAForEd()
Dim myCell As Range

For Each myCell In Range("A1", Cells(Rows.Count, 1).End(xlUp))
myCell(1, 2).Formula = _
"='C:\Documents and Settings\excel test\[" & _
myCell.Value & "]" & "Sheet1!D16"
Next myCell

End Sub


"Ed (from UK and useless with computers!)" <Ed (from UK and useless with
wrote in message
...
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