The syntax of your formula is illegal. I think you've got confused between
a dollar and an ampersand.

Have you tried in A2 the formula
=INDIRECT("'\\...\[week"&A$1&".xls]Sheet1'!$Z"&ROW(A2)-1) ?

In A3 this becomes =INDIRECT("'\\...\[week"&A$1&".xls]Sheet1'!$Z"&ROW(A3)-1)
and in B2 it becomes
=INDIRECT("'\\...\[week"&B$1&".xls]Sheet1'!$Z"&ROW(B2)-1)
--
David Biddulph

"Excelhobbyist" wrote in message
...
However.... now that I'm using this....
Is there a way to make the cellreference in the INDIRECT statement
(External
or not - I trust for the moment that they behave in the same manner)
behave
according to the standard rules of relative references, i.e. imagine:
A1=41
B1=42
A2=INDIRECT("'\\...\[week"&A$1$".xls]Sheet1'!$Z1")

Copying A2 to A3 should give:
A3=INDIRECT("'\\...\[week"&A$1$".xls]Sheet1'!$Z2") (i.e from week 41)
While copying A2 to B2 gives:
B2=INDIRECT("'\\...\[week"&B$1$".xls]Sheet1'!$Z1") (i.e. from week 42)

Again, thanks in advance!

shifts from Z1 to Z2 if the formula is copied downwards to A3, but does
not
shift to AA1 when the formula is copied to B2 ??

"Mike H" wrote:

Hi,

You need indirect

=INDIRECT("'[Week" & $A2 &".xls"& "]sheet1'!C15")

Mike

"Excelhobbyist" wrote:

I can't believe there is not an easy way to solve the following, but
thusfar,
I've not been able to find it. Your help is much appreciated:
I want to use the value of one cell to determine the filename that is
used
as a reference in a different cell:
A1='\\....\[weekxx.xls]'Sheet1'!$C$15
Where xx is the value of Cell A2.
So if A2=41, then the reference is to file week41.xl and A1 will be
filled
with the content of C15 in Sheet1 of that file
If A2=42, the reference is to week42.xls etc.
Anyone knows a solution that does not involve creating a macro?

Thanks in advance