dking_wri
asked on
Why does Excel 2010 returns #Value error for cells referencing unopened linked spreadsheets.
Hello,
My office is pilot testing a conversion to Office 2010 and I've noticed an issue in Excel that wasn't present in our 2007 installation. I'm getting #Value errors in any cell that references an unopened linked spreadsheet. I can open the linked spreadsheet to resolve the issue but this isn't the most desireable solution.
It's odd that we didn't have this issue in 2007. Co-workers not in the test group are still running Excel 2007 and they can open the same file without the #value errors. I do have "Enable automatic update for all workbook links" checked and I realize this adds to the problem but I'm hoping there is another setting that we can modify to get back to the 2007 functionality. Any ideas?
Thanks!
David
My office is pilot testing a conversion to Office 2010 and I've noticed an issue in Excel that wasn't present in our 2007 installation. I'm getting #Value errors in any cell that references an unopened linked spreadsheet. I can open the linked spreadsheet to resolve the issue but this isn't the most desireable solution.
It's odd that we didn't have this issue in 2007. Co-workers not in the test group are still running Excel 2007 and they can open the same file without the #value errors. I do have "Enable automatic update for all workbook links" checked and I realize this adds to the problem but I'm hoping there is another setting that we can modify to get back to the 2007 functionality. Any ideas?
Thanks!
David
When you say "links" are there formulas involved. Can you provide a sample?
regards, barry
regards, barry
ASKER
Remigrant - Since the file extension (.xlsx) does not change between 2007 and 2010 there is really no migration of the spreadsheets. I just have Office 2010 installed on my machine and others not in the test group have 2007 installed on theirs. You are correct that 2007 works with no #values error. It's just when I open the same file in 2010 that I get the error.
barryhoudini - I should have mentioned that I'm using sumif() functions in my linked data. I've seen articles from Microsoft that indicated this #value error can occur with the sumif() and goes back to version 2003. But the puzzlling part is that we've not seen the #value until 2010. See examples below:
=SUMIF('[testfile.xlsx]Ind ustrial'!$ C:$C,A296, '[testfile .xlsx]Indu strial'!$N :$N) - when linked file is open
=SUMIF('L:\Acct\[testfile. xlsx]Indus trial'!$C: $C,A296,'L :\Acct\[te stfile.xls x]Industri al'!$N:$N) - when linked file is closed
Thanks,
David
barryhoudini - I should have mentioned that I'm using sumif() functions in my linked data. I've seen articles from Microsoft that indicated this #value error can occur with the sumif() and goes back to version 2003. But the puzzlling part is that we've not seen the #value until 2010. See examples below:
=SUMIF('[testfile.xlsx]Ind
=SUMIF('L:\Acct\[testfile.
Thanks,
David
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Barry,
That's what I understood from Microsoft documentation but I can login to a separate server that still has 2007 installed and recalc the same worksheet without error. That's the piece that leaves me puzzled.
Thanks for your help.
That's what I understood from Microsoft documentation but I can login to a separate server that still has 2007 installed and recalc the same worksheet without error. That's the piece that leaves me puzzled.
Thanks for your help.
Yes, I got that wrong.
A normal calculation with F9 doesn't change the figure but a full recalculation (CTRL+ALT+F9) will give you a #VALUE! error for SUMIF formulas when the source workbook is closed. The important point is that the figures can't update unless they are changed with both workbooks open.
regards, barry
A normal calculation with F9 doesn't change the figure but a full recalculation (CTRL+ALT+F9) will give you a #VALUE! error for SUMIF formulas when the source workbook is closed. The important point is that the figures can't update unless they are changed with both workbooks open.
regards, barry
can you describe how the test area has been setup and the spreadsheets 'migrated'
Reg