calling a value from another file using a variable in the file nam

[DA_Potts]

I want to be able to get a vale from another Excel file where the value of
"E3" will change depending on the day of the month.

Below was what I tried, but it did not work.

=INDIRECT(CONCATENATE("'[daily flash ",E3,".xls]sheet1'! E22"))

I have the file open that Iwant to call from.
I am using Excel 2003.

 

[T. Valko]

Try it like this:

=INDIRECT("'[daily flash "&E3&".xls]sheet1'!E22")

 

[David Biddulph]

It works for me.

What value do you have in E3 (or is it a formula)? You would have problems
if, for example, E3 was a date formatted as "dd", and your file name
contained just the day number. If E3 is the result of a DAY() formula then
it should work, but remember that this will return 2 for today, not 02. If
you want 02 you might need =TEXT(DAY(...),"00") in E3 or TEXT(E3,"00")
instead of E3 in your formula.

 

[Tim Zych]

The formula works OK for me, but if there are single quotes in the workbook
name (e.g. "december's.xls"), they'll have to be doubled.
This modification should work for all workbook names.

=INDIRECT(CONCATENATE("'[daily flash
",SUBSTITUTE(E3,"'","''"),".xls]sheet1'! E22"))

Otherwise, you can troubleshoot it by selecting the concatenate part of the
formula in the formula bar, press F9, copy, (ESC to revert formula), paste
result into cell, and go from there.