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

Hello excelhobbyist,

You can try using INDIRECT function, something like

=INDIRECT("'\\....\[week"&A2&".xls]'Sheet1'!$C$15")

obviously you need to include the full path.

Note: that INDIRECT won't work with closed workbooks though. If you
want it to work with a closed workbook you could try downloading
Morefunc add-in from this link

http://www.download.com/Morefunc/300...-10423160.html

and then use INDIRECT.EXT function

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

Thanks Mike and Barry!
In fact, Barry nailed the problem best for me because he correctly suspected
that the files are indeed closed at the time I want to interrogate them. so
INDIRECT.EXT from the downlaoded library did the trick!
Thanks to both of you!

"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

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

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