asked on
How to preserve Excel values when external reference add-on isn't present
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?
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?
Windows OSMicrosoft ExcelMicrosoft Office
Last Comment
CompProbSolv