Selecting a cell on a different sheet in a formula

[mpk620]

I'm writing pretty simple formulas in a Calc document with 4 sheets. Formulas as simple as: =sheet1.c4-a25

My question is that when I'm creating this formula, I press "=" and then select a new sheet by clicking the tab at the bottom. When that sheet opens if I just click a cell to select it, nothing happens. The formula bar at the top just says "=" and no reference cell is created. I have to click in the formula bar so the cursor is in it and it is "active" again. Then I can click a cell and it will reference it in the formula. Is there a way to switch sheets where the cursor stays active in the formula bar so I don't have to click in it each time I do this? I simply want to hit equal, switch the sheet and select a cell.

[RusselB]

What you are describing as your actions is exactly what we recommend. Since you are having difficulties, I'm going to recommend that you reset your user profile as that seems to resolve a lot of very different problems.

[mpk620]

thank you for the reply, but that did not resolve the issue.

[RoryOF]

FJCC gives instructions here
viewtopic.php?f=9&t=78723&p=360959
I think what you are missing is not pressing Enter after typing the "=".

[mpk620]

this is not resolving the issue, either. and actually, to simplify slightly, i don't even have to be doing anything other than wanting to reference a cell on another sheet. let's just say i want this new cell to simply pull the value from a cell on a different sheet.

1) i click on a blank cell where i want to enter a formula.
2) i press =
3) i do *not* press enter
4) i click on the tab of the sheet i want to pull the value of a cell from
5) i click on the cell in the "new" sheet that i want to pull the value from

at this point in the process, it should select the new cell and pull the reference point (sheet.cell) into my formula, so it would read: =sheet.cell
but this is where the failure is. when i select that new cell, it shows a red box around it like it IS pulling the value, only my formula still simply reads: =

as best i can tell, the act of clicking on a sheet to select it is somehow making the cell i'm currently editing not the current/active cell any longer.

[RoryOF]

Put single quotes about the source cell address. See the last line in the posting of the above URL and the last post in that thread.

[mpk620]

nope. also not it as the source cell never even appears for me to put quotes around - i am not typing the name of the source cell, but trying to click to select it.

although i have found something interesting. this is only happening with cells in the body of the table i created an am working in. i randomly clicked on a cell outside that to test the above and it worked fine - but still didn't when i went back to the cell i'm actually trying to use the formula in. so i think it's a formatting issue with the cell(s), but i have no idea what it might be...

[RusselB]

Would you be willing to upload/attach the file? Note that there is a 128k maximum size.
If you are willing to do this, then we can work with your actual document, as every idea I've had has been mentioned and you have stated that it hasn't worked.
If the document contains confidential information, then please anonymize the confidential data before uploading it.
If the file won't fit due to the size restrictions, a smaller sample file that presents the same problem should suffice or you can upload it to any one of a number of file sharing sites.

[acknak]

... when i select that new cell, it shows a red box around it like it IS pulling the value, only my formula still simply reads: =

Yeah, this is not right. I think the steps you described are fine and normally the cell in the other sheet should appear in the formula (toolbar above the cell grid) as you said.

I would first try shutting down OO and restarting. Try with a new, empty spreadsheet as well.

If that doesn't help, you might try a fresh OO settings profile. This just means exiting OO, renaming (or removing) the folder where OO stores all its settings, then restarting OO--it will then create a new settings profile folder.

For details, see: [Tutorial] The OOo user profile

[mpk620]

ok, i've attached a version of the file where i took out all my actual details and just did a quick rename to something that might approximately make sense - but it might not, which ultimately shouldn't matter.

bottom line is that i'm trying to use the first tab (overview) as the bird's eye view and pull details in from other sheets which have more specifics on them. i deleted all but one of the "detail" sheets to cut the file size down. it still replicates the issue. where i get a problem is trying to enter a new formula into overview.d4, for example. there's an existing formula in there, for example and you can simply try to replicate that one. i hit = and when i shift to the accounts tab to select a cell, i get the problem described above.

i'm fairly certain this is some sort of formatting error as i've found the problem seems to only be occurring in the d column of the overview tab. which figures as that's the only one i ever use to pull in values from another sheet. what's crazy is that if you completely delete the c and d columns (so that the e and f columns then become c and d), you're deleting the problem cells but the NEW d column still has the same issue.

[gerard24]

I can reproduce also with LO 4.4.5.

I don't know why the column D only, but the problem seems to be related to the frozen columns.
If I remove this with Window > Freeze, It worls. If I froze again A and B, the bug appears again.

Edit: Seems to be an old bug : Issue for LibreOffice : https://bugs.documentfoundation.org/sho ... i?id=47349 for OpenOffice : https://bz.apache.org/ooo/show_bug.cgi?id=94192

[acknak]

I see the same problem and the same workaround (window > freeze: OFF).

Another way around: instead of starting where you want the formula, start at the source sheet.cell, copy, switch to the target sheet.cell and Edit > Paste Special (Ctrl+Shift+V) and enable option > link: ON. This pastes a normal formula referencing the copied cell.

You can do the same using drag/drop while holding Ctrl+Shift. Window > New Window allows to have source and target on the screen at the same time.