Hi
I think that the problem is that the autocomplete has no logical way to evaluate whether or not your sheet reference should be changed, so you need to assist with this. In the template for the different workbook, if you make a listing of the sheet names from the original workbook (eg in Cells A1 to A12, reading 1st, 2nd etc), we can then build a series of formulae in e.g. cells B1 to B12 using the INDIRECT() function. If you type the values 28 to 31 respectively into sheets 1st to 4th of book July.xls at cell I21, and ensure that this workbook is left open.
Then in the new workbook enter the following formula in Cell B1 of the new workbook:
=INDIRECT("'[July.xls]"&A1&"'!$I$21") which will return 28.
Copying down B2 to B4 will return 29, 30 and 31.
HTH
Bookmarks