Calc: recall cells in other sheets (of the same document)

English
, , ,
#1

Dear AskLibO,

I need to refer a formula to some cells located in other sheets of the document.

In details, I want to fill a cell with the following sum:

C33 (of Sheet1) + C32 (of Sheet2) + C33 (of Sheet 3)

What’s the correct syntax?

I tried: =SUM(Sheet1.C33:Sheet2.C32:Sheet3.C33) but the result shown is an obscure: #NAME?

Thanks for the support!

p.s. Please consider that I’m using the Italian version of LibreOffice, so I wrote, for example, Foglio1.C33 instead of Sheet1.C33, SOMMA instead of SUM etc, and the result shown is, in fact, #SOMMA?. But I don’t think this changes the terms of the question.

#2

The issue is:
=SUM(Sheet1.C33:Sheet2.C32:Sheet3.C33)
has a not well formed range address, try with:
=SUM(Sheet1.C33:Sheet3.C33)

Like in the same sheet a range address is defined only with the begin and the end of the range.
You can add more ranges with the separator.

=SUM(Sheet1.C33:Sheet3.C33;Sheet1.C45:Sheet3.C45)

#3

@mariosv: I have to sum the values of three single cells, each one located in one different sheet. The relative location of the cell in the Sheet 2, C32, is different from the other referred to Sheet 1 and 3, that is C33, so I can’t use the concept of ranges.

If I try something like =SUM(C25:C28) or =SUM(C21;C25;C29), i.e. with cells in the same sheet, all works; but when I add the “Sheet” word to refer to cells in different sheets, the ‘result’ is an ugly “#NAME?”.

What should I do?

#4

@michele.bartolettistella - If I understand you well you want to add only 1 cell per sheet. In this case I would use the formula

=SUM(Sheet1.D9,Sheet2.E5,Sheet3.E3)

Please note that I use a comma separator. You might need to replace the comma with your separator.

#5

When I started the formula on Sheet4 (had =sum( keyed in), then switched to Sheet1 to click on one cell, typed a comma, switched to Sheet2, clicked on a cell, typed a comma, switched to sheet3, clicked on a cell, typed ) to close the “sum” function, and switched back to Sheet4 (where I am entering the equation) and clicked the checkmark, it properly added the three cells using the equation,
=SUM(Sheet1.B3,Sheet2.B3,Sheet3.B6)

#6

@Mark12547 - this is exactly the way I created the formula. Only exception: instead of clicking the checkmark I just hit Enter.
AS you mention Sheet 4 as the location of the formula, I want to point out the formula you and created require the formula being NOT on sheet1, 2, 3
When the formula needs to be placed on sheet 1 or 2 or 3, the sheet where the formula is located must be removed from the formula.

#7

The formula of @mariosv is also correct and possible but it needs to be understood that in his formula the values of cells sheet2 c33 and sheet2 45 is also included in the sum.

A very interesting approach! Thanks @marrosv for this hint.

#8

Ok, I took advantage of all your suggestions and solved my issue, in the end.

:slight_smile:

I understood what was wrong when I tried Mark12547’s procedure (to start the formula, then to switch to each Sheet and to click every single cell I wanted to sum). Doing so, I could see how Calc was forming the formula code, and discovered that the main problem was that I had renamed my Sheets (one of them is called May 2013, for example). So, in the formula I had to replace the word Sheet1 with ‘May 2013’.

Sheet1.C33 ---> 'May 2013'.C33

Please note that if the name has been changed, I have to write ‘May 2013’ with apostrophes, while if I have the original name I can omit them: May 2013.

In fact, if I omit the apostrophes in May 2013, I will receive Err:508; on the other hand, if I write ‘Sheet1’ with apostrophes and hit Enter, Calc will remove them and give me the correct result.

Definitively:

=SUM('NameOfTheSheet1'.C33;'NameOfTheSheet2'.C32;'NameOfTheSheet3'.C33)

is the correct way to obtain the sum (in my experience, at least).

A last remark about the separators:

two point :

is used when I want to sum a range of cells (also across different Sheets, as originally written by mariosv): =SUM(Sheet1.C33:Sheet3.C33) will include also Sheet2.C33. While I have to use a

semicolon ;

if I want to consider single cells.

comma ,

is wrong: I receive Err:501.

Bye!

#9

I have renamed sheets, but I don’t use spaces in mine. To copy a value from bottom of one sheet to the top of the next sheet I use a formula like:

=Apr2013.G64

to copy the value into cell G2 of the May2013 page.

(Too many years of being a programmer in languages that don’t accept spaces got me used to not using spaces in names, including sheet names.)

#10

Im’ in agreement, I’m not in the habit of using spaces often, too, when I have to rename something. But this is mostly with file names; I didn’t thought it would have caused me troubles within LibreOffice. It’s true that, under some points of view, Calc is a sort of machine language.

#11

Hello
just wanted to add some interesting content. I’ve landed on this question since one invoices calc “program” that ever worked, after a recent update, didn’t worked anymore.

The invoice items (prodotti) lines were using this formula

=SE(VAL.VUOTO(C23);" ";CERCA.VERT(C23;Prodotti;CONFRONTA("Price";$Prodotti.$A$1:$AMJ$1;0);FALSO()))

I’m sorry, but the sheet is made with LibreOffice Italiano

SE = IF


VAL.VUOTO = Empty (vuoto) Value (val) (returns true if that cell is empty)


CERCA.VERT = Vertical Lookup (I don’t know the English version, maybe it is VLOOKUP)


CONFRONTA = Compare


FALSO = False

in the Prodotti sheet, there are just 4 columns, which the headers were: Description, Price, Discount %, VAT

Well “VAT” was working while “Price” and “Discount %” were not anymore.

The current question topic on ask.libreoffice.org, gave me the inspiration to turn the columns names to UPPERCASE and it came back to work again O_o??

As first step, thank to this topic/question, I’ve noticed that the issue was fixed by replacing the headers names with their corresponding cell coordinates e.g. instead of “Prices” I could have use Prodotti.B1

=SE(VAL.VUOTO(C23);" ";CERCA.VERT(C23;Prodotti;CONFRONTA(Prodotti.B1;$Prodotti.$A$1:$AMJ$1;0);FALSO()))

But that was tricky, since trying to select and drag the fixed cells, the Prodotti.B1, would have become Prodotti.B2, Prodotti.B3 etc

Is that the point where I had the inspiration and so

I ended up with editing the 4 columns names from
Description, Price, Discount%, VAT

to
DESCR, PRICE, DISC, VAT

It works

Cheers

Robert @ Recupero Dati Hard Disk 299

#12

Hi @RecuperoDati, there is an option to see the functions in English independently what is the locale.
Menu/Tools/Options/LibreOffice/Formulas/Formula Options - Use English function names.

#13

I have just done a try and MATCH() formula works for me without change to upper case, please can you share a sample file (with minimal data) to see the issue.

#14

Hello to all of you, I’m experiencing a strange issue… :frowning: … it doesn’t work anymore, The day I’ve written this answer, I was working on the document but it was not saved yet.
It was looking fine, so I’ve written this answer and happy like a carnival I’ve saved, closed the document and the browser.
Today I’ve re open that document and it is not working again.
It works only writing the sheet-name.head-cell, so like …CONFRONTA(Prodotti.B1;$Prodotti… - How to share a sample?

#15

It doesn’t work neither using the English formulas, I mean, if I do use

=IF(ISBLANK(C21);" ";VLOOKUP(C21;Prodotti;MATCH(Prodotti.B1;$Prodotti.$A$1:$AMJ$1;0);FALSE()))

it works, but if I do substitute Prodotti.B1 with "PREZ" it does return #N/A

#16

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.