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


--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile: http://www.excelforum.com/member.php...o&userid=33723
View this thread: http://www.excelforum.com/showthread...hreadid=535045

Try putting brackets around B2-1 and Row()-1, i.e.:

=indirect("[MyFile"& (B2-1) &".xls]Sheet1!" &
char(column()+64)&(Row()-1))

and ensure that the file is open (as you are already aware)

Pete

When I remember correctly, INDIRECT works with external sourece only, when
this is open at same time


Arvi Laanemets


"Sailor4life"
wrote in message
...

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


--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile:
http://www.excelforum.com/member.php...o&userid=33723
View this thread: http://www.excelforum.com/showthread...hreadid=535045

Yes thats why I made the statement about the open file. Both file are
open at the time of the error. Even with this change suggested by Pete
I am still getting the error. It appears as though this formula
evalutes with " around the ref.


--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile: http://www.excelforum.com/member.php...o&userid=33723
View this thread: http://www.excelforum.com/showthread...hreadid=535045

Hi!

Your formula works for me. (as long as both files are open)

Is the year number in B2 manually entered? Is it a date that's formatted to
display just the year?

Biff

"Sailor4life"
wrote in message
...

Yes thats why I made the statement about the open file. Both file are
open at the time of the error. Even with this change suggested by Pete
I am still getting the error. It appears as though this formula
evalutes with " around the ref.


--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile:
http://www.excelforum.com/member.php...o&userid=33723
View this thread: http://www.excelforum.com/showthread...hreadid=535045

Your formula worked for me, so a guess is, is it possible that the 2006 in
cell B2 is actually a date formatted to look like 2006 rather than the number
2006?
--
Kevin Vaughn


"Sailor4life" wrote:


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


--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile: http://www.excelforum.com/member.php...o&userid=33723
View this thread: http://www.excelforum.com/showthread...hreadid=535045

Sailor4life wrote...
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?

I suppose you've checked that [MyFile2005.xls]Sheet1!B4 itself doesn't
evaluate to #REF! .

Basic debugging applies. First, drop the INDIRECT but add visible
characters around the textref.

=""&("[MyFile"&B2-1&".xls]Sheet1!"&CHAR(COLUMN()+64)&ROW()-1)&"<"

Does this evaluate to "[MyFile2005.xls]Sheet1!B4" ? If so, is these
*really* your workbook and worksheet names? Even if they are, it never
hurts and often helps to enclose them inside single quotes, so

"'[MyFile"&B2-1&".xls]Sheet1'!"&CHAR(COLUMN()+64)&ROW()-1

as your textref. Next, check the literal external reference.

=[MyFile2005.xls]Sheet1!B4

If these turn out OK, then try R1C1 addressing. Since you seem to want
the value of B4 returned to cell B5, try

=INDIRECT("'[MyFile"&B2-1&".xls]Sheet1'!R[-1]C",0)