How to add cell locater?

[jiminy_crime]

I am somewhat new to using excel but am needing to learn how to use i
pretty quick.

I have began a list of svc. points for my company and it has grow
substantially in the last few months and need to know how to add
button to a cell that will allow me to pull up a specific city withou
having to go searching for it using a filter.

Is there such a process and if so, is there also a way that I can ad
new cities with out having to scroll down my list and add it to th
list in alphabetical order. This has really become a big pain.

Thank

 

[Trevor]

Try "Validation..." from the "Data" menu, and choose the "List" type. This
might be what you're looking for. It will allow you to build a list of
cities somewhere on your spreadhseet. Then you can tell a certain column to
only allow entries from that list of cities. In that column, you can type a
city name, or you can pick one from the drop down list that Excel now puts
in the cells for you.

 

[jiminy_crime]

Thanks for the reply. I will give that a try but can I also make a cel
that you can type the city name and it will search and find tha
particular cities information

 

[jiminy_crime]

I guess my real question is can you add a search button to excel

 

[Trevor]

Sure.

You can make a list of cities and use that list for your cell validation.
Next to that list of cities, you can put informatino about the city. For
example, if the cities are listed in column J, you can put the state in
column K.

Now, if you format column A to validate against the list of cities in column
J, then you can have column B show you the state that the city is in. in
column B, put the following formula:
=VLOOKUP(A1,J1:K10,2)
where A1 is the city you entered, J1:K10 is the range of the list of cities
and the informatino about them in the adjoining columns, and the number 2
means retrieve the data from the second column in that range.

Note that if you have multiple columns of information next to your list of
cities, you can retrieve any column by changing the number 2 in the above
formula. Also note that you can combine multiple pieces of information into
one cell:
=VLOOKUP(A1,J1:K10,2) & " " & VLOOKUP(A1,J1:K10,3)

 

[jiminy_crime]

I appreciate the response but I have run into a problem. When you sa
validate A from B, what do you mean?

I would like to add a search button to A6 in my spreadsheet which is
bland cell. I have a list of cities and the state they are from fro
A7 to A250. How would I type the formula for something like this

 

[Trevor]

You need to provide more information about what you want your spreadsheet
contains and, more importantly, what columns you're tryingt to fill in, and
with what information. In particular, I don't understand what you mean by a
"Search button" and you haven't told me what you're going to do with the
results once you've "Found" what you're looking for.

If you expect the user to type a city into cell A6, that's fine. If you
expect the user to ONLY type in a city that is included in your list, then
you click on cell A6 and go to the Data menu, choose Validation, in the
Allow field, choose List, and type A7:A250 in the Source field.

What you do in column B is up to you, I was just trying to guess at what you
meant be by "search". I thought the VLOOKUP function was what you needed.
But nothign you've said below indicates to me taht you need that function.
But you might want to read up on it anyway.

 

[Tushar Mehta]

Why not use XL's native find capability? CTRL+F

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions