struggling with INDEX function and using a cell value as part of the reference - is it possible?

[sparx]

struggling with INDEX function, and need some help

I have a number of closed excel workbooks
I have a consolidation workbook that should look up values from the closed workbooks placed in different folders

I can manually enter a index function to make the lookup work - but I need the function to be automatic based on cell values in the consolidation workbook.

e.g.
=INDEX('C:\Desktop\PE\PE_1130\[PE1130.xlsx]PE'!C:C;3;1) this works manually entering

but the reference should be dynamic like this example using cell value c6 to create the path
="'C:\Desktop\PE\PE_" &C6& "\[PE"&C6&".xlsx]PE'!C:C;3;1"

But I cannot figure out how to get this into the INDEX function?

Is that possible???

TIA

 

[Fluff]

You would need to use the INDIRECT to do that, but it does not work with closed workbooks.