Updating External Links Excel 2000 vs 2002/2003


Heers Muhgoo

I have a situation where I'm using Excel 2000 with a
workbook containing references to another workbook. When
opening the first workbook & the second workbook is not
available, you can say "no" to the update external links,
and still see all values as they were when the first
workbook was last closed.

However, when the same workbook is opened in Excel 2002 or
2003, the external links specified only as a cell reference
show the proper data (e.g, =wbkname!E1), but when they are
Excel formulae (specifically a SUMIF), I'm getting a #VALUE!
error in the pertinent cells

Is there a way to get the values to show without having to
provide my target audience the second workbook, as we are
able to do in Excel 2000.




Dave Peterson

xl2002+ likes to recalculate any workbooks that were created in previous
versions. In earlier versions of excel, if you answer No to the update links
prompt, the existing values are kept. In xl2002+, you get those errors.

Jim Rech posted a registry tweak:
http://groups.google.com/[email protected]

Maybe it'll work ok for you.

Heers Muhgoo

Thanks, Ed.

Thanks for your quick response!

I have a specific requirement in which a client is
explicitly citing the use of Excel 2000. In that version,
our data shows up just fine. I suspect by their error
report, however, that our client has updated to and is now
using Office 2002+.

I don't think we'll be able to send them Jim Rech's registry
hack to resolve the problem. However, in reading your
response, are you implying that if we recreate both
workbooks in 2002+, the problem goes away?


Dave Peterson

I don't use xl2003, so there might be a problem if you create it in xl2002 and
open it in xl2003--but I can't verify that.




I was having the same problem. I just discovered that SUMIFs won't work on
a closed workbook. However, if you change it to a Sum(if array formula, that
does work. See Microsoft Knowledge Base article 26045.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question