3
I'm using libre calculator. I just want to reference a cell in another worksheet in the same workbook, but I get #NAME? showing up. I know in the past, I've had this problem before, but never figured out how to solve it. Here's what I did.
On sheet1, I have a cellB1 which has the value 1999. On sheet2, I typed into A1 the value ='sheet1'!B1. Then the sheet2 A1 cell shows #NAME?.
What am I doing wrong?
John
Posted 2015-01-01T22:00:30.937
Reputation: 673
3
This is the first I've run into this (recent Excel immigrant and no need yet for multiple sheets in Calc), but I replicated your problem. Instead of typing in the cell reference, I pointed to it to see what would get stored. This is what I got:
=Sheet1.B1
No quotes (which I thought might be your issue). However, apparently, LibreOffice Calc uses a period instead of an exclamation point to delimit the sheet name. I checked the help, and sure enough, that's the format.
The period is the Calc default, but there is a menu setting (it's moved around a bit in different versions), that lets you use Excel address formatting. Currently it's in Tools | Options | LibreOffice Calc | Formula. Under Formula Syntax, choose Excel A1. As in Excel, the exclamation point is used as the delimiter with that setting.
fixer1234
Posted 2015-01-01T22:00:30.937
Reputation: 24 254
0
Ok that's weird. So to fix the problem, I saved the file. Then I closed the file. Then I re-opened the file. Libre Calculator automatically converted my ='sheet1'!B1 to =$Sheet1.B1, and now it shows the value of sheet 1 b1. I have no idea why I have to save and re-open file just to get this to work.
John
Posted 2015-01-01T22:00:30.937
Reputation: 673