Dynamic links to other sheets

[Jim]

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

 

[Don Guillett]

=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 Edit>Replace> s1, s2
you can have a macro with an inputbox to ask for the file.

 

[Gord Dibben]

You can use the INDIRECT function.

=INDIRECT("'[" & A1 & ".xls]Sheet1'!A1") entered in A1 of Master and
copied to A5

Note that referenced workbooks must be open for INDIRECT to work.

If this would be a problem, you could download Laurent Longre's
MOREFUNC add-in which has the INDIRECT.EXT function capable of
referencing closed workbooks.

http://xcell05.free.fr/morefunc/english/index.htm


Gord