Indirect function error

[D_Train]

Hi,

I am using the indirect funciton combined with the match funciton to look up cells from external workbooks. All the workbooks are open however I am faced with a sheet full of #ref cells .

Yesterday the same thing happened, although the sheet decided to work an hour after my deadline!

Is it possible that the workbook doesn't recognise that the other workbooks are open?

Cheers, Dave

 

[gauntletxg]

What is your formula?

The syntax should be something like:

=MATCH(A1,INDIRECT("'C:\MyFile.xls'!A1:A10"),0) - note the single quotes enclosing the filename

 

[D_Train]

=offset(indirect("'["&$a6&"]"&$a$1&"'!a1"),match($b6,indirect("'["&$a6&"]"&$a$1&"'!$a:$a"),0)-1,match(f$3,indirect("'["&$a6&"]"&$a$1&"'!$6:$6"),0)-1)

I haven't been using the file path, just the file name.

 

[gauntletxg]

Well I'm not going to debug a long formula where I don't know any of the inputs. Maybe try removing the brackets, i.e. this

=MATCH(A1,INDIRECT("'[this is a test.xls]'!A1:A10"),0)

does not work. But, this does

=MATCH(A1,INDIRECT("'this is a test.xls'!A1:A10"),0)

 

[D_Train]

Thanks, my reference didn't include .xls and the end of the file name.

That has done the trick!

P.S. It worked the day before without the .xls however..