Using VLOOKUP with array in different workbook and referencing the filename

[Binoy]

Hey all. I've got 2 different workbooks I want to compare the data between, sort of an audit thing. In order to get specific data for comparison I was thinking of using a vlookup function. That's no problem. My issues arrive because the names of the workbooks I need to look up the data in change based on what days I'm looking up.

Now I've got a macro already opened which puts the path and filename as a string in a range name cell. Is there a way to incorporate the range name into the vlookup function? Sort of like:

=vlookup(Lookup_value,'[RangeName]Sheet!Array]

If I simply type in the function with that layout it just throws an open dialog box at me to specify the file, but I want it to use the path specified in the Range name cell.

If there's a better way to do it I'm open to suggestions.

Thanks,

Binoy