Error in Formulas when adding cells from different worksheet

User ba033e6d58

03-12-2009 21:25:20

I am using 1.1.2 JChem for Excel, MarvinBeans5.2.6, Office 2007, JRE 1.6.14.

See the attached Excel example for reference.

On worksheet 1, I have a structure in cell A2, molecular weight calculated from it in cell B2, and a random number in C2. If I add cell B2 + C2 on the same worksheet (cell E2), the numbers add up just fine.

However, if I go to the second worksheet, I have problems adding cells across worksheets. On worksheet 2, I have another random number in cell A2. In cell C2, I am trying to add A2 (worksheet 2) and B2 (worksheet 1) and I get a value error (data of the wrong type). In cell D2, I can add 2 numbers on different spreadsheets. The difference is that formula with the error is using a value calculated from the structure and the formula that works are 2 simple numbers.

Another issue with formulas between spreadsheets that is hard to clearly show is that when you start in a cell on one worksheet to enter a formula, then toggle to a different worksheet to select the cell to perform an operation on, then return to the original worksheet with the formula the cell you have the formula in is no longer selected (which makes it more difficult to enter formulas based on cells in different worksheets without manually typing in cell references)

Will these issues be fixed in v1.1.3? They are functions that our users need to work with on their project tracking sheets.

ChemAxon bd13b5bd77

03-12-2009 21:59:21

Dear customer,

Unfortunatelly, it is a known issue in our program.

I cannot see to much chance to get it fixed in 1.1.3.


User ba033e6d58

03-12-2009 22:08:17

When would you realistically expect it to be fixed? We currently have both JChem for Excel and ChemDraw for Excel and it will be hard to convince users to switch to Jchem if they can't do what they need to without giving them a fix within a reasonable time frame.

ChemAxon bd13b5bd77

03-12-2009 23:37:23

I think our drawing convention how we put structures on Excel screen is one of the best on the market,
however there are side effects of this like the cross sheet refrence issue you have highlighted.
We are sorry for this, trying to take this on the prioritization list, however the issue seems to very hard to resolve.

Now what I can recommend you is to temporarily please switch off the drawing from JChem toolbar (eye icon on a toggle button), you can set up the cross references freely, and afterwards you can switch the drawing on, now this is what you can do to avoid loosing the cross selection of worksheet cells.

We will get back to you soon with a realistic plan when this can be resolved. 

ChemAxon 0e37943a96

04-12-2009 13:08:25

Dear Jean,

we will try to do our best, to fix this particular issue, especially if you consider it serious. We have a list of issues and improvements for each release, which is now will be based more on customer feedback and request.
Since 1.1.3 will be release next week, we cannot make this fix in that version.

In 1.2 there will be several architectural changes in the rendering (technically speaking, we will move this part of the application to native C++ code). We have ideas how to fix this particular issue, but we do not know how it will affect the other parts of the rendering. As soon as we finished with the planned changes for 1.2 we will work on this. I cannot promise that the fix will be available in 1.2, but if we succeed we will issue a patch release for this in April.

Other option would be that we use simple Excel objects for representing structures, but that way we would loose the performance and comfortability advantages, this rendering solutions offers.

Best regards,
Tamas Pelcz.