INDIRECT Function

[Sailor4life]

Hello
My file is myfile2006.xls In cell B2 I have the current year 2006. I
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 wil
not work with closed files. What am I doing wrong?
Thank

 

[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]...

 

[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]...

 

[Guest]

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?

 

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