Auto Complete

[MACDOB]

Hi Out There. I am still working on my Transport log sheet in Excel and I understand that I should be able to click on a cell in sheet 1 and after typing in one or two characters it will either complete it or refer to a list which has been compiled in another worksheet in the same workbook. I have tried do do this quite a few times but at the moment have failed. What I wanted to do was the same as when you start to type in an e mail address in and it automatically gives complete similar optioons.
I am too old @74 to be starting to learn VB and I was advised when I was about60 that unless I was going to do it for a job I should not bother . How I wish that I had ignored the advice. So any help will be greatfully accepted. Regards Macdob

 

[Trevor G]

Have you looked at using the Validation feature as you can set it to look at list from some where else and it can provide you a drop down arrow. Active in Excel @74 nice one !

 

[MACDOB]

Hi Trevor, I have just tried to do the Validation but I cannot for the life of me get it to work. Obviously doing something wrong. I have compiled a list of data in sheet 3. In this case it is Registration Numbers of Vehicles that might regularly arrive on site. In sheet 1 I want to be able to either double click on a cell and access the list in sheet 3 or type in the first two characters of a Reg. No and it will give me a selection of those say beginning with "A" or "AE". By the way I have just been to the dentist and had two very difficult teeth pulled and at the mo am under the influence of tranquillisers so excuse any sp[elling mistakes. Look forward to your reply hope yo can help. Step by Step instructions would be good. Since I have your name mine is Chris

 

[Trevor G]

Ouch Poor you Chris.

Try doing this

Select the Vehicle List in Sheet 3
On the Formula Bar very left Click in Name Box
Type in Vehicles and press Enter (Simple way to Name a Range)
Back to the other sheet
Select the Cell then Data And Validation
Change to List
Press F3 (Shows name box)
Select the Name and click OK

That will give you a drop down of all the vehicle names from the other sheet

I hope this helps towards finding a solution.

 

[MACDOB]

Thank You Trevor, I tried that and it does not seem to want to work for me. I had already named the list column as Col 1 in sheet 3. If I click on the arrow to the right of the name box it also shows two other names including Vehicles but if I select vehicles it always goes back to Col 1 which was the first name I gave it. I now seem to be locked into the names for the list How do I get rid of them? Should I start again?

 

[Trevor G]

Good morning Chris,

To delete named ranges if using use Ctrl + F3 this should show you the Named Ranges box then just select the named range you no longer need and delete it. You can also create new names from here.

The name I mentioned was just a suggestion, so be creative !

 

[MACDOB]

Thank You again Trevor, My gums are a bit sore but everything seems to be OK. I will try everything you suggest again in a new workbook and I have downloaded Learn Excel from MrExcel so I hope I can understand how it is done. My problem is that I find it difficult, maybe because of computer speak, to learn from books. I was a technical Lecturer in Education but they things practical like carpentry and painting etc. I tend to learn from example. I will let you know how I get on Regards Chris