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?
ASKER
Thank you... I've not tested this yet but it may just work.
Any luck in your testing?
ASKER
The client hasn't been available so far. I should have an opportunity tomorrow, though.
ASKER
I looked more carefully at the link and don't think it will be useful in this case. It would be very easy for me to identify the cells with the F9 functions (they all start with the same string), which is about all that the software in the link would do.
I'll likely just write something to copy the entire workbook to a new workbook, but have it watch for the F9-identifying string. If it finds it, just copy the value. Otherwise, copy the cell exactly.
I'll likely just write something to copy the entire workbook to a new workbook, but have it watch for the F9-identifying string. If it finds it, just copy the value. Otherwise, copy the cell exactly.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks for your help. I'll have to write some VBA code to do this, but it should be fairly straightforward.
You could use that code and modify it so that if a formula is found to a be UDF (or non Excel formula) then have cell's formula be replaced with the value contained therein.
You'd run this code and then save a copy of the file (i.e. the one that has values where the F9 fomulae used to be) and send this copy to a recipient that doesn't have F9 on their computer.