Searching another worksheet from a worksheet

**Robotacha2010** · 12-04-2009, 08:31 AM

Hello all,

This is my first post

Im sure this is possible what I am trying to achieve, but I thought I would ask first to clarify and also to get any tips.

BACKGROUND INFO:

I have an excel sheet which we fill in when we have a new potential customer call in to open an account.

Recently we have been having issues where companies are calling in to open an account, but have previously been refused an account.
Unless one of the sales staff remember the company name from previous applications, there is no surefire way of knowing whether this person has rang in before and applied.

ALL new prospects for the company come through me and I log all of them on a excel spreadsheet.

WHAT I WANT TO ACHIEVE:

I want to add a button to the new prospect form, (I have added this button just under the cell where you fill in the BUSINESS NAME of the customer applying)

Then, when someone types in the BUSINESS NAME, they click the 'search' button and the button will activate a macro to search the prospect database (which is all in 1 column on a sperate worksheet)

if the name appears in this database I want it to return a simple YES or NO on the original form next to the search button.

Is this possible? perhaps using a lookup formula & also would the spreadsheet that the macro is searching need to be open on the same computer? (it is currently hosted on a network drive)

Hope you understand. Any tips or advice greatly appreciated!!!

Thanks in advance,

Michael Jones

**DonkeyOte** · 12-04-2009, 09:55 AM

Probably best to post a sample workbook which outlines what you have in place thus far... dummy client names obviously (and anything else confidential)

You can attach a file via the paperclip icon in the reply window - if you can't see it click Go Advanced.

**Robotacha2010** · 12-04-2009, 10:34 AM

Here are the files I am using (apologies if they are messy!!)

Basically, the SEARCH button I have added onto the prospect form, when clicked I want it to scan the data in the first column of the database file and check to see if the data entered in the business name cell on the prospect form matches any data in the database.

Also, if possible, I want to search for close matches aswell. eg:

Business name = Thorn ltd

Database file contains = Thorns ltd or Thorn or Thornbury ltd etc etc...

The database file i have currently contains around 450 potential new customers. This amount has been aquired over a 6 month period. so a years worth of prospects would average around 1000 rows....

Hope this helps!

Regards,

Michael Jones