Insert CONCATENATE-generated file path into IF formula

Hello all!

Say I have the following formula, which gives me either 1 or 0 as a result of finding "CAT" within a particular range in an external workbook.

=IF('\\root\201310\[Source20131011.xls]Data'!F1:F20="CAT",1,0)

That works fine. Except that the file name and location change everyday, following the current date.

So I came up with this:

=CONCATENATE("\\root\",Z1&Z2,"\[Source",Z1&Z2&Z3,".xls]")

This displays the correct path and filename, corrected daily. The output, in this example, is \\root\201310\[Source20131011.xls], provided that Z1, Z2 and Z3 are cells containing year, month and day respectively.

Now here's where I get stuck. How can I insert this path, which I just generated in the second formula, into the first formula? I tried a lot of stuff, but there seems to be something very wrong with including the CONCATENATE function (or a reference to another cell where the CONCATENATE function is) into the =IF formula.

I've seen some people resolve similar problems using INDIRECT, but that doesn't seem to do the trick for searching inside an entire range of the external file - which I need to be able to do.

Any help much appreciated.

Can you post the formula you tried using indirect, that would be the go-to method for this sort of problem...

Something like this:

=INDIRECT("'"&B1&"Data"&"'!$H$2:$H$30000")

Where B1 holds the CONCATENATE function, and currently displays the referred file path: \\root\201310\[Source20131012.xls]

All I get from that INDIRECT function is #REF!
No difference if I actually type the file path in the formula instead of referring to the B1 cell.

No idea why this doesn't work, it seems like a textbook example of INDIRECT. The only thing is I'm using Excel for Mac - not sure if that matters.

Well it looks ok to me so first thing to check I guess is do you still get a #REF! error if you type this in a cell?
='\\root\201310\[Source20131012.xls]Data'!$H$2:$H$30000

INDIRECT does not work with files that are closed.

Pete