troubleshooting Question

How to preserve Excel values when external reference add-on isn't present

Avatar of CompProbSolv
CompProbSolvFlag for United States of America asked on
Windows OSMicrosoft ExcelMicrosoft Office
7 Comments1 Solution71 ViewsLast Modified:
I have a client who uses the F9 Excel add-on to pull data from their Sage BusinessWorks software into a spreadsheet.  As normally used, it has no significant problems.

The issue came where the client wants to be able to send the spreadsheet to someone who doesn't have F9.  The goal is to keep the data in the external formulas (the ones that use the features of F9) static, but allow the normal Excel functions (such as @sum) to work.

I looked into how to keep values only for external references and found the solution of breaking the links.  That works well on the computer that has F9 installed.  The data with F9 references doesn't change, but the user can manipulate other data or formulas and they work as expected.

The problem arises when the spreadsheet is opened on a computer that doesn't have F9 installed.  When opened, the cells look as one would expect.  The formulas still reference F9, but the calculated results are the same as they were on the computer that had F9 installed.

The message appears near the top about Protected View.  If I click on Enable Editing, all of the cells with F9 references show #NAME? and there's a message "The formula includes unrecognized text" and the values are lost.

If I disable Protected View, the #NAME? issue occurs as soon as I open the file.

I'm using Excel 2013 on Windows 10, but I believe the client is using 2010 on Windows 7.

The way the cells are structured, it would be very complicated to copy values from the cells with external F9 references while keeping the local formulas intact.

I just noticed that the spreadsheet is in XLS format.  I can try tomorrow to save it on XLSX format on the computer with F9, but I'm not really expecting that to change anything.

Is there any straightforward way to preserve the values in the cells that have external references to F9?
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros