Automating gathering data from other workbooks

[tumbler]

I am trying to gather data from a number of other excel files, I want to piece the file reference name together from other pieces of information - in a CONCATENATE way if you like, getting the network address from here - file name from there etc..
However, when I do this, I then need to copy and paste special value, from the CONCATENATE formula, then enter the cell and press return again before it actually becomes automatic - any help please ?!

 

[Marshalls]

sorry i dont understand exactly what you want to do here do you want to beable to change where you are pulling your info from or will it allways be the same?

 

[tumbler]

I want to change where the data is coming from because the main "index" workbook will be interrogating about 100-150 separate project workbooks - so I am very keen to automate this task, once the link is established it shouls rarely - if ever change, so I suppose I could set up a macro to copy, past special, value, then re-enter cell and enter again (the only way I seem able to get the system working to the moment)