How to Reference Formulas Between Multiple Sheets

Tags: #<Tag:0x00007efc70568f38> #<Tag:0x00007efc70568dd0>
#1

I followed your examples in your documentation at the link referenced below. I’m able to reference Sheet1 cells from Sheet2 as your example shows. However, I’m unable to do it in reverse - reference Sheet2 cells from Sheet1. The value returns a #REF! error.

In the code below I provide a simplified version of your example. Cell C1 in Sheet1 is trying to retrieve the value from cell A1 from Sheet2. However, it returns a #REF! error.

Interestingly, if I double click on the relevant cell and hit enter afterword, it will return the desired result. It just doesn’t work upon otherwise.

Link:
Formula calculation - JavaScript Data Grid | Handsontable

Code:

const data1 = [
    ['Sheet 1 Info.', 'This does not work', '=Sheet2!A1'],
];

const data2 = [
    ['Sheet 2 Info.', 'This works', '=Sheet1!A1'],
];

const hyperformulaInstance = HyperFormula.buildEmpty({
    licenseKey: 'internal-use-in-handsontable',
});

const container1 = document.querySelector('#example1');
const hot1 = Handsontable(container1, {
    data: data1,
    formulas: {
        engine: hyperformulaInstance,
        sheetName: 'Sheet1',
    },
    licenseKey: 'non-commercial-and-evaluation',
});

const container2 = document.querySelector('#example-basic-multi-sheet-2');
const hot2 = new Handsontable(container2, {
    data: data2,
    formulas: {
        engine: hyperformulaInstance,
    },
    licenseKey: 'non-commercial-and-evaluation',
});
#2

Hi @ptownbro

In this case it’s just a matter of configuration and order of operations. First, you need to add sheetName also to the second table. Then the problem was that the HyperFormula Instance was initialized after the data was declared, and it has to be done before that happens. Finally, you have to add the sheets to the HyperFormula instance.

Here’s a working example: https://jsfiddle.net/handsoncode/thzuw56a/

1 Like
#3

Awesome. That worked perfectly. Thank you!

#4