Data validation dropdown with autocomplete replication, can't remember how to do this?

[ThiaJay]

I realise that there are a lot of posts about this subject but none of them help me in this instance.

Before Christmas, I created a worksheet that enables the user to start typing and then auto-suggests and (when the user hits enter) auto inputs the choice from a list somewhere within the worksheet.

I can't remember how I got it to auto suggest as shown in the images below.

ztBBHA2.png

This is from the working worksheet.

l2zXbek.png

PMQ75Ml.png

Can someone please remind me how this is achieved?

It's doing my head in.

Thanks very much!

[Carim]

Hello,

Regarding Data Validation with the Autocomplete feature ...

attached is a test file with a UserForm

[ThiaJay]

Hello Carim, that looks very useful and I am sure I will be able to use that in other instances so thank you. However, that's far more involved that the implementation in my worksheet. Yours features a userform and separate datasheet. I haven't inserted a userform and don't think it's a defined/named range so really don't know what I did.

[Carim]

Hello again,

In order to have the Autocomplete feature WITHOUT any intervention ...

The quick and dirty solution is to have your list in the first rows of the same column (and hide these rows...)

[ThiaJay]

Thank you Carim, that was the reason it didn't work. It HAS to start in the row immediately underneath to auto suggest/complete. If there a way for this to work with three columns of data that are all in the same row?

For example, in the new worksheet, the dropdown would be in C4 and there would be data in C5:E168. If I choose the data from C10, it then should display D10:E10 in D4:E4 in addition.

6dL8qUt.png

I do imagine that this will require the use of VBA.

Thank you very much.

[Glenn Kennedy]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet. Make it clear exactly what has to autocomplete!!

[ThiaJay]

Hi there.

A picture is worth 1,000 words. An Excel sheet is worth 1,000 pictures.

Please read the yellow banner about sample worksheets, at the top of the screen. Act on its guidelines and post a SMALL sample sheet. Make it clear exactly what has to autocomplete!!

Hello Glenn, thank you for your reply. I was careful to make sure I included a screenshot, it should help make sense of what I wrote.

[Glenn Kennedy]

If you read the what was said... you were asked for a sheet, not a (non-editable) picture of one.

[ThiaJay]

Okay, in the end I stumbled across the solution: https://www.excelhow.net/auto-popula...selection.html