Problem with F4 absolute cell referencing in Calc

English
#1

Using F4 for absolute cell referencing in Calc generally seems to work. However, if I cross refer from one sheet to another and the formula cell is in area of the sheet where rows & columns have been frozen then F4 seems to have no effect.
If it is within the same sheet then the problem does not occur. If I unfreeze the rows and columns in the sheet where the formula is being entered the problem also does not occur.
Is there any way to get it to work for cross-sheet formula , where the relevant rows and columns have been frozen in the sheet you are writing the formula?

Edit update: LibreOffice version 7.4.5.1 using Windows 10.

#2

Works for me, LO 7.5.1.0.0+ Linux. What LibreOffice version and operating system anyway? Copy-paste Version Information from Help → About best editing (three dots, pencil) your question, not as comment.

#3

Let me be very explicit about how to recreate this problem.
Create a new a new Calc spreadsheet.
Add a second sheet to the spreadsheet.
In Sheet 1, click on cell B2, then View → Freeze Rows and Columns
Enter a formula in Sheet 1, Cell B2, for example, “=Sheet2!A1”, then hit F4, the absolute reference is not enabled.
However if you press return to enter the formula, then you can then click on Sheet 1, Cell B2 again, and F4 will now work.
So in this scenario it just adds an extra keystroke or two when entering the formula. If you have a complex formula with several parts some of which require absolute references, it is rather a pain not to be able to use F4 as you are entering these.
However, F4 does work before you hit ‘enter’ in other cases when not switching between sheets which are part frozen.

#4

Works for me with the regular dot (.) separator:

=Sheet2.A1

The (!) is for Excel.

1 Like
#5

You can choose whether to use (.) or (!) in Tools-> Options → LibreOfficeCalc → Formula → Formula Syntax.

It makes no difference which syntax I choose, I still get the F4 not working problem in the precise example I gave earlier.

#6

Can you upload your .ods type sample fle here?

#7

The F4 works for me (in my LO 7.4.4 portable version) - with “Excel A1” setting and (!) separator too - except the “Sheet” part of the formula. That never will be changed between the Sheet2! - $Sheet2!"
(There is not LO 7.4.5 portable yet, and I will not change the permanently installed version for a test.)

#14

Excel does not know relative sheet references.

2 Likes
#8

F4 Test.ods (7.7 KB)
Here is the simple test spreadsheet which illustrates the problem.
To be clear, if I click on Sheet1.CellB2 now then F4 will work. However, the problem is that F4 does not work when you create the contents of this cell (before you click enter).
(Also references within the same sheet (or across sheets but not from areas separated from frozen row or column headings) then F4 does work at the point of entry.)

#9

It works for me if I edit the formula of the Cell B2 to =Sheet3!A2, before I hit Enter. And it works, when is type in a new formula in an other cell of the Sheet1 (for example in Cell C5: =Sheet2!G8)
(I just changed the separator option in my LO 7.4.4 to “Excel A1” (!) )

#10

Yes it works if you type in the reference manually. But if you create the contents of the cell by clicking on the the target cell(s) (as you normally do when constructing a spreadsheet), then F4 doesn’t work.

#11

I see it now.
Same with (.) and (!) separator.
Workaround: use The F4 after you hit entrer.

(I am always typing the desired “$” sign manually into the formula.)

#12

Glad that you now see the problem.
I agree that you can use F4 after you hit enter as a workaround, but when you are constructing complex expressions with multiple terms, this is not a very satisfactory solution.
How can I report this as a bug to be fixed in a future update?

#13

I think it is not a bug. But you report it (or you can send a request for a new / improved feature) on this page:

https://bugs.documentfoundation.org/

#15

There is a bug report
https://bugs.documentfoundation.org/show_bug.cgi?id=142341

#16

That bug is about the cell-range-picker, not arbitrary cell content formula input.

#17

Btw @njbear, please do not use the Answer or Suggest a solution field for comments that are not an answer to the original question / solution to the problem, use Comment instead, or edit your original question to provide further details. Thanks.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.