We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

Calc: recall cells in other sheets (of the same document). [closed]

asked 2013-04-13 10:38:00 +0200

michele.bartolettistella gravatar image

updated 2020-08-03 12:57:27 +0200

Alex Kemp gravatar image

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.

edit retag flag offensive reopen merge delete

Closed for the following reason question is not relevant or outdated by Alex Kemp
close date 2015-11-16 18:39:31.124095

Comments

1

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)

m.a.riosv gravatar imagem.a.riosv ( 2013-04-13 11:18:58 +0200 )edit

@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?

michele.bartolettistella gravatar imagemichele.bartolettistella ( 2013-05-02 10:37:21 +0200 )edit

3 Answers

Sort by » oldest newest most voted
1

answered 2013-05-02 12:18:19 +0200

ROSt52 gravatar image

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

edit flag offensive delete link more

Comments

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)

Mark12547 gravatar imageMark12547 ( 2013-05-02 20:01:15 +0200 )edit

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

ROSt52 gravatar imageROSt52 ( 2013-05-03 03:26:44 +0200 )edit

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.

ROSt52 gravatar imageROSt52 ( 2013-05-03 03:35:55 +0200 )edit
0

answered 2013-05-03 14:21:23 +0200

michele.bartolettistella gravatar image

updated 2013-05-03 14:22:13 +0200

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

:)

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!

edit flag offensive delete link more

Comments

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

Mark12547 gravatar imageMark12547 ( 2013-05-04 07:01:59 +0200 )edit

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.

michele.bartolettistella gravatar imagemichele.bartolettistella ( 2013-05-05 16:46:44 +0200 )edit
0

answered 2013-12-28 08:29:46 +0200

RecuperoDati gravatar image

updated 2013-12-28 09:05:09 +0200

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

edit flag offensive delete link more

Comments

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.

m.a.riosv gravatar imagem.a.riosv ( 2013-12-28 13:02:56 +0200 )edit

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.

m.a.riosv gravatar imagem.a.riosv ( 2013-12-28 13:22:17 +0200 )edit

Hello to all of you, I'm experiencing a strange issue... :-( ... 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?

RecuperoDati gravatar imageRecuperoDati ( 2014-01-03 19:17:40 +0200 )edit

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

RecuperoDati gravatar imageRecuperoDati ( 2014-01-03 19:27:56 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2013-04-13 10:38:00 +0200

Seen: 9,815 times

Last updated: Dec 28 '13