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

=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

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



On Thu, 3 Nov 2011 17:36:42 -0000, "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