Spreadsheet cell with a range of valid entries

Disclaimer: You are free to reuse it “as-is”, on your own responsibility and without any guarantee whatsoever. No rights can be derived from, successfully or unsuccessfully, completing the steps outlined below.

This is a scenario that tests whether your application is able to validate the value of a spreadsheet cell against a range of allowed values. Typically, this is rendered as a cell with a drop-down list, while the range itself is on another (hidden) sheet.

Steps

Part 1:

  • Create a new spreadsheet
  • Make sure it has at least 2 sheets/tabs in it
  • On the second sheet, enter the values “red”, “green” and “blue” in A1..A3
  • On the first sheet, add a validation rule to cell A1, only allowing the values from the range Sheet2/A1..A3 + allow empty cell
  • Enter/select “red” as value for A1
  • Add the same validation rule to the empty cell A2, do not enter a value

Part 2:

  • Open the spreadsheet in another application
  • Check if the rule is still in place, by entering an incorrect value in cell A2 (e.g. “yellow”, this should produce a warning)

Files

Please follow the file name convention outlined in the faq. Note that the PDF files can be included as a previewing aid, but uploading the ODF files is much more important.

Original

Reopened /modified

Tracker

A list of known issues. Implementations not listed here may or may not work as expected. Results may vary depending on user settings and computer environment. Use at your own risk.

Nr Status Write Read Comment
001 Open LibreOffice 3.5.2 MS-Office 2010 Validation rule is gone / not checked
002 Open LibreOffice 3.5.2 Calligra 2.4 (Win7) Validation rule is not checked
003 Open Apache Open Office 3.4.0 b9586 LibreOffice 3.4.5 Validation rule is gone / not checked
004 Open Apache Open Office 3.4.0 b9586 Gnumeric 1.10.17 Validation rule works, warning
005 Open LibreOffice 3.5.2 Calligra trunk Linux Validation rule not checked

Analysis

scenarios/20120419/cellvalidationrange.txt · Last modified: 2012/04/20 11:48 by michiel
www.ez.nl www.noiv.nl www.opendocsociety.org