AutoComplete from Other List

[Pascal]

Hi,

I've just tried this in Excel 2010 and I'm unable to get it to work.

AutoComplete Typing in an Excel Data Validation List Without Macros

What I'm trying to do is AutoComplete based on a list elsewhere within the Workbook, so if a user enters in the first 2, 3 characters it will suggest the match based on what is found in the list. The same as the AutoComplete if adding an entry in a new row on a column.

It appears it can be done, but I'm unable to get the example to work?

Thanks & Regards

 

[Andrew Poulsom]

Did you have the list above the data validation cell?

 

[Pascal]

Hi,

Thanks for your reply, my mistake, looking at it again, I hid the columns and not rows, hence got myself into a bit of a mess.

Now that I've hidden the Rows and tried it again it works fine.

However, is there a way to do it without having the list at the top of the Sheet and looking it up from elsewhere?

Thanks & Regards

 

[Andrew Poulsom]

However, is there a way to do it without having the list at the top of the Sheet and looking it up from elsewhere?

No, because it relies on Excel's AutoComplete feature, which uses the same column to determine the possible entries.

 

[Pascal]

Hi,

Thanks again for your reply.

I see.

In that case if I explain what I'm trying to achieve...

I'm wanting users to be able to enter a car registration number into a Cell. On a daily/weekly basis the registrations are repeated due to site traffic, it would make entering them a lot quicker if upon starting to enter the registration the Cell somehow suggests which registration they're entering.

Currently this is done on a single sheet, one per month, here obviosuly because the registration has previously been entered that month Excel, using the built-in AutoComplete feature does this for the user, suggests the registration based on the first 2/3 characters entered.

What I'm trying to do is have a single sheet, which would be cleared at the end of each day, the data entered for the day would be saved elsewhere before being cleared. Therefore the AutoComplete wouldn't work as it has nothing above it to base it on.

Can anyone think of a way to have a 'AutoComplete' on a clean sheet? Idealy without the use of a Combo/Drop Down Box?

Thanks & Regards

 

[Andrew Poulsom]

Sorry, no.