Good afternoon

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

An example of one such cell (say, 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 increment the 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

So essentially change 6 to 7, 7 to 8, and so on.

I could move each of the files into a single folder if it is easier to just increment the filename. - that is no problem at all.

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

Thanks

Leon

Using INDIRECT with closed workbooks doesn't allow updating. It is better to use VBA to add the formulas:

Sub TestMacro()
For i = 6 To 8 ' and so on
Cells(i + 1, "D").Formula = "'C:\Users\leonj_000\Documents\Dissertation - " & _
    "INSM025\FOI\FOI Responses\" & i & "\[" & i & ".xlsx](1) Project Management'!$C$3"
Next i
End Sub

Hi leonjudge,

Please refer to the reply of Bernie Deitrick, Excel MVP 2000-2010, you can use this code to help you solve your issue.

If you need more help with code, you can post your issue in MSDN forum for Excel.

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

George Zhao
TechNet Community Support