#1

Data validity - cell range does not appear to have full text search. This hampers the functionality to the point of being unusable. Requires being able to recall exact word ordering. If that were the case, generally wouldn’t need cell range lists to begin with.

Searchable dropdown list
#2

Plese upload your ODF type sample file here.

#3

Why ? There is a dropdown. So you are only required to be able to read?
.
Usually I use Alt+ArrowDown to access the List, if I remember correctly.

#4

Tips:

  • Make the column of the Data Validity cell wider. Adjust the column width to the longest text of the list
  • Use the slider at bottom ods the list of the Data Validity cell. Then you will able to see the rest part of the long texts.
  • Use the ComboBox form control element instead of the Data Validity. That can display the full text of the List, even if it is slimmer than the longest text of the assigned List.
    DataValidity_vs_ComboBox.ods (10.5 KB)
1 Like
#5

@Zizi64

libreoffice-cellrangvalidty-example.ods (29.6 KB)

#6

So you are only required to be able to read?

If you’re content to waste untold hours scrolling through 100-item dropdowns in a spreadsheet, that’s your prerogative. It’s a waste of time, and virtually every other online/native spreadsheet supports full-text search autocomplete.

2023-01-01_11-08
2023-01-01_11-081792×672 74.7 KB

1 Like
#7

Use the combobox

Appears potentially viable, but highly over-engineered solve to the comparably simple issue. Seems to require connection to a DB; all sorts of custom formatting to get it to behave like a regular cell dropdown; etc.

#8

You can use it similarly to the Data validity. Just use a Cell Range as a “database”, and a Linked (“Target”) cell.

#9

You have three data in one but in mixed order. It is contraproductive. You need restructure your data.
Maybe it is better to use three data lists: Manufacturer, Type, Year, and maybe need to use a preselection for the three dropdowns, or to use one precombined lists, like:

  • Year & Manufacturer & Type
  • Type & Year & Manufacturer
  • etc…
    Then you will able to decide which order you want actually, If you want to search by Year, then you must preselect the appropriate order. The combined string list may be located in a helper cell range.
    .
    Or maybe you can use filtered lists.
#10

I’m using validation only for “validation”, not as a selection.The big dropdown seems to be your idea…
.
As LibreOffice includes Base there are other ways to solve this.
.
You didn’t ask a question at an ask-site. So to stop wasting your precious time I assume you have two possibilities:
Either using “virtually every other online/native spreadsheet” or requesting an enhancement:

Feature requests should go directly to Bugzilla as Enhancement
Log in to Bugzilla

1 Like
#11

Not seeing a way to do this. To select a new source seems to require finding an ODF database file?

2023-01-05_19-47
2023-01-05_19-471142×531 43.8 KB

#12

At that point displayed on your attached image use the Cancel buttons twice.

  • Then clik on the inserted Form Control Element in Edit mode: Select it.
  • Righ click - click on item “Control” on the context menu. You will see a three TAB panel: General, Data, Events.
  • On the TAB Data there are fields like the “Linked Cell” and “Source cell range”. Fill-in them.
  • Switch off the Edit mode.
#13

FTR: “virtually any other” spreadsheet is MS Excel :wink:
And it does not provide this kind of search in its Validation tool.
Please follow @Wanderer’s advise and file an enhancement request.

1 Like
#14

The combo box does not appear to have full-text search either? Does not autocomplete unless typing characters from the beginning of the cell.

image

#15

Not. But it can display the full text without widener the Combo Box or the column. Try it with a slimmer Combo Box.
You will able to read the selection items without usage a slider, and you can choose the appropriate item. There are no more possibilities.
.
Try to reorganize the items, and use some preselection based on the parts of your mixed “data”. (As I suggested it above)

#16

OK, so this wasn’t the premise presented. Hard to tell if you didn’t understand the scenario or, given the arm-chair consulting above, you rejected the premise as somehow invalid. So in the real world, you are routinely provided semi-structured or completely unstructured “data” and do still need to work with it. Yep, a full data normalization exercise is the ideal when time and budget allow, but just as often the reality on the ground is needing to work with the data as-is, locking down follow-on logic (e.g. lookups) with simple functionality, like say cell validation…

#17

Sincerely do appreciate the more cordial tone. But am afraid it is based on a fallacy that this is not a gap of LO Calc in comparison to its competitors.

FTR, Excel’s cell validation does provide full-text search autocomplete.

2023-01-06_18-46

The previous screenshot already posted was Zoho’s spreadsheet tool. Google sheets does the same.

I’d definitely rather use open-source than proprietary MSFT or Big-Data cloud solutions, but push-comes-to-shove if there are simple functionality gaps and the attitude encountered above (*from other users) is the norm trying to address them; then back to big-data I go.

#18

Multiple web searches all landed at ask.libreoffice.org. Will let LO’s SEO folks look at that.

A far more constructive initial response would have simply been “there’s an enhancements site, [URL]”. It would seem your priority was getting in a snide insinuation that the poster was lazy (or I guess, possibly, illiterate).

If anyone moderates this site, end-of-the-day the trolly tones :point_up_2: are a legitimate barrier to FOSS adoption, and that’s a shame.

#19

To summarize for anyone finding their way to this that takes in interest in Combo Box implementation. My experience:

  • View > toolbars > Form Design
  • Turn Design Mode ON
  • Insert > Form Control > Combo Box
  • At Combo Box Wizard > click Cancel
  • Right-click Combo Box > Control Properties > Data
  • Source cell range:

To be clear, no the resulting list does not provide full-text search autocomplete, so is no different than cell validation in that regard. It also is no longer a spreadsheet cell (it is a html-like form element), and so you also lose the ability to run additional logic (e.g. vlookups) over the selected content, if that were also a requirement for you.

#20

Leaving the wording of the original post for posterity (and, I suppose, a spirit of fairness). However, the trivial pivot to adapt this to a Q&A forum is, predictably:

Q: “How do I get full-text autocomplete within cell validation in LO Calc?”

A: Libreoffice Calc does not currently support this functionality.

See logged enhancement request here.
https://bugs.documentfoundation.org/show_bug.cgi?id=152936

Autocomplete after 100 rows in Calc
Searchable dropdown list