Formatting AutoComplete for cells

**pricey74** · 10-21-2009, 06:53 PM

Hi Guys this sound like a dumb question, i am using excell to do job sheets up for my business and wanted to know how to set up the following -
I want to be able to set up spread sheet so when i type in a roof colour it will show the word in the box if it has been used before such as if i am typing in roof colour box woodland grey and it has been used before it would show up when i typed the first few letters. How is this done, i have set up the sheet so i have drop down boxes with all examples in them but there is only so many examples you can put in dropdown boxes so i thought this might work better and quicker.
Hope it makes sense what I am asking
Cheers

**shg** · 10-21-2009, 06:56 PM

See http://www.ozgrid.com/Excel/autocomplete-validation.htm

**pricey74** · 10-21-2009, 07:07 PM

Hi shg, I have had a look at link but I don't understand 1 part of it

The Solution

Go to Tools>Options - Edit and check Enable AutoComplete for cell values "Did this part"
Move your list of items is in the range immediately above the Validation cell. "Don't understand what is meant by this or how to do it"
Hide the rows above your Validation cell. "Don't understand what is meant by this or how to do it" Cant find out where to change these options?? I am using Office 2007 if this helps.

**shg** · 10-21-2009, 07:09 PM

If you have a 10-item data validation list, put it in A1:A10, and put the data validation in A11. Then hide rows 1:10.

**pricey74** · 10-21-2009, 07:13 PM

Sorry lost me with all that new to using excell i will attach the file so it makes a bit more sense what I am after if you can see it.

Job Sheet.xls

Thanks
Shayne

**shg** · 10-21-2009, 07:22 PM

See the data validation in the highlighted cells.

**pricey74** · 10-21-2009, 07:37 PM

Thanks shg it works great, thanks heaps for all of your help everytime I have had a problem with excel I always get great help from people on this forum.
It's great thanks again