[Solved] Misbehaving data validity

[MrProgrammer]

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.

[Zizi64]

Just a tip:

Use the option "Sort entries ascending". Then the repeated (empty-like cell contents) values will be appeared once only.

[Zizi64]

The setting of "Allow Empty Cells" means: The cell - where you applied the Data validity feature - can be empty.

[morphingstar]

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.

[robleyd]

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.

[Zizi64]

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.