Cell refering filename in formula bar

[yee ven]

How do you refer filename1 to a cell in the formula bar?
Example:

='\\Year 2004\September\[_filename1_.xls]sheet1'!$A$1

A | B | C
1 | |
2 | filename1 |
3 | |
4 | |

="\\Year 2004\September\["_B2_".xls]sheet1'!$A$1
will not work

 

[Jazzer]

Hi Yee Ven,

Just add &-chars around the ref, like this:

="\\Year 2004\September\["&B2&".xls]sheet1'!$A$1"

- Asse

 

[Dave Peterson]

I think the function you want to use is called =indirect().

But the bad news is it doesn't work with closed workbooks.

Harlan Grove already wrote that VBA UserDefinedFunction that will return a value
from a closed workbook (when that path/workbookname/sheetname/address is not
directly typed in:


http://www.google.com/[email protected]

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

How do you refer filename1 to a cell in the formula bar?
Example:

='\\Year 2004\September\[_filename1_.xls]sheet1'!$A$1

A | B | C
1 | |
2 | filename1 |
3 | |
4 | |

="\\Year 2004\September\["_B2_".xls]sheet1'!$A$1
will not work.