Hello
My file is myfile2006.xls In cell B2 I have the current year 2006. In B5 I am trying to reference last years file [MyFile2005.xls]sheet1!B4. The formula that I am using
=indirect("[MyFile"& B2-1 &".xls]Sheet1!" & char(column()+64)&Row()-1)
is returning #ref

Both files are in the same directory. I understand that indirect will not work with closed files. What am I doing wrong?
Thanks