Data validation only allows listed values

[agiga]

In Libre 6.x, I made a cell, which had dropdown with allowed values (which were listed elsewhere in the sheet to be editable). However, it was also possible to enter unlisted value, and the sheet worked with it further.

Now in Libre 24.2., I am unable to restore this functionality. If I want a drop-down list, then the cell only allows values from the list. Writing any value is ignored, if it is not on the drop-down list.

Here’s a simplified example:

B1 is the cell with data validation, using range A3:A8.
I can set B1 by using the dropdown. I can also write “3” or “a” to set the cell.
But writing “4” into the cell won’t change it’s content - in the picture, it’s value will remain 2.

Is there a way I can use dropdown menu, from values that are listed in the sheet (rather than inside data validation menu), but the cell will still allow any other value to be entered?

For completeness sake, here is full options of B1 cell:
Data Validation:

• Criteria:
  • Allow - Cell Range
  • Show selection list - Yes
  • Source: $Sheet1.$A$3:$A$8
• Input Help:
  • Show input help… - No
• Error Alert:
  • Show error… - No
  • Action - Information
  • Title - ‘empty’
  • Error Message - ‘empty’

[Villeroy]

On the third tab of the validation dialog, you find 4 levels:

1. Error: show error message and reject value
2. Warning: show warning message with a choice to accept (default: No).
3. Information: show info message with a choice to accept (default: Yes).
4. Let a macro handle the conflict.

I think, you want #3.

[agiga]

That’s partial solution, if I set error message, I can click “OK” and the value does actually stay.
However, once I uncheck “show error message”, the cell simply cannot be changed to anything not on the list.

I tried Macro, but that shows “no macro chosen”, though it also allows the value to stay in the cell.

Thank you for this solution at least. I so need solution that allows filling B1 without pop-up windows, though.

[Villeroy]

qa108048.ods (15.8 KB)

Call menu:Tools>Options>Security>[Macro Security…] and specify your documents directory as trusted source. Store my file in that directory (or any subdirectory therein) and open it.

[Mitch99]

Villeroy. Thankyou for your post. I have been searching for your answer for many, many hours since switching from Version 7.6.7.2 (where I didn’t have this issue) to a newer Version 24.2.6. Just choose the #3 option (Information) add a short note in the message box provided.
This solution works so easy (if you know that’s what you need to do). Don’t forget to put a check mark in the box to activate this option.

[ady]

See:
tdf#160096

Or, IOW, the patch for tdf#159595 should be reverted.