Hi,
I know this is a developer forum but I’m getting desperate looking for information.
I am using a Synology Office installation and I believe it is HandsonTable
I have a simple question which I cannot find the answer anywhere!
How do I reference a cell in a different sheet in a formula. Surely this is a basic function of any multi sheet spreadsheet.
All I need is to be pointed in the right direction. Thanks in advance
Handsontable by default doesn’t create multiple sheets. If by the multiple sheets you are talking about different instances of Handsontable you can refer to them calling the other instance data. Here’s an example that uses Bootstrap to create a multiple-sheet look: Bootstrap Tabs - JSFiddle - Code Playground
In the example above I added 3 sheets. In the first cell of the first sheet, coordinates (0, 0) I set 12 and in the Sheet 3 on the cell (1, 1) the value is the sum of the above + 12.
Currently there are two approches you can use two achieve this goal.
hot3.setDataAtCell(1,1, '=SUM(' + hot.getDataAtCell(0,0) +', 12)');hot3.setDataAtCell(1,1, hot.getDataAtCell(0,0) + 12);The difference between this two approaches is that in the first example when you double click the (1, 1) cell in Sheet 3 you’ll get the formula:
and in the second example user will only see the result
What’s important, currently user can’t refer to other sheets via cells formulas. To get it working you would need to create a custom function that takes typed string and parts it and analize (but that I guess is a subject for another forum topic).
If you want to know more, here’s a link to our docs: Formula calculation - JavaScript Data Grid | Handsontable
