I’m trying to sum a cell from several sheets in Calc. If I use:
=SUM(SheetA.H101:SheetD.H101)
It works and the math is correct.
The first sheet always has the same name. The last sheet name changes. What I have so far is:
To get the name of the current sheet I found:
=MID(CELL(“filename”),FIND("#$",CELL(“filename”))+2,LEN(CELL(“filename”)) )
To get the value of the cell:
=INDIRECT(CONCATENATE( MID(CELL(“filename”),FIND("#$",CELL(“filename”))+2,LEN(CELL(“filename”))), “.”, “H101” ))
I build SUM() with this:
=SUM(SheetA.H101:INDIRECT(CONCATENATE( MID(CELL(“filename”),FIND("#$",CELL(“filename”))+2,LEN(CELL(“filename”))), “.”, “H101” )) )
There is no error but the value is the one in SheetA.
If I put the INDIRECT in a cell in the current sheet and reference it:
=SUM(SheetA.H101:INDIRECT(“M25”))
Again, no error but this time the math is wrong.
I’m out of ideas.
What am i missing?
Thanks
Please best attach a sample file.
Your problem seems to be - at a first sight - that CELL("filename") always returns a reference to the current sheet name. So all your calculations depend on the sheet, where you put your formulas to. Thus you need to put your sum into the last sheet and your formula needs to be:
=SUM(INDIRECT("SheetA.H101:" & CONCATENATE(MID(CELL("filename"),FIND("#$",CELL("filename"))+2,LEN(CELL("filename"))), ".", "H101")))
See also the following sample file: SumAccrossSheets.ods and see a solution overcoming the “formula must be in last sheet”-requirement of your approach in the answer below.
Hello,
using the following user-defined function:
Function SHEETNAME(iSheetNum as integer)
dim ii as integer
dim iSheetsC as integer
iSheetsC = ThisComponent.Sheets.Count
if iSheetNum <= iSheetsC then
REM --- Arrays start at 0 ---
ii = iSheetNum - 1
SHEETNAME = ThisComponent.Sheets(ii).GetName
else
SHEETNAME = "#N/A"
end if
End Function
you could use the following formula:
=SUM(INDIRECT("SheetA.H101:" & SHEETNAME(SHEETS()) & ".H101")) --or–
=SUM(INDIRECT(SHEETNAME(1) & ".H101:" & SHEETNAME(SHEETS()) & ".H101"))
which would sum all cells H101 in all sheets of your document and which could be located anywhere in your document.
See also a modified versions (contains the user-defined function - macro):
SumAccrossSheetsWithMacro.ods
Tested using LibreOffice:
Version: 6.4.0.3, Build ID: b0a288ab3d2d4774cb44b62f04d5d28733ac6df8
CPU threads: 8; OS: Linux 4.12; UI render: default; VCL: kf5;
Locale: en-US (en_US.UTF-8); UI-Language: en-US, Calc: threaded
Hope that helps.
If the answer helped to solve your problem, please click the check mark (
) next to the answer.
That is exactly what I was looking for. I guess I didn’t understand what was going on in INDIRECT()
Thanks
Your problem wasn’t INDIRECT() but CELL("filename) 