Search code examples
node.jstypescriptexceljs

ExcelJS cross sheet formula parsing error

I have a title sheet, where I am trying to sum up all the values in Cell E6 for all my other sheets.

Off of this GitHub issue, I have tried this:

spreadsheetExport.worksheets.map((worksheet: Worksheet, index: number) => {
        if (index !== 1) {
            formulaArr.push(`${worksheet.name}!E6`);
        }
    });
    const formula = formulaArr.join(' + ');
    titleSheet.getCell('B3').value = { formula, date1904: false };

these have all led to an error (photo attached) in the export which reads: The formula couldn’t be imported and was replaced by the last calculated value.

If I try to do it manually, I can get this formula enter image description here, but if I copy that same notation, I get the same error The formula couldn’t be imported and was replaced by the last calculated value. Original formula: ='Master Bedroom'::Table 1::E6 + 'Dining Room'::Table 1::E6 + 'Bedroom #1'::Table 1::E6 and if I copy the erroring formula and paste it into the cell it works.

Please help, I have been stuck for days, with no end in sight. I have tried named cells as well but that is not working either.

enter image description here

Solution

  • I was just missing quotes!

    Solution:

    if (index > 0) {
    formulaArr.push(`'${worksheet.name}'!E6`);
}