Build a reference to another worksheet from cell values in currentsheet
 

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.

Build a reference to another worksheet from cell values in currentsheet
 

Lucas Reece wrote:
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.

Build a reference to another worksheet from cell values in current
 

Try

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

Mike

"Lucas Reece" wrote:

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.

Build a reference to another worksheet from cell values incurrent
 

On 17 Feb, 21:11, Mike H wrote:
Try

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

Mike

"Lucas Reece" wrote:
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?

Build a reference to another worksheet from cell values in cur
 

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.


"Lucas Reece" wrote:

On 17 Feb, 21:11, Mike H wrote:
Try

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

Mike

"Lucas Reece" wrote:
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?

Build a reference to another worksheet from cell values in cur
 

On 17 Feb, 22:16, ~L wrote:
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.

"Lucas Reece" wrote:
On 17 Feb, 21:11, Mike H wrote:
Try

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

Mike

"Lucas Reece" wrote:
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.