Referencing a Cell in Another Sheet in CALC

[steve.leach.549]

I have a calc file with 201 sheets, the 201st is called Totals. I would like to display the contents of a cell from the Totals sheet … for example, in Sheet 1, I want the contents of cell G1 of the Totals sheet. In Sheet 2, I want the contents of cell G2 in the Totals sheet. The closest I can come up with is >

=Totals.G&“Sheet()”

Haven’t been able to figure it out. Thanks!

[mariosv]

Try with a formula like: =INDIRECT(ADDRESS(SHEET();7;1;1;"Totals");1) It takes the row number from SHEET() which gives the sheet number, ADDRESS() gives the address as text and INDIRECT() returns the value for text address.

[rautamiekka]

Trying my luck: put ='Totals'.G2 to the cell you want something to appear in. That’s how Google converted a spreadsheet from their own format to ODS and it worked just fine when I changed some values.

[karolus]

@rautamiekka: did you read and understand the question ??

[rautamiekka]

@karolus: I could had misunderstood it.

[karolus]

@mariosv :
think twice over stuff with INDIRECT(ADDRESS...

The answer is
=INDIRECT("Totals.G" & SHEET())

[PYS]

I would add that if you want the result to the same position in all the sheets you can select sheets before entering. Steps to Reproduce:

1. Click on the tab of the first sheet
2. Shift + Click on the last sheet tab (before “Totals”)
3. Click in the cell where you want the result and then enter the formula given by mariosv

[steve.leach.549]

Thank you very much - perfect!

[rautamiekka]

Delete this answer !