On 17 Feb, 22:16, ~L <L...@discussions.microsoft.com> 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 <Mi...@discussions.microsoft.com> 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.
Similar Threads
