Searchable list in excel?

[leahca]

Hi All,

I am wondering if it is possible in any way to create a searchable list in excel?



We have a 'job costing sheet' that staff fill in and send to me for invoicing which has a data validation cell at the top connected to a 'list' of customers. The staff find it painful going through a very large list to find the customer they need and would like to instead be able to click in the cell, start typing 'Ap..' for example and customer 'Apple' automatically appears. Or even if they type 'G' and it automatically brings them to the list of customers starting with G.

Any ideas on how I can get around this? The result of this list is also connected to a Vlookup in the cell next to it to bring across the correct accounting code.

Thanks!

[Pecoflyer]

Hi,
maybe attach a sample file with an example of what you have, and what the result should be

[leahca]

Ok, I've uploaded an example, there is usually a lot more data in this sheet but have deleted everything other than what's part of my query (It was easier than making it all up) and obviously made up rubbish customers.

So, under the customer header, there is usually around 300 or so customers and they choose this using the drop down arrow, set up using data validation. Once the customer is selected it brings over the industry code automatically using vlookup. I need to know if there is an easier way for me to set up the customer selection process as scrolling though a list of that many customers using a drop down box can be painful. Ideally like I said it would be good if there was some form of search box or something that I could use so they only need to type the first few letters of a customer name to either bring in a suggested customer or bring them down the list to the customers starting with those letters. As far as I know it's now possible but it would be great if someone has an answer!

[Pecoflyer]

As I don't have 2007/2010 at work, I can't open your file.
But I think you can find the solution at http://www.ozgrid.com/Excel/autocomplete-validation.htm

[leahca]

Thanks, I thought about that one but it makes it hard when using a form because you need the list to be exactly about the data validation cell. It might be my only option though.

[leahca]

I found a way around it using that option, I've managed to move a few things and get the hide rows to work out, so thanks!!

[Pecoflyer]

Glad to here you found a solution

care to share it with us?

[leahca]

Just using the instructions here (The site you shared with me)

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

I had tried this in the past but it hadn't worked because the space between the cells that the list was referenced and the data validation cell was too great. But by playing around with hiding rows, I managed to get the selection to sit directly above the 'customer' data validation cell without changing my formatting