Cells in Excel worksheets with custom functions return '#NAME?' instead of calculated value.
Mar 1, 2017 8:16 AM(3863 views)
Is there a way to tell JMP not to recalcualte an excel sheet when it opens, or to ensure that the calculated values are available before saving the excel file?
A common problem for users who import data into excel via a third-party add-in is an apparrent inconsistency in whether the calcualted values are correctly imported into JMP. Sometimes the values are imported as expected, and sometimes the data table is filled with '#NAME?', which is the same error Excel returns if the Add-in is disabled. I assume that the Excel file is being re-calculated when JMP opens it, and at that time the add-in is not avaialble or is not fast enough.
I was able to replicate this problem by making a custom vba function in an add-in that returns a value stored in code. Add a few cells that reference that function and save the sheet, then open it in JMP. Unload and reload the excel add-in and save the sheet. One day it opens fine and a week later it returns #NAME? instead.
JMP uses a 3rd party engine to read the contents of the Excel file. #NAME can be returned in cases where there is a formula that the engine does not know how to interpret, usually one containing functions that were introduced more recently in Excel. References that span multiple worksheets can also cause an issue.
Both JMP 12 and 13 improved upon this and made this issue less likely to occur. What version of JMP are you using?