We help IT Professionals succeed at work.

Why does Excel 2010 returns #Value error for cells referencing unopened linked spreadsheets.

dking_wri
dking_wri asked
on
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
Comment
Watch Question

CERTIFIED EXPERT

Commented:
It works as 2007 for me, the only time I can force a #value error is where the target sheet is not in the expected location

can you describe how the test area has been setup and the spreadsheets 'migrated'

Reg
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
When you say "links" are there formulas involved. Can you provide a sample?

regards, barry

Author

Commented:
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]Industrial'!$C:$C,A296,'[testfile.xlsx]Industrial'!$N:$N)   - when linked file is open

=SUMIF('L:\Acct\[testfile.xlsx]Industrial'!$C:$C,A296,'L:\Acct\[testfile.xlsx]Industrial'!$N:$N)   - when linked file is closed

Thanks,
David
CERTIFIED EXPERT
Most Valuable Expert 2013
Commented:
Yes, SUMIF (and COUNTIF and other ...IF functions...and OFFSET) don't work with closed source workbooks (in any version). It may be that they weren't updated so just showed the original value, but a re-calculation results in an error - you can use SUMPRODUCT instead, e.g. assuming the source is open initially

=SUMPRODUCT(('[testfile.xlsx]Industrial'!$C:$C=A296)+0,'[testfile.xlsx]Industrial'!$N:$N)

although SUMPRODUCT is less efficient than SUMIF and so you should probably restrict the ranges rather than using the whole column, e.g. restrict to 1000 rows

=SUMPRODUCT(('[testfile.xlsx]Industrial'!$C1:$C1000=A296)+0,'[testfile.xlsx]Industrial'!$N1:$N1000)

regards, barry

Author

Commented:
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.
CERTIFIED EXPERT
Most Valuable Expert 2013

Commented:
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