Hello,
Please bear with me - I am a long term excel user but I am new to Questions
and I may not have phrased this very well.. I am using Excel 2000 on a work
computer. I apologise if this has already been answered - I did see some
similar questions but none which explained how to do this.

I have made an application form spreadsheet to send out to new teachers.
When they are sent back they are saved in the same folder under teachers'
name, ie JohnSmith.xls, AnnabelleGreen.xls etc. Their data is compiled from
the form in a single column in a hidden sheet called Data in each workbook.

I want to make a database workbook (in the same folder) which will take
information from all of these data sheets in each file and keep them together
on one sheet so that we can run mailmerges etc.

I could copy and paste the data, but I would like it to update
automatically. I would like them to be able to save their application form,
enter the name once on an index sheet, and the database would update with the
info from the new application. I would also like not to use macros as our
council computers crash a lot, if possible I would like to do this entirely
with formulas and links. (I realise this would be easy with a "find and
replace" macro).

I can link to any of the teacher applications once I know their name - with
='[JohnSmith.xls]Data'!B2 etc.

Basically I would like the "Johnsmith.xls" bit to be variable based on what
I type into a cell in an index of teachers, so that in the index I will have
cell a1 "johnsmith.xls", cell a2 "annabellegreen.xls", and in my database
sheet I will have "='[Index!a1]Data'!B2" which will point to cell b2 in
sheet "data" of workbook "Johnsmith.xls"
and
"='[Index!a2]Data'!B2" which will point to cell b2 in sheet "data" of
workbook "Annabellegreen.xls"

However I can't find a way of making the filename in a link variable based
on the cell value.


Is there a way of doing this? It feels like there should be, but I can't
find a way. I thought the "substitute" command might work but it seems it
only operates on the result of a formula and not on the actual text of the
formula itself.

Many thanks to anyone who can sort this out!

Bekki