I am trying to write a vlookup formula in which the table array is from another file in a different location.I just want to make sure that even if the address of the the file in which table array is defined, is changed it should not effect my formula and it should not ask for the new address. coz there are too many cells containing this formula and I cannot change the address in individual formula. For this what I did was, I copied the address of file [ That contains the table array] into a cell and tried to give the cell reference at vlookup formula using indirect function. But instead of the right value I am getting #ref. My formula goes as follows.

= vlookup($D5,indirect(A1&"["&"Filename"&"]"&"Sheetname'!$A:$Z"),6,False)

Where cell A1 contains the address of the file that contains table array. File name is actual name of the file and Sheet name is the name of the sheet name containing table array.

Can anyone please help me to sort out this issue.