Description: I have .xlsx file with 2 sheets:"Sheet1" and "Sheet2". In "A1" cell of "Sheet1" custom formula "Data Validity" is set. In "D1"-"D10" cells of "Sheet2" 10 values are written. Custom formula "Data Validity" for "A1" in "Sheet1" uses values from "D1"-"D10" cells of Sheet2. This formula performs incorrect results forbidding any values entry. Steps to Reproduce: 1. Open the attached file "Bug2.xlsx" 2. Enter value "hello" in "A1" cell of "Sheet1". This entry will be forbidden. It's the error. Value "hello" must be allowed to entry in "A1" cell. Actual Results: Entry of any values in "A1" cell of "Sheet1" is forbidden. Expected Results: Any values must be allowed to entry in "A1" cell of "Sheet1" excepting values equal to "D1"-"D10" cells of "Sheet2" values. Reproducible: Always User Profile Reset: Yes Additional Info: Version: 7.5.2.2 (X86_64) / LibreOffice Community Build ID: 53bb9681a964705cf672590721dbc85eb4d0c3a2 CPU threads: 12; OS: Linux 5.4; UI render: default; VCL: gtk3 Locale: ru-RU (ru_RU.UTF-8); UI: en-US Calc: threaded
Created attachment 187005 [details] An example of file with the error.
I think you can't do it in that way. Using the cell address to calculate. Because of the new value, it's not in the cell before it is accepted.
(In reply to Konstantin from comment #1) > Created attachment 187005 [details] > An example of file with the error. This document is (based on its metadata) generated by "openpyxl", and MS Excel reports an error when opening it: > We found a problem with some content in 'Bug2.xlsx'. Do you want us to recover > as much as we can? If you trust the source of this workbook, click Yes. and then, if confirmed: > Excel was able to open the file by repairing or removing the unreadable content. > Removed feature: Data validation from /xl/worksheets/sheet1.xml part NOTOURBUG.