Automatically increment folder and filename for referenced cell

Good afternoon

I have a worksheet with several cells which are referenced from another, closed workbook.

An example of once such cell (D7) is ='C:\Users\leonj_000\Documents\Dissertation - INSM025\FOI\FOI Responses\6\[6.xlsx](1) Project Management'!$C$3

What I would like to do is have a similar formula on the following row (D8) which would reference 'C:\Users\leonj_000\Documents\Dissertation - INSM025\FOI\FOI Responses\7\[7.xlsx](1) Project Management'!$C$3

I could move each of the files into a single folder if it is easier to just increment the filename.

I have looked at Indirect and other things but cant figure it out.

Thanks

Leon

I have three worksheets in each workbook, and 228 workbooks - so lots of sheets. So whilst I could copy them into the master workbook, that would be nearly 700 sheets.

Is there a VBA way which someone could help me with?

Thanks in advance.

Yes, there are 16 cells in the master workbook, D2 through S2, and each of those cells (on the same row) pull data from one of the three sheets I mentioned.

D, E & F take data from sheet one
G, H, I, J take data from sheet 2
K through S take data from sheet 3

The data in those remote cells is consistent for every workbook, it is just that the filename of the workbooks are different. It is basically a questionnaire being completed, and I have named each questionnaire by the identifier for that organisations return, a number between 1 and 238.

So as an example for row 7 (which is actually return 6 because I have a header row) looks like this; (one example cell per remote sheet)

D7 = ='C:\Users\leonj_000\Documents\Dissertation - INSM025\FOI\FOI Responses\6\[6.xlsx](1) Project Management'!$C$3

G7 = ='C:\Users\leonj_000\Documents\Dissertation - INSM025\FOI\FOI Responses\6\[6.xlsx](2) Service Management'!$C$3

K7 = ='C:\Users\leonj_000\Documents\Dissertation - INSM025\FOI\FOI Responses\6\[6.xlsx](3) Natl. Systems & Frameworks'!$C$3

So on the next row, row 8, I would want the data from filename 7.xlsx being pulled in.

Does that make sense?

Thanks

Léon