Ask Your Question
0

Cross-document formula with cross-worksheet range

asked 2017-07-28 21:30:07 +0100

craigh gravatar image

updated 2017-07-28 21:35:31 +0100

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).

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2017-07-28 23:21:48 +0100

m.a.riosv gravatar image

Looks like a bug, please can you report it on bugzilla

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-07-28 21:30:07 +0100

Seen: 36 times

Last updated: Jul 28 '17