[Solved] Misbehaving data validity

Discuss the spreadsheet application

[Solved] Misbehaving data validity

Postby morphingstar » Wed Dec 02, 2020 12:56 am

I use Validity with cell range. I do not want to see white lines (fields) in Validity cell if there is nothing visible in the cell of the covered range. I have a range of >100 cells of which about 10% contain visible data. The data is concatenated. If I refer to a cell with "nothing" as a date source the date formula returns the zero value of win10 date, implicit cell containing 0, which it does not! If Data Validity refers to an "empty" looking cell which contains a formula the Validity cell shows an empty menu line as a nuisance. The setting of Allow Empty Cells is ignored, does not matter. The invisible formula is treated as an entry. Compare the logic!
I use the validity cell to see where there is a column with an entry. The entry consist of a date Dec-31 and a action note, concatenated. The range of applicable columns is >100. OO has been distributed for many years already. I can not understand why SoftWarriors operating as Trouble-Hunters have not corrected this matter.


I attach a spreadsheet showing the situation.
May be there is a solution - without macros.
Attachments
Validity failure LO Forum 20201129.ods
(17.22 KiB) Downloaded 20 times
Last edited by MrProgrammer on Tue Dec 15, 2020 2:15 am, edited 1 time in total.
Reason: Tagged ✓ [Solved]
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 88
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: misbehaving data validity

Postby Zizi64 » Wed Dec 02, 2020 8:10 am

Just a tip:

Use the option "Sort entries ascending". Then the repeated (empty-like cell contents) values will be appeared once only.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9800
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: misbehaving data validity

Postby Zizi64 » Wed Dec 02, 2020 8:25 am

The setting of "Allow Empty Cells" means: The cell - where you applied the Data validity feature - can be empty.
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9800
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary

Re: Misbehaving data validity

Postby morphingstar » Sun Dec 06, 2020 12:35 am

thanks, I noticed the one empty line in one of the validity menus, wondering - no more. You explained it. SORT may be a compromise. I have an non-alphabetical order in the range, more to my needs.
When the content of the range changes Validity ignores it, requiring DATA-VALIDITY confirm_range. Automatic "recalculation" missed this part. OK, it is a free program!
Are you still 64? (song). Sending you a virtual Bulls Blood.
OO 4.1.6 on Win10. Member since StarOffice 7. Alternative name "morphingstar2" when other fails. Use Writer, Calc (as DB + Calc), Draw. Impress mainly for Eyedropper and animating GIFs. Updated 20190716.
User avatar
morphingstar
 
Posts: 88
Joined: Mon Mar 28, 2011 5:52 am
Location: Mx

Re: Misbehaving data validity

Postby robleyd » Sun Dec 06, 2020 1:48 am

Topic also asked at viewtopic.php?f=9&t=103783

If you cross post, please note that you have done so, otherwise it leads to several discussions and a waste of time because several identical answers may be posted by different users.
Cheers
David
Apache OpenOffice 420m2(Build:9821) - Slackware 14.2 - 64 bit
LibreOffice 6.4.6.2 (SlackBuild for 6.4.6 by Eric Hameleers) - Slackware 14.2 - 64 bit
Apache OpenOffice 4.1.4 - Windows 7 Virtual machine
User avatar
robleyd
Moderator
 
Posts: 3847
Joined: Mon Aug 19, 2013 3:47 am
Location: Murbko, Australia

Re: Misbehaving data validity

Postby Zizi64 » Sun Dec 06, 2020 9:47 am

Automatic "recalculation" missed this part. OK, it is a free program!

The Data Validity not uses a link to the cell of the selected value. When you have selected a Value, that still in the cell after the source is changed, because you have not selected that new value yet. It is a normal behavior.
I hope it never will be changed. (It is same in Excel - if I know it exactly.)

And (if the settings of the error-indication function enables it) you can input your own value into the Data Validity cell, or even you can delete the value from a Data Validity cell, too. It is not possible if the function uses a "live link". The Data Validity reqvests an user action: the selection. The software can not recognize if you want to choose the new value in the source range or not (it work not based on the index of the choosen data, but it will be passed the data itself).

Are you still 64? (song). Sending you a virtual Bulls Blood.

I was born in '64, and my favorite wine is the Eger Bull Blood. ;)
Tibor Kovacs, Hungary; LO6.1.6, 6.4.7 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.1.1;AOO4.1.9
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
User avatar
Zizi64
Volunteer
 
Posts: 9800
Joined: Wed May 26, 2010 7:55 am
Location: Budapest, Hungary


Return to Calc

Who is online

Users browsing this forum: Google [Bot] and 17 guests