I am trying to create a formula in one Calc file (call it “summary.ods”) that references a range of cells across worksheets in another Calc file (call it “source.ods”).
The source.ods workbook contains a bunch of identical worksheets (invoices). I want to find the SUM of the value of cell H54 in every worksheet in source.ods, and have it appear in summary.ods.
I can create a formula that finds the sum of all of the H54 cells in all sheets within source.ods, and I can create a formula in summary.ods that references a single cell in source.ods, but I cannot seem to combine the two into a single formula in summary.ods showing the total of all of the values of H54 in all of the sheets in source.ods. I want to do this with multiple source workbooks, hence trying to find a way to put all of the sums in one place in a single workbook.
The following works to show the sum of all of the H54 cells within source.ods:
=SUM(Sheet1.H54:SheetLast.H54)
The following in summary.ods tells me the value of H54 in the first sheet in source.ods:
=SUM(‘file:///path/to/file/source.ods’#$Sheet1.H54)
I figure the following should give me what I want in summary.ods:
=SUM(‘file:///path/to/file/source.ods’#$Sheet1.H54:‘file:///path/to/file/source.ods’#$SheetLast.H54)
However, instead I just get 0 (zero), which, I can assure you, is not the correct answer.
There must be something simple I’m missing here, and searches here and on the Web don’t produce anything related. Any help would be much appreciated. Thanks.
This is LibreOffice v. 4.2.8.2 under Xubuntu 14.04 (LTS).