Bug 161836

Summary:	FORMULA() incorrectly shows the formula, instead of the #N/A error value, when the sheet is protected and Hide Formula is enabled
Product:	LibreOffice	Reporter:	Óvári <ovari123>
Component:	Calc	Assignee:	Not Assigned <libreoffice-bugs>
Status:	UNCONFIRMED ---
Severity:	normal	CC:	rb.henschel, vsfoote
Priority:	medium
Version:	unspecified
Hardware:	All
OS:	All
See Also:	https://bugs.documentfoundation.org/show_bug.cgi?id=161834
Whiteboard:
Crash report or crash signature:		Regression By:
Bug Depends on:
Bug Blocks:	108827
Attachments:	Protected sheet with blank password incorrectly showing formula in A1 Workaround to view formula when formula is hidden

Description Óvári 2024-06-29 03:25:02 UTC

Description:
FORMULA() should *not* show the FORMULATEXT when the cell protection is enabled in a protected sheet.

Steps to Reproduce:
1. In cell A1 type: =1+3
2. In cell C1 type: =FORMULA(A1)
3. In cell D1 type: =FORMULA(A1)
4. Right-click on D1, Format Cells…, Cell Protection, unselect Protected and unselect Hide Formula
5. Right-click on sheet Sheet1, select Protect Sheet…
6. Click OK (no password) (Sheet 1 is protected)

Actual Results:
1. Click on cell A1, formula bar is empty (correct)
2. Click on cell C1, the value of the cell is "=1+3" (incorrect)
3. Click on cell D1, the value of the cell is "=1+3" (incorrect)

Expected Results:
1. Click on cell A1, formula bar is empty (correct)
2. Click on cell C1, the value of the cell should return the #N/A error value
3. Click on cell D1, the value of the cell should return the #N/A error value


Reproducible: Always


User Profile Reset: No

Additional Info:
FORMULA() should *not* show the value returned by the FORMULATEXT() function when the cell protection is enabled. The FORMULATEXT() function should show the #N/A error value.

Thank you

Comment 1 Óvári 2024-06-29 03:52:42 UTC

From https://support.microsoft.com/en-us/office/formulatext-function-0a786771-54fd-4ae2-96ee-09cda35439c8
> In the following cases, FORMULATEXT returns the #N/A error value:
> * The formula can't be displayed in the worksheet; for example, 
>   due to worksheet protection.

Comment 2 Óvári 2024-06-29 03:54:21 UTC

Created attachment 195048 [details]
Protected sheet with blank password incorrectly showing formula in A1

Comment 3 Regina Henschel 2024-06-29 18:39:03 UTC

The ODF format does not specify such behavior. "Protection" of a table cell only prevents editing the cell by users. 

From 19.700.5 <table:table-cell>, part 3, ODF 1.3
"The table:protected attribute specifies whether a table cell is protected. Users cannot edit the content of a cell that is marked as protected."

"Users cannot edit" does not mean that the cell content cannot be used in other formulas.

BTW, opening the .ods file in "Protected View" in Excel does not show #N/A error but shows =1+3.

Comment 4 Óvári 2024-06-30 09:19:37 UTC

Created attachment 195054 [details]
Workaround to view formula when formula is hidden

(In reply to Regina Henschel from comment #3)
> The ODF format does not specify such behavior. "Protection" of a table cell
> only prevents editing the cell by users. 
> 
> From 19.700.5 <table:table-cell>, part 3, ODF 1.3
> "The table:protected attribute specifies whether a table cell is protected.
> Users cannot edit the content of a cell that is marked as protected."
> 
> "Users cannot edit" does not mean that the cell content cannot be used in
> other formulas.
> 
> BTW, opening the .ods file in "Protected View" in Excel does not show #N/A
> error but shows =1+3.
Sorry for the misunderstanding.

1. No formula shown in formula bar as cell A1 has
(a) "Hide Formula" enabled
(b) "Protect Sheet" enabled with no password
2. D1 shows formula from cell A1.
(a) Is this inconsistent with the behavior of the formula bar when cell A1 is selected?
(b) Should the value in D1 be the #N/A error value?

Thank you

Comment 5 ady 2024-06-30 18:38:03 UTC

FWIW...
(In reply to Óvári from comment #4)

> 2. D1 shows formula from cell A1.
> (a) Is this inconsistent with the behavior of the formula bar when cell A1
> is selected?
> (b) Should the value in D1 be the #N/A error value?

This is an ods file, so it should follow ODF + Calc rules.

If cell D1 should not show its formula or its content, then you should modify the "Cell Protection" properties of cell D1 accordingly. I would add that, in some situations, the cells that are allowed to be modified by users (i.e. not protected) should be also limited by "Validity" (or "Validation") rules.

To be clear, I have not reviewed exactly how this should work (according to the ODF standard).

I have not tested what happens when mixing an ods file pointing to an xlsx file, but that seems to be a different scenario anyway.

Comment 6 Eike Rathke 2024-07-01 12:41:05 UTC

To me this is not a bug. A protected cell is prevented from being edited and showing its formula, it's a UI feature, that's all. Not anything inheriting to whatever might access its formula, be it the FORMULA() function or macros reading the cell content i.e. formula.