#VALUE! error returned when linking worksheets Excel 2010

Question

I'm trying to link data between two workbooks using the usual '= then selecting the workbook, worksheet & cell, pressing return' and I'm only getting the correct data in my first row.

All other rows return #VALUE! but there is no error in the formula. I've tried using an older version of excel also but it doesn't work. Our IT expert has also checked this and cannot understand why this is happening. All the formatting is the same for the cells too.

Any help much appreciated, thanks

Answer 1

I've solved this problem; for info the cells in both workbooks were merged and although the first row of cells on each worksheet worked perfectly the subsequent rows didn't. When I removed the selection of cells from the formula and made the link to the first of the cells (in the merged area) the formula worked.

Answer 2

Can you reproduce the error with a pair of blank workbooks ?

Regards.

Daniel

Answer 3

The formula is ='[Schedule 2010 - 2011.xls]September 2010'!$P$6:$P$8 so I think so. This formula works for one row just changing the cell ref, just not the others.

Thanks

Answer 4

If I create two new workbooks there is no problem, however if I paste the info from the original spreadsheets it happens again. Office 2010 has only been installed this week, the spreadsheets I'm trying to link were created in Office 2003 & 2002. I have double-checked the cell formatting and there is no difference between the row that works and the following rows so it seems strange that this is happening.

Answer 5

Do you have it set to absolutes?

=[MENU.xls]Sheet2!$A$1

instead of

=[MENU.xls]Sheet2!$a1

---

Don Guillett MVP Excel SalesAid Software *** Email address is removed for privacy ***