=INDIRECT(a3&"!A1") works in the workbook so

=indirect("'[" & a3 & ".xls]Sheet1'!a1")
should work. NOT tested
Please be aware that indirect does not work with CLOSED files
So, I would then use EditReplace s1, s2
you can have a macro with an inputbox to ask for the file.


On Nov 3, 12:36*pm, "Jim" wrote:
I have 5 spreadsheets - s1 ...s5, one for each of 5 people

A master spreadsheet has links to these 5 *so that cell
* * D1 of the master has *='[s1.xls]Sheet1'!a1
* * D2 of the master has *='[s2.xls]Sheet1'!a1
* * D3 of the master has *='[s3.xls]Sheet1'!a1

and so on

Now * Cell A1 of the master contains S1, A2 has S2, A3 has S3 etc

What I want to go is to have the formulae in D1 ....D5 to ve volatile in as
much as if I change Cell A3 to contain "s2" then the formula in D3 becomes
='[s2.xls]Sheet1'!a1

Can I do it and if so how. Dont really want to create a function or have a
button to run a sub if I can help it, rather do it on the line real time

Jim Crawford