Currently, there is the possibility of combining the ranges using concatenation character in some formulas. https://help.libreoffice.org/Calc/Operators_in_Calc#Reference_operators For example, it is possible in the SUM, PRODUCT and AREAS formulas. In my opinion, it would be a good idea to use this functionality in the Source field of Validity function ... (Data → Validity). This can be useful for small businesses and entrepreneurs who lead the database using a Calc, and sale both a raw material and finished products, and leading sales statistics into Calc. For example, small electrical parts shop, which complete a kits of items, and sell the separate details and those kits. In this case, it is possible to assume that the shop has two or more tables, one with a list of the characteristics and the price etc. of details, the second listing of ready kits with their characteristics and price etc, the third will be made to data from the first and second tables (or more). It would be convenient if the Source field could take united a range of names from the first table and the second, for example, = Sheet1.A1:A100~Sheet2.A1:A100, and in the drop-down list could choose any one of the these tables (details and kits). Intended use: The user has two tables or more, and wants in the current table to create a drop-down list for Validity in the cell value in which they will be able to choose one item from their tables. 1. Select the range in cell which will be located drop-down list. 2. Select the Data -> Validity... in menu. 3. Choose Cell Range in Allow list of Validity window. 4. Enter two or more ranges separated by ~ (tilde) in Source 5. Click Ok Since the user can use ranges from one sheet, different sheets, different files and named ranges, it is assumed that the field can take values in the form of: =Rang1~Rang2[~Rang3[~…]] =NamedRang1~NamedRang1[~ NamedRang1[~...]] =Sheet1.Range1~Shet2.Range2[~Sheet3.Range3[~…]] =path/file.Sheet1.Range1~path/file.Sheet1.Range1[~ path/file.Sheet1.Range1[~…]] And combinations thereof, are not hindering the unequivocal understand what range of meaning. A small window Validity.Source caused by using the Shrink button should be able to make reference to the following range, after typing of the sign ~ (tilde), not dropping a reference to the first range.
Sounds good -> NEW
In current implementation of ScValidationData::GetSelectionFromFormula [1], finding proper values creates a ScFormulaCell with validation formula and MM_FORMULA flag (which allows the cell to be a matrix cell). The result value is obtained by the cell's Interpret() and then converted to allowed values list. So, just results that are possible in a cell are possible ATM. Reflists are not proper values of a cell. The error is set in ScInterpreter::Interpret [2] (search for "case svRefList"). 1. sc/source/core/data/validat.cxx 2. sc/source/core/tool/interpr4.cxx