Build a reference to another worksheet from cell values in currentsheet

[Lucas Reece]

I have a summary sheet and separate week number sheets. In the summary
sheet I have a link to the week number sheets returning data from A1
see below.

A B
1 Week Data (cell A1 from Week ??.xls)
2 01 ='[Week 01.xls]Sheet1'!$A$1
3 02 ='[Week 02.xls]Sheet1'!$A$1
4 03 ='[Week 03.xls]Sheet1'!$A$1

Is it possible to build the formula which includes a cell value held
in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A
$1 in cell B2, I'd like to build the formula something like this...
='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense?

Is this possible?

Many thanks.

 

[Glenn]

I have a summary sheet and separate week number sheets. In the summary
sheet I have a link to the week number sheets returning data from A1
see below.

A B
1 Week Data (cell A1 from Week ??.xls)
2 01 ='[Week 01.xls]Sheet1'!$A$1
3 02 ='[Week 02.xls]Sheet1'!$A$1
4 03 ='[Week 03.xls]Sheet1'!$A$1

Is it possible to build the formula which includes a cell value held
in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A
$1 in cell B2, I'd like to build the formula something like this...
='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense?

Is this possible?

Many thanks.

Look at the INDIRECT() function.

 

[Mike H]

Try

=INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1")

Mike

 

[Lucas Reece]

No, and yes.

No as in:  Not with the out-of-the-box indirect function.

Yes as in, you can change how the data is stored (consolidating it into one
workbook for example) and using a non-volatile function

OR

Have a look at:http://xcell05.free.fr/morefunc/english/

which includes (among other things) a function called INDIRECT.EXE that
works more or less like indirect, but on workbooks that seem closed.

Try
=INDIRECT("'[week " & A2 &".xls]Sheet1'!$A$1")
Mike
:
I have a summary sheet and separate week number sheets. In the summary
sheet I have a link to the week number sheets returning data from A1
see below.
      A            B
1    Week      Data (cell A1 from Week ??.xls)
2    01           ='[Week 01.xls]Sheet1'!$A$1
3    02           ='[Week 02.xls]Sheet1'!$A$1
4    03           ='[Week 03.xls]Sheet1'!$A$1
Is it possible to build the formula which includes a cell value held
in column A for example rather than specify ='[Week 01.xls]Sheet1'!$A
$1 in cell B2, I'd like to build the formula something like this...
='["Week" & A2 & ".xls"]Sheet1'!$A$1. Hope this makes sense?
Is this possible?
Many thanks.

Thanks guys. This works a treat! However, when the week number sheet
is closed  the summary sheet value returns a #REF!. Any way around
this?

Excellent thanks.