topic Re: Open XLS table that contains cell formula's in Discussions

Open XLS table that contains cell formula's

Niklotus — Sat, 10 Jun 2023 23:50:15 GMT

Hi, I have an excel sheet which contains cells with values, strings, and formula's. JMP imports the cells with values, strings, and simple formula's correctly. However, if the cell has a complicated formula (=SUBSTITUTE(MID(FORMULATEXT(Z29), 2, LEN(FORMULATEXT(Z29))-1),MID(FORMULATEXT(Z29),9,3),"x")) JMP replaces that cell with "#NAME?". It appears to not understand part of the formula, even though it works perfectly in Excel. Is there a way to correct the JMP import, without rewriting all of my XLS functions?

Re: Open XLS table that contains cell formula's

jthi — Thu, 23 Jun 2022 17:53:42 GMT

Which version of JMP are you using? I remember that at least with some older versions of JMP (before JMP16) couldn't convert some formulas. Not sure if it will help in this case though, but it is worth trying out. was solved by using newer version of JMP.

If that won't work out I think you might have to save the excel in different format which would change the formulas to values. You might even be able to automate this with JSL with scripting using Run Program().

Re: Open XLS table that contains cell formula's

Niklotus — Thu, 23 Jun 2022 18:12:40 GMT

I'm on JMP 16. The way our system currently works, its not feasible/efficient to manually open and save the XLS sheet as some other format. If there is a way to have JSL do that, I would like to see it. But I think you need to open the worksheet in JMP, and then save it. And that obviously will run into the same issue.

I may be able to make an XLS macro that will save a duplicate copy of that worksheet as a csv. Not optimal, but it may be a workable solution.

Re: Open XLS table that contains cell formula's

ih — Thu, 23 Jun 2022 18:30:52 GMT

If you just make a copy of the sheet and copy/paste as values (be sure to have correct formatting set first) it should import okay, and that should be easy enough to script with VBA. You could also use R or python to open the excel file from within JMP, or if working interactively the JMP add-in for excel doesn't have the same issue.

Re: Open XLS table that contains cell formula's

Niklotus — Fri, 24 Jun 2022 14:54:16 GMT

I was able to upgrade to JMP 16.2, and that fixed the problem. Thanks jthi