INDIRECT Function

[Sailor4life]

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

[Pete_UK]

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

[Arvi Laanemets]

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


Arvi Laanemets


"Sailor4life" <[email protected]>
wrote in message
news:[email protected]...
>
> 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]

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.

[Biff]

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" <[email protected]>
wrote in message
news:[email protected]...
>
> 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
>

[Kevin Vaughn]

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
>
>

[Harlan Grove]

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)