Return a value in a range on another spreadsheet using VLOOKUP?

Hello,

I have two spreadsheets. In one spreasheet I have a sheet I wish to populate (sheet A), while the other spreadsheet (sheet B) has the array I want to use. Normally I'd simply use "insert function" and when prompted to I'd select by table range, but I'd like to call the array using an existing text string in another cell on sheet A.

The text string in this instance is the location of the range I want to use, i.e.

N:\[...]\TEST\[SheetB.xls]Header!headerArray

located in Cell W56 (also the string in W56 is a concatenated from several other cells)

But when I do

=VLOOKUP("25NN2",W56,3,FALSE)

I get a #N/A error.

Please help