Help with formula to pick up a file path to a workbook from a cell in the same worksheet

Hi everyone,

I have a bit of a problem. I am trying to make a series of workbooks which contain different stages of a fairly complicated report. There are over 60 reports in total so having multiple workbooks is the only way to separate it to a level that can be usable by anyone.

What I am trying to do is get my Index function to work across workbooks. I can link the workbooks manually but this is time consuming and is prone to mistakes and more problems.

This is my Formula atm with some test names and such put in just for examples:

{=INDEX('C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$C$1:$C$2500,MATCH(1,($A6='C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$B$1:$B$2500)*($E6='C:\Users\Ian Stubbs\Documents\Jason Work Experience\[1392_39_JS02216S1R.xlsx]Sheet1'!$E$1:$E$2500),0))}

The formula works absolutely fine but has to be manually linked for each new report as they all have different numbers for the file name. I am wondering if it is at all possible to replace the filepath links in this formula with a cell in the same worksheet. And then within that cell it contains the filepath to the required file. However every time I have attempted to do this it simply won't load the filepath properly and returns an error.

Can anyone help at all?