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
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.
Created attachment 195048 [details] Protected sheet with blank password incorrectly showing formula in A1
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.
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
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.
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.