INDEX Function on Variable Filename

I am trying to display the value of cell D2567 from different workbooks, onto a single workbook, using the filename name in the function. For example, I have all of my workbook names in Column C, and in column E, I would like to pull cell D2567 from each workbook.

In Column E, I have the following formula:

=INDEX("'D:\Document\["&C2&".xlsx]Sheet1'!D:D,2567)

I cannot seem to get the variable filename to work, and believe I do not have the quotation marks in the right place. I have tried many different variations but to no avail.

Originally Posted by Pepe Le Mokko

Perhaps =INDEX("'D:\Document\["&C2&".xlsx]Sheet1'!D:D",2567)

Pepe, that formula still doesn't work and results in an error.