Drop-down list with conditional formating acting strangely

English
#1

I have a spreadsheet with hundreds of rows with several columns of cells that have a drop-down list with either ‘Yes’ or ‘No’ as options. Each of these cells is also formatted to be green for ‘Yes’ and red for ‘No’. If I don’t use the right-hand arrow with the mouse to chose from the list and instead start typing into the cell with the 1st letter of ‘y’ or ‘n’ (upper or lower case), the cell will fill with the rest of either word. This has worked just fine for a long time for every cell… up until recently. Now, in my new unfilled cells (and even in all previously filled cells above in the same columns, when I change the input), there are several cells in each row (always in the same columns) that will not complete the word when the 1st letter is typed. If I move to the next cell with only that 1st letter I get my preset conditional error message and the cell remains empty, and if I finish typing the word the 1st letter will not automatically capitalize and the conditional background color does not change to green or red. Whats even stranger is that each one of these screwy cells only does this with one or the other (Yes or No), never both. Again, this is only happening in 4 or 5 of the 12 columns. All of the other cells are working just as they always have.

I have tried everything I could think of and searched every LiberOffice help for a cause for this with no success. Could use some guidance here. Thanks

#2

Please report your LibreOffice version and OS Menu/Help/About LibreOffice., and attach a sample to test the issue.

#3

OS is Win 8.1 & LibreOffice is:

Version: 7.2.0.4 (x64) / LibreOffice Community
Build ID: 9a9c6381e3f7a62afc1329bd359cc48accb6435b
CPU threads: 16; OS: Windows 6.3 Build 9600; UI render: Skia/Raster; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL

Spreadsheet Sample.ods (21.6 KB)

#4

Column “E” does not autocomplete when you press the “Y” key because there is no cell with “Yes” in this column. When you click on “N”, the cell is filled in as “No”. The other columns have “Yes”.

#5

Well now, everything is working perfectly and I I’m not sure why. What I sent earlier was just a small segment of a much larger spreadsheet. The top row, which I deleted for the sample, has the headings for each column. I had several other rows, but not all, that had the exact same header for each month. When I deleted all but the top row header and froxe it, all the cells started working just fine. The reference cells for the dropdowns and the conditional formatting for the cells has always remained the same. Definately wierd. Thank you both for your time.

#6

Let’s open the file from your message and select cell F40 (for example). If you enter “y” and then press Enter, “Yes” appears in the cell. But cell F40 has no validation.
This means that cell autoinput is not related to validation of values, but to other filled cells in the column.

#7

I’m not entirely sure what you mean. If you’re saying that the autofill is occuring only because of the cells directly above having a “Yes” or “No” in them, I get that. What I don’t get is that there is no validation happening & why.

#8

The presence or absence of data validation in a cell does not affect how the autocomplete (autofill, autoinput) process of this cell will proceed.
If you press Alt+down arrow, you can select the value you need from the list (but this is not called autofill).

#9

I’m not entirely sure what you mean. If you’re saying that the autofill is occuring only because of cells above having a “Yes” or “No” in them, I get that. What I don’t get is that there is no validation happening & why.

Also, in my sample F40 is outside the box and not formatted. I’m unsure why you chose that as an example. What I have noticed in the sample and the full version of my speadsheet is that unless a cell is immediately beneath an already filled cell it won’t auto fill with a “Yes” or “No” when typing a “y” or “n” at all. I suspect this has something to do with the non-validation you mention.

And I’m still left with why I had 3 columns on my spreadsheet (the sample failed to duplicate this) that when I typed an “n” into the immediate cell below, all it would show is the “n” and not a “No” (the “Yes” worked just fine), and all 3 of these columns did have Nos & Yeses above in them. All other columns worked fine. Even stranger (and keeping in mind that my sample was only for a single month and the full sheet has roughly 18), is when I removed some header rows (as mentioned in my previous comment) that were still above the last few months, these 3 columns started working like the rest.

#10

AutoFill and Word Completion and AutoInput are all different things. AutoInput is the completion of input with already existing entries of the same column when typing. Word Completion is completing words from a dictionary (in Writer). AutoFill is filling a cell range with a value or formula along its adjacent cell range.

#11

Thank you @erAck, I used these words only within the framework of this topic in order to be understood. Naturally, we are discussing AutoInput.

#12

The presence or absence of data validation in a cell does not affect how the autocomplete (autofill, autoinput) process of this cell will proceed.
If you press Alt+down arrow, you can select the value you need from the list (but this is not called autofill).

Yes, I get that. Thank you.

#13

Good luck!

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