REF issue when linking Workbooks but only in certain tabs

[DonRaul]

Hi

I have 2 source workbooks being created by a system, alongside 2 templates, A and B. A reads directly from the source file 1, with some additional basic calculations. B reads some data from the source file 2 and the results of the calcs in A.

My issue comes with Workbook A, which has 3 tabs that has formulas of = the same cell in the source workbook for 6000 lines, Columns A to K. This works fine for the first tab, but the other 2 tabs are filled with #REF !, and I receive an error when opening of 'Unable to Read File'. This then effects the formulas in Workbook B which are trying to read this data using a SUMPRODUCT (I changed from SUMIF as apparently that doesn't work with closed workbooks).

I can resolve many of the errors in A by clicking the cells and dragging down the formulas again, but this doesn't work in Column A for some reason. The errors are also resolved if I open the connected source file.

Does anyone have any ideas of what may be causing this issue, and how I can resolve it? I am using Excel professional 2010

Thanks