Currently the autocomplete feature proposes results from the current column. I would like to suggest a feature and an option: "Source of AutoComplete results": "Current column of current sheet (default)"/"All columns from current sheet"/"Current column of all sheets"/"All columns of all sheets" Use case: we are storing a lot of invoice information in .ods files, where the second column always contains a company name, but the different years are contained in same file, but different sheets. The Company names are mostly repeating, but there are lots of new entries each year. Currently the AutoComplete is mostly unusable for some time each year, at least as long as the list gets filled up with recurring company names. For our case it would be really useful to be able to set the source of AutoComplete results to "Current column of all sheets", this way we could use the same names over and over without having to search for them in other sheets. The other options besides "Current column of current sheet (default)"/"Current column of all sheets" are the logical extrapolations of this feature which - I would say - should be added to handle all cases. Albeit we already had several cases when it would have been useful to be able to AutoComplete values from other columns as well. I've tried to search for similar feature requests, but I couldn't find anything related. Sorry if I was inattentive and there is already a ticket about this. If there is anything that can/should be cleared I'm really happy to correspond about it.
We already have a lot of options. Maybe the use of Menu/Data/Validity, could help.
Validity restricts the input and would be a proper solution for a fix list. But AutoComplete is a different workflow and suggests repeated input based on the current column. All major spreadsheet tools complete the input based on the current column. What kind of "pivoted" data would that be if A1:10 contains the same information as B1:10? So I second Miguel's hesitation for such an enhancement.
Thank you guys for the prompt reply! As Heiko already pointed out Validity is for a different workflow. While the argument about the plenty of options is true and agreeable, the other one about all the other spreadsheet tools is way weaker. A feature shouldn't be considered on the base that some other software has it or not, but solely on the base whether if it makes sense and if it is feasible or not. <<placeholder of some rant-suspicious thought-to-be-funny text about rarely used so not needed features>> :))) (actively trying not to offend anyone) As about the repeating/reused data: I wrote the 4 member option list as a logical expansion of my original need. Maybe I overshot the target a little bit. Sorry! So if I want to present my strict needs, that's about getting AutoComplete results from the same column but different worksheet, so the two options would be: "Current column of current sheet (default)"/"Current column of all sheets" Btw in the past I had several times the need to use the other two options as well, but I can't precisely recall those cases, so let's skip that for now. :) Thank you for taking the time to think about my idea, maybe it takes root in one-form-or-another.
[Automated Action] NeedInfo-To-Unconfirmed
We discussed the topic in the design meeting. Autocomplete (actually Tools > AutoInput) in large spreadsheets is not advisable as too many content starts with the same characters. Plus, using the whole sheet/document as basis for the autoinput has likely a negative impact on performance (probably the reason why competitors don't do either). The simple workaround is to add the word list at the bottom of the needed column. And using a macro is always possible too. Putting all together we decided to not support the idea.