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.
I also think this is not a but. - if you don't want a cell's formula to be shown, in what use case would you _also_ have other cells whose job is explicitly and exclusively to expose such formula? - why should a cell's setting propagate to other cells? I think this opens a can of worms. For example, what happens if the contents of cell D1 are then reused elsewhere with a TEXTJOIN() function? And is "#N/A" better than, say, an empty string? I am closing as "not a bug", but if you are not convinced, please feel free to ping the UX/Design team for more opinion.
(In reply to Stéphane Guillou (stragu) from comment #7) > - why should a cell's setting propagate to other cells? I think this opens a > can of worms. For example, what happens if the contents of cell D1 are then > reused elsewhere with a TEXTJOIN() function? And is "#N/A" better than, say, > an empty string? An empty string seems better than "#N/A", which is the same output as shown in the formula bar.
(In reply to Óvári from comment #8) > (In reply to Stéphane Guillou (stragu) from comment #7) > > - why should a cell's setting propagate to other cells? I think this opens a > > can of worms. For example, what happens if the contents of cell D1 are then > > reused elsewhere with a TEXTJOIN() function? And is "#N/A" better than, say, > > an empty string? > > An empty string seems better than "#N/A", which is the same output as shown > in the formula bar. I was wrong, #N/A is better than an empty string as the value is unavailable (Not Available). Additionally, this is the behavior of other spreadsheet programs. Please advise if you require a screenshot of the other spreadsheet program. (In reply to Stéphane Guillou (stragu) from comment #7) > I am closing as "not a bug", but if you are not convinced, please feel free > to ping the UX/Design team for more opinion. Adding keyword 'needsUXEval' and CC 'libreoffice-ux-advise@lists.freedesktop.org' for input from UX. Not sure what the convention with the status is at LibreOffice. Please change the status if needed. Thank you
(In reply to Óvári from comment #9) > Additionally, this is the behavior of other spreadsheet programs. Tested with MSO365 and a cell with =formulatext(a1) shows the formula with protection enabled. Both Calc and Excel hides the formula "=formulatext()" or "=formula()" from the formula bar if the hide option is enabled. (In reply to Stéphane Guillou (stragu) from comment #7) > - if you don't want a cell's formula to be shown, in what use case would you > _also_ have other cells whose job is explicitly and exclusively to expose > such formula? This needs to be answered.
(In reply to Heiko Tietze from comment #10) > (In reply to Óvári from comment #9) > > Additionally, this is the behavior of other spreadsheet programs. > Tested with MSO365 and a cell with =formulatext(a1) shows the formula with > protection enabled. Both Calc and Excel hides the formula "=formulatext()" > or "=formula()" from the formula bar if the hide option is enabled. > > (In reply to Stéphane Guillou (stragu) from comment #7) > > - if you don't want a cell's formula to be shown, in what use case would you > > _also_ have other cells whose job is explicitly and exclusively to expose > > such formula? > This needs to be answered. 1. A protected spreadsheet, with hidden formulae, is provided to an end-user. 2. It is expected that any hidden formula remains hidden and should not be seen by the end-user. 3. An end-user can easily reverse engineer a formula in LibreOffice Calc as the FORMULA() exposes the hidden formula. Excel 2021 LTSC stops this happening with #N/A error.
(In reply to Óvári from comment #11) > 1. A protected spreadsheet, with hidden formulae, is provided to an end-user... But if the sheet designer wants to hide a formula s/he would not explicitly expose it with =FORMULA(). It mean you want to show the calculation - inside the sheet. The hide option does correctly affect the formula bar and something like A1=1+2 / B1=FORMULA(A1) would not be shown in the formula bar but in the sheet for B1.
(In reply to Óvári from comment #11) > 2. It is expected that any hidden formula remains hidden and should not be > seen by the end-user. That's a false assumption anyway, any formula can be easily seen in the XML stream of the document, be it .xlsx OOXML or .ods ODF. Hidden formulas are _not_ a security feature, it's just UI obscurity.