Two identical Data Validation workbooks- two different results

English
#1

Using LO 7.3.6.2
validity fail.ods (15.2 KB)
valdity success.ods (11.8 KB)

Attached are two workbooks. I’m trying to identify the essential difference causing one to fail, and the other to succeed. The one called “Validity FAIL” is excerpted from a larger worksheet I’m working on, with all extraneous data deleted. It demonstrates several issues, possibly bugs. The other workbook, “Validity Success”, was recreated from scratch to be practically identical to the “Fail” workbook. It does not suffer the same problems.
The “Fail” workbook has these issues:

  • it appears the following characters in Employer text prevent a match or index: ( ) [ ] \ | and “asterisk”

  • Removing these characters allows index & match

  • Deleting the “Extra” sheet also deletes all defined Data Ranges, although no ranges reference the “Extra” sheet

  • Replacing the range AppConTable with Rates in the Data Validity formula in cells G3:G5 results in no matches, even though the ranges are defined exactly the same.

The “Success” workbook, although it has virtually identical parameters, has none of these problems.

Can anyone help identify why the one demonstrates problems, and the other does not?

#2

imagen

#3

Thanks @LeroyG, for solving the 3rd problem. I forgot that Named Ranges and Data Ranges can conflict. The distinction between them is very fuzzy to laypersons like me. I deleted all Named Ranges and then the data validation formula was transferable to both data ranges.

If you have any ideas as to the other two problems, I will be glad to know!

#4

Don’t know why, but if Extra sheet tab is moved to another position (i.e., to the left of List), you can delete the sheet and Database ranges are not deleted.

Also, I can’t say why, but choose menu Tools - Options - LibreOffice Calc - Calculate, and there select Enable wildcards… or No wildcards… (beware of unwanted consequences).

#5

This are common characters with special meaning in regular expressions. There are more, see:
https://help.libreoffice.org/latest/en-US/text/shared/01/02100001.html?&DbPAR=SHARED

1 Like
#6

:+1:t2:

image
image1920×1040 195 KB

Why? Because the “validity fail.ods” was created in an older version of LibreOffice, or in OOo/AOO (where wildcards didn’t / doesn’t exist at all), while “valdity success.ods” in a newer version of LibreOffice (5.3 and newer), where wildcards became the default for interoperability. Or maybe the regex setting was chosen explicitly for powerful capabilities.

Note that wildcards have their own (smaller) set of “strangely behaving” characters.

2 Likes
#7

Looks like a bug to me, although @erAck can tell more.

2 Likes
#8

@mikekaganski I believe this is the most likely reason. As mentioned, “Validity fails” is from a pretty old workbook, all settings intact. I’ve never had a need to change wildcard or regular expressions settings before, at least until now. I think this may solve Problem #1.

@LeroyG also correctly identified the solution for the second and third problems. It does appear that perhaps the “deleting the Extra sheet” problem may indeed just be a bug, that somehow worked it’s way in over years.

Much thanks to all for the detective work, and helping me to learn a few things.

#9

That very much looks like a bug, though contrary to the original post it also happens in the “success” document.

But if sheet List is moved to the end and then sheet Extra (then in the middle) is deleted, the database ranges are deleted.

Anyway, someone please file a bug for this and report the bug number back as tdf#123456.

#10

Hello,

side note: TheCAT extension (TheCalc Auditing Tool) helps auditing Calc spreadsheets.

As for the problem at hand, its Named Ranges chapter would show that, for validity fail.ods, all named ranges addresses display the #REF! error.

Capture d’écran du 2022-12-16 18-03-51
Capture d’écran du 2022-12-16 18-03-51843×314 35.8 KB



TheCAT can be found here: https://extensions.libreoffice.org/en/extensions/show/20671

FWIW

2 Likes
#11

tdf#152577

2 Likes

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.