title: Excel data validation by typing instead of selection
body: <p>I am struggling with the autocomplete list in my excel document.
  I was trying to use the example from OzGrid</p>
  
  <p><a href="https://www.ozgrid.com/Excel/autocomplete-validation.htm" rel="nofollow noreferrer">https://www.ozgrid.com/Excel/autocomplete-validation.htm</a></p>
  
  <p>But it seems like this step is not explained well enough.</p>
  
  <p>First of all, I did step one by linking my cells between these 2 sheets.</p>
  
  <p>[![enter image description here][1]][1]</p>
  
  <p>In both "Frontsheet" and "Locality" the list range is from C51 to C67, as per the OzGrid advice.</p>
  
  <p>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</p>
  
  <p>[![enter image description here][3]][3]</p>
  
  <p>and finally, I put the formula (assuming that the C50 is my dropdown list cell):</p>
  
  <p><strong>=OFFSET(Frontsheet!$C$50,0,0,MATCH("*",Frontsheet!$C$51:$C$67,-1),1)</strong></p>
  
  <p>but I am getting nothing apart of #N/A</p>
  
  <p>I don't know what's next.</p>
  
  <p>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?</p>
  
  <p>This question is somewhat a duplicate to the previous ones, which unfortunately didn't bring me the solution.</p>
  
  <p><a href="https://stackoverflow.com/questions/7989763/excel-2010-how-to-use-autocomplete-in-validation-list">Excel 2010: how to use autocomplete in validation list</a></p>
  
  <p><a href="https://stackoverflow.com/questions/19818075/excel-data-validation-with-suggestions-autocomplete">Excel data validation with suggestions/autocomplete</a></p>