Linking certain cells from Multiple files in a folder onto a summa

[lee]

hi folks
okay let me try and explain my proplem.
i have a summary sheet which generates quotes and a full summary report.
to do this i must link the data on this file to a specific file in a folder.
i am currently doing this linking manualy when the files are made.
the files are surveys at the moment there are 180 files one per survey and
this will build up way past 18000 files. (thats alot of links)
In an ideal world i would like to simply type in the survey report number in
say cell A1 and then a2:a10 would call up the data from that file from the
specfic cell the data is in.
i will try and put an example for this in hope it explains it better.
a1 = 000001
a2 = ='c:\myfiles\Survey Results 2008\[000001.xls]Sheet1'!$C$9
a3 = ='c:\myfiles\Survey Results 2008\[000001.xls]Sheet1'!$E$3
etc.
except instead of manualy selecting the file and cell i was hopeing for
something more like this (but this doesnt work)
a1 = 000001
a2 = ='c:\myfiles\Survey Results 2008\[(A1).xls]Sheet1'!$C$9
a3 = ='c:\myfiles\Survey Results 2008\[(A1).xls]Sheet1'!$E$3

i hope this makes sense and that you can please help me out.

 

[Ron de Bruin]

Hi lee

Try this macro that create the links for you
http://www.rondebruin.nl/summary2.htm