How to add cell locater?

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

I have began a list of svc. points for my company and it has grown
substantially in the last few months and need to know how to add a
button to a cell that will allow me to pull up a specific city without
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 add
new cities with out having to scroll down my list and add it to the
list in alphabetical order. This has really become a big pain.

Thanks


---
Message posted from http://www.ExcelForum.com/

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 >" <<jiminy_crime.19g9cp@excelforum-nospam.com> wrote in
message news:jiminy_crime.19g9cp@excelforum-nospam.com...
> I am somewhat new to using excel but am needing to learn how to use it
> pretty quick.
>
> I have began a list of svc. points for my company and it has grown
> substantially in the last few months and need to know how to add a
> button to a cell that will allow me to pull up a specific city without
> 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 add
> new cities with out having to scroll down my list and add it to the
> list in alphabetical order. This has really become a big pain.
>
> Thanks
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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


---
Message posted from http://www.ExcelForum.com/

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


---
Message posted from http://www.ExcelForum.com/

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 >" <<jiminy_crime.19gbfa@excelforum-nospam.com> wrote in
message news:jiminy_crime.19gbfa@excelforum-nospam.com...
> Thanks for the reply. I will give that a try but can I also make a cell
> that you can type the city name and it will search and find that
> particular cities information?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>

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

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


---
Message posted from http://www.ExcelForum.com/

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.

"jiminy_crime >" <<jiminy_crime.19gdsy@excelforum-nospam.com> wrote in
message news:jiminy_crime.19gdsy@excelforum-nospam.com...
> I appreciate the response but I have run into a problem. When you say
> validate A from B, what do you mean?
>
> I would like to add a search button to A6 in my spreadsheet which is a
> bland cell. I have a list of cities and the state they are from from
> A7 to A250. How would I type the formula for something like this?
>
>
> ---
> Message posted from http://www.ExcelForum.com/
>