[Solved] Cannot Reference a Cell in Another Sheet

[lfriii]

OpenOffice Calc will not let me reference a cell in a different sheet in the same file in certain cells of the original sheet. Many other cells on the same sheet as the cell in question contain a references to a cells in the second sheet, but the cell in question will not reference the cell in the second sheet. To create the reference I press the = sign while in the original cell, click to the other sheet, click on the cell I want to reference, and then press enter key. All I get in the original cell is the = sign. There are a few cells in this sheet that have this problem. Most cells work fine. Can anyone help?

[RusselB]

Your best bet for decent assistance is to help us identify the actual problem.
In this case, as with many, the easiest way to help us identify the actual problem is best done by having you upload/attach a copy of your spreadsheet.
There is an Upload attachment tab under the Post Reply screen when using PostReply, not QuickReply,
Note that there is a 128k file size limit.

If you can't do that, then my next suggestion would be to try resetting your Open Office user profile, as that seems to cure most problems that seem to have no relation to anything else.

[morchat]

Make sure you see the = sign in the input line when you go to the second sheet.
  If not, it means that this character has been treated as a text entry.
The original cell can have a space. If you enter a formula after double-clicking a cell, the = character is inserted after a space, and that means the entire entry is text.

[lfriii]

I updated my user profile but it did not help. So I have attached the file as you suggested.

The cell in question is on the Calculations sheet B8 and I am trying to set it equal to cell E7 on the Input Sheet. For some reason, it will not allow me to do this. All of the other cells on the Calculations sheet that only contain the equal sign (=) have the same issue. Note that cell c15 on the Calculations sheet had the same issue but I copied the reference from another cell and it now works. Any assistance will be appreciated.

[morchat]

It seems that if you freeze the columns, you can not type the formula into the first two after the frozen, pointing the cell from another sheet.
Unlock column A

[Zizi64]

Attachments

X7RL-2C-95.5-D-15.0-3.0.xls
(63 KiB) Downloaded 4 times

OpenOffice version 3.2.1, Operating System; Windows XP Pro, Version 2002, SP3

Some general hints:
- always use the native, International Standard ODF fileformats (.ods, in this case)
- never use dots in the filename (except the extension separator)
- never use space or other special characters in the name of a Sheet
- the OpenOffice 3.2.1 is a very old version. Ugrade to the newest AOO 4.1.3 or the latest Still version of the LibreOffice (and/or update your signature in this Forum)

[Villeroy]

I manually entered ='Input Sheet'.E7
for some reason I don't understand pointing to the source cell does not work here.

[morchat]

In AOO and LO, cell freeze deactivates the ability to point cells from other sheets in the first two columns outside the column freeze.
AOO uncheck "Window - Freeze"
LO change "View -Freeze Cells - Freeze Cells and Columns"

[lfriii]

I unfroze the sheet and it now works. I have not experienced this issue in the past.

Thanks,

[Alan G]

Sorry could not find any reference to this within the past 24 months.

I would like to be able to import data from other Workbooks on my pc in the same way that data can be linked from Sheet to Sheet within a Workbook.

I can access such files using " Insert / Sheet from File " but wonder if there is an alternative to recreating the link each time I wish to update information ?

Any help would be much appreciated !

[morchat]

Yes, it is possible. The general record is as follows:
='file:///Z:/bet.ods'#$Sheet1.C2
Copythe source cell and paste special with option Link

This link will be inserted as a CSE formula.
Second way: Open two documents side by side. Build a formula by pointing the cell from the second sheet.
This formula will be an ordinary formula.

170922200826_3.jpg (4.98 KiB) Viewed 8167 times

[jimmy_521]

Re: Paste Special function, Open Office, ver 4.1.3

The familiar Paste Special function that we recall from Excel, is resident within the AOO spreadsheet.

Well, by trial and error, I discovered that to (right-click) Paste Special and checking the Link box doesn't (always) work.

What does work may not use the Paste Special function. Instead, to do so, effectively the procedure is:

1) Set cursor on the cell that is to be PASTED TO;
2) enter an "=" sign (no quotes, however); {as Morchat mentions (above, ref Sept 19, 2017).
3) Go to the cell desired for COPYING FROM, either within the same sheet or different sheet within the workbook;
4) On the cell to copy from, hit the Copy function or (Ctrl + C) of that cell;
5) Go back to the cell that is to be pasted to, and the reference to the cell copied from will appear;
6) Hit ENTER to complete the operation.

Comment → The above procedure will not copy over the format of the first (original) cell. To do that, use this procedure:

1) Go to the cell desired for COPYING FROM, either within the same sheet or different sheet within the workbook;
2) On the cell to copy from, hit the Copy function or (Ctrl + C) of that cell;
3) Set cursor on the cell that is to be PASTED TO;
4) Right-click whilst on that cell and hit Paste Special on the pop-up that appears;
5) Be sure that Numbers, Formulas, Formats and Link are checked,- or check the Paste all box;
6) Hit the OK box within the pop-up or hit the ENTER key to complete the operation.

Should the second procedure not function for you, then revert to the first procedure as per above and format the pasted to cell as desired.