Creating a Range Reference

[Lmason7]

So I have a large file that pulls data from lots of other files. One row pulls data from a different file every week. I want to know if I can concatenate or use indirect to create the lookup array within the match() function. I don't want to hand put in a new file name every week.

This is what I have right now in cell A3 and it works.
=Match("Schedule Adh. %",'[Schedule Adherence WK31 2013.xlsx]Summary'!$6:$6,0)

In cell A2 I have concatenated [Schedule Adherence WK31 2013.xlsx] as a text string.

[Ace_XL]

Have a look at INDIRECT.EXT as it seems its a reference external to your workbook

[Jonmo1]

Yes, you can use indirect for that.
Note, the file written in A2 must be open for this to work.

=Match("Schedule Adh. %",INDIRECT("'"&A2&"Summary'!$6:$6"),0)

[FDibbins]

To add to Ace's post, this is part of the Morefunc add-in that you need to download and run, this allows INDIRECT to work with closed workbooks

[romperstomper]

Since the formula doesn't include a file path, one would assume the workbook will be open.