[Webby29]

I have created an Excel worksheet which acts as a summary of a series of workbooks. Using a Macro to import the data creates the links to the underlying workbooks. Once the Macro is run the data is in rows and needs transposing (the links work fine at this point). I use paste special transpose to put the data in the new worksheet and as soon as it is run the data is linking correctly. However, before I can do anything the blue circle appears and Excel changes the cell reference of the linked formulae in the number of rows I have copied.

For example the cell references in the linked workbooks are 'workbook name' $A$1,$B$1-$B$20. If I transpose 5 rows of data the new linked cells are 'workbook name' $A$1,$B$1,$C$1,$D$1,$E$1,$F$1,$B$6-$B$9.

I'm using absolute cell references and if I only transpose the cells B1-B20 this problem doesn't occur. I assume that it is to do with having A1 then B1 and Excel auto-correcting what it sees as a mistake in the formula. Is there anyway to stop this?

Thanks