software

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(quot;[MyFilequot;amp; B2-1 amp;quot;.xls]Sheet1!quot; amp; char(column() 64)amp;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: www.excelforum.com/member.php...oamp;userid=33723
View this thread: www.excelforum.com/showthread...hreadid=535045Try putting brackets around B2-1 and Row()-1, i.e.:

=indirect(quot;[MyFilequot;amp; (B2-1) amp;quot;.xls]Sheet1!quot; amp;
char(column() 64)amp;(Row()-1))

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

PeteWhen I remember correctly, INDIRECT works with external sourece only, when
this is open at same timeArvi Laanemetsquot;Sailor4lifequot; gt;
wrote in message
...
gt;
gt; Hello
gt; My file is myfile2006.xls In cell B2 I have the current year 2006. In
gt; B5 I am trying to reference last years file [MyFile2005.xls]sheet1!B4.
gt; The formula that I am using
gt; =indirect(quot;[MyFilequot;amp; B2-1 amp;quot;.xls]Sheet1!quot; amp; char(column() 64)amp;Row()-1)
gt;
gt; is returning #ref
gt;
gt; Both files are in the same directory. I understand that indirect will
gt; not work with closed files. What am I doing wrong?
gt; Thanks
gt;
gt;
gt; --
gt; Sailor4life
gt; ------------------------------------------------------------------------
gt; Sailor4life's Profile:
www.excelforum.com/member.php...oamp;userid=33723
gt; View this thread: www.excelforum.com/showthread...hreadid=535045
gt;

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 quot; around the ref.--
Sailor4life
------------------------------------------------------------------------
Sailor4life's Profile: www.excelforum.com/member.php...oamp;userid=33723
View this thread: www.excelforum.com/showthread...hreadid=535045Hi!

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

quot;Sailor4lifequot; gt;
wrote in message
...
gt;
gt; Yes thats why I made the statement about the open file. Both file are
gt; open at the time of the error. Even with this change suggested by Pete
gt; I am still getting the error. It appears as though this formula
gt; evalutes with quot; around the ref.
gt;
gt;
gt; --
gt; Sailor4life
gt; ------------------------------------------------------------------------
gt; Sailor4life's Profile:
gt; www.excelforum.com/member.php...oamp;userid=33723
gt; View this thread: www.excelforum.com/showthread...hreadid=535045
gt;
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 Vaughnquot;Sailor4lifequot; wrote:

gt;
gt; Hello
gt; My file is myfile2006.xls In cell B2 I have the current year 2006. In
gt; B5 I am trying to reference last years file [MyFile2005.xls]sheet1!B4.
gt; The formula that I am using
gt; =indirect(quot;[MyFilequot;amp; B2-1 amp;quot;.xls]Sheet1!quot; amp; char(column() 64)amp;Row()-1)
gt;
gt; is returning #ref
gt;
gt; Both files are in the same directory. I understand that indirect will
gt; not work with closed files. What am I doing wrong?
gt; Thanks
gt;
gt;
gt; --
gt; Sailor4life
gt; ------------------------------------------------------------------------
gt; Sailor4life's Profile: www.excelforum.com/member.php...oamp;userid=33723
gt; View this thread: www.excelforum.com/showthread...hreadid=535045
gt;
gt;

Sailor4life wrote...
gt;My file is myfile2006.xls In cell B2 I have the current year 2006. In
gt;B5 I am trying to reference last years file [MyFile2005.xls]sheet1!B4.
gt;The formula that I am using
gt;=indirect(quot;[MyFilequot;amp; B2-1 amp;quot;.xls]Sheet1!quot; amp; char(column() 64)amp;Row()-1)
gt;
gt;is returning #ref
gt;
gt;Both files are in the same directory. I understand that indirect will
gt;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.

=quot;gt;quot;amp;(quot;[MyFilequot;amp;B2-1amp;quot;.xls]Sheet1!quot;amp;CHAR(COLUMN() 64)amp;ROW()-1)amp;quot;lt;quot;

Does this evaluate to quot;[MyFile2005.xls]Sheet1!B4quot; ? 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

quot;'[MyFilequot;amp;B2-1amp;quot;.xls]Sheet1'!quot;amp;CHAR(COLUMN() 64)amp;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(quot;'[MyFilequot;amp;B2-1amp;quot;.xls]Sheet1'!R[-1]Cquot;,0)