0

I am struggling with the autocomplete list in my excel document. I was trying to use the example from OzGrid

https://www.ozgrid.com/Excel/autocomplete-validation.htm

But it seems like this step is not explained well enough.

First of all, I did step one by linking my cells between these 2 sheets.

[![enter image description here][1]][1]

In both "Frontsheet" and "Locality" the list range is from C51 to C67, as per the OzGrid advice.

Next the step with [Dynamic Ranges][2] probably refers to older versions of Excel with traditional menu, where we could select the "Tools" from the bar. Now in Excel 2016 I believe, that it should be like follows: Formulas - Name manager - New... where we put our name, scope and refers to (range). I have created the Myrange

[![enter image description here][3]][3]

and finally, I put the formula (assuming that the C50 is my dropdown list cell):

=OFFSET(Frontsheet!$C$50,0,0,MATCH("*",Frontsheet!$C$51:$C$67,-1),1)

but I am getting nothing apart of #N/A

I don't know what's next.

I don't want to use VBA this time, because I want to have these lists allocated to the specified cells. I want to search the records by typing not by selecting since I have got them quite a lot. Is it possible?

This question is somewhat a duplicate to the previous ones, which unfortunately didn't bring me the solution.

Excel 2010: how to use autocomplete in validation list

Excel data validation with suggestions/autocomplete

Geographos
  • 827
  • 2
  • 23
  • 57
  • " finally, I put the formula ...". Please disclose where you put it. I defined a named range using your formula. Then I assigned the named range as list source for a data validation drop-down. It worked perfectly - presuming that that's what you have in mind. Your question has too many references and no clear target. – Variatus Apr 29 '20 at 12:25
  • I put my formula in the cell C68, as you may have noticed. If you defined the named range and made the dropdown list it's fine, although was you able to type-in the text inside? or just select from the list? What I need to achieve is the dropdown combined with possibility of type some string, which help me to populate some best matching records. This is why is this mess around about. – Geographos Apr 29 '20 at 13:29

2 Answers2

1

Your formula =OFFSET(Frontsheet!$C$50,0,0,MATCH("*",Frontsheet!$C$51:$C$67,-1),1) shouldn't return anything but #N/A when entered in a cell because it defiens a range which Excel can't display in a single cell. However, you can use it to define a named range and then use that name to define a Data Validation list.

MATCH("*",Frontsheet!$C$51:$C$67,-1) doesn't work reliably if there are numbers in the lookup range. You might replace it with COUNTA(Frontsheet!$C$51:$C$67) which can deal with numbers or text equally well. The difference is that MATCH will produce the entire list, including intervening blanks, while COUNTA will truncate the list at the bottom by as many rows as there are blanks higher up. Either way, one usually avoids blanks in the source for a validation list.

If you want the user to be able to either choose or enter, you must disable Show alert after invalid data is entered on the Error Alert tab of the Data Validation dialog box, where you set up the validation rules.

Variatus
  • 14,293
  • 2
  • 14
  • 30
  • Yes, it works. However, I still have no autofill. If one of my credentials is Abi Xxxx. When I start typing "Abi" it returns only "Abi" with no autofill, that could let me type the surname instantly if that makes sense. – Geographos May 04 '20 at 09:08
  • Check *File > Options > Advanced > Editing options > Enable AutoComplete for all Cell Values*. If that is checked, any further improvement can only be achieved by replacing the Data Validation drop-down with a ComboBox. The worksheet *AutoComplete* works with what was previously entered in the same column. The ComboBox's *AutoComplete* works with what's in the drop-down list. I'm afraid you may want the latter. – Variatus May 04 '20 at 09:17
  • I have got it turned on – Geographos May 04 '20 at 09:28
  • Thank you for the hint anyway. I will attend again with the macros probably :) – Geographos May 04 '20 at 09:32
0

The OzGrid solution is poorly written and deceptive. It is simply capitalizing on AutoComplete for cell values. There is no magic in linking to another sheet and using offset or in creating a named reference.

All you need to do is add a list of values you intend to use in the column above the column. Avoid empty rows between this list of 'default' values and what you intend to enter.

List of 'defaults' above header. "Data1" is autocompleted.

Skipped rows 'break' AutoComplete for cells.

Skipped rows 'break' AutoComplete for cells.

But can be resolved by adding an adjacent contiguous 'indexing' column.

Contiguous indexing column resolves AutoComplete for cells in adjacent column.

M.Sqrl
  • 394
  • 3
  • 12