Subscribe Bookmark RSS Feed

Cells in Excel worksheets with custom functions return '#NAME?' instead of calculated value.

ih

Community Trekker

Joined:

Sep 30, 2016

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.

3 REPLIES
mems35

New Contributor

Joined:

Apr 14, 2017

I also have the same problems. Please let us know how to fix this issue!
briancorcoran

Joined:

Jun 23, 2011

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?

 

Thanks,

 

Brian Corcoran

JMP Development

ih

Community Trekker

Joined:

Sep 30, 2016

These are user defined functions so it makes sense that the third party software would not know how to interpret those cells. Is there any way for the engine to reference custom macros or add-ins?

 

I am using JMP Pro 13 and have the same trouble as in 11 and 12.  It is possible that it happens less frequently in 13; I avoid opening excel files with UDFs in JMP for this reason.