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.