AUTO SUGGEST DROP DOWN LIST

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
119
Office Version
  1. 2013
Platform
  1. Windows
Can anyone please tell me how to do this.

From Cells A1 to A9 I have a list of names

From Cells B1 to B 40 I made a data validation list so that the user can select only the names in Cell A1 to A9

Works fine,

However it will be more user friendly if the cell say B 30 while being data entered, suggests from the drop down list based on the characters input by the user so he will not have to use the cursor to select the name from the drop down list but after seeing the match just press enter to fill the cell
 

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
119
Office Version
  1. 2013
Platform
  1. Windows
CANT SEE TOOLS > OPTIONS IN EXCEL 2013
 

Rahul1987

Board Regular
Joined
Apr 10, 2021
Messages
80
Office Version
  1. 2010
Platform
  1. Windows
pls provide image with your question for the better understanding
 

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

It should Be File>>Options>>Advanced>>Editing Options
 

apgmin

Board Regular
Joined
Mar 1, 2010
Messages
119
Office Version
  1. 2013
Platform
  1. Windows
already did, not working
 

Attachments

  • Screenshot_3.jpg
    Screenshot_3.jpg
    86.5 KB · Views: 9

Michael M

Well-known Member
Joined
Oct 27, 2005
Messages
21,104
Office Version
  1. 2016
  2. 2013
  3. 2007
Platform
  1. Windows

ADVERTISEMENT

Which solution did you try, as there are 2 in the link provided??
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,881
Office Version
  1. 2016
Platform
  1. Windows
already did, not working
I guess by default the auto-complete is enabled

The first method needs you to put the list on rows above the validation cell which is not the way you lay out of your current sheet. This means

You list is from A1 to A9, the validation cell is at A10. You can hide all the rows above the validation cell which is A1 to A9. As you type the validation cell, the auto-complete will predict the item based on the list.

The option 2 is placing the list on different sheet which you can hide later. You can use named range as validation source.

If you go for VBA solution, you need need to use a combo box as drop-down validation list.
 

Forum statistics

Threads
1,148,182
Messages
5,745,210
Members
423,933
Latest member
ankushmukherjee

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top