Looking to create a simple user form with lookup

G

Guest

I am trying to create a simple little program for our employees to check
credit card numbers and return which Credit Card company it belongs to.
My thought was to have a simple user form 'popup' stating, "Please enter
Credit Card Number here", a box to put the numbers in. Once they enter the
card number click the button and have it return a statement, "This is a ABC -
Master Card, contact number is (800) 555-1234"

The lookup would compare the first 6 numbers (although a few only do the
first 2 or 5) of the credit card entered, to a list in column A in Sheet1.
The Credit Card company names are in column B and the phone numbers are in
column C.

Can anyone point me in the right direction?

Thanks
Tim
 
D

Dave O

There are a number of ways you can do this: you could create a
userform, for instance. Since you're starting from scratch you have
the ability to customize the data in / data out process, and you will
look like a genius if you set it up to be most convenient for the end
user. Do they need to print this info? Do they need to enter 10 at a
time? If you can determine the issuing company by the first 6
characters, are you going to require the users to enter all 15 digits?
(People in a production environment hate that kind of thing.)

The easiest way to do this, to my tiny mind, would be to have a
workbook with two tabs: one that allows user input and returns results,
and one that stores the credit card company info and phone number (this
tab could be hidden, if you like). Set up a column of 10 or so cells
for the user to enter those 6 digits; right next to it set up a VLOOKUP
formula that matches the 6 digits to the issuing company data and phone
number.

If you'll post some different examples of data (specifically, the
number of characters that may be used to match) and the desired
outcome, someone here on the newsgroup can help you with the exact
formulas.

Good luck with it!
 
G

Guest

They information would not have to be printed. As it stands now, there is 551
possible numbers to compare to. All but 4 are 6 digit numbers (i.e. 451212) 3
are 5 digit and one is a 2 digit.

The card information would only be entered one card at a time. I guess it
would save a bit of time if they only had to enter the first 6 digits of the
credit card.

The way I have my data sheet setup at this time is in three columns/ 551 rows:

Column A holds all the possible 6 digit combinations (including the 3, 5
digit and 1, 2 digit).
Column B has the corresponding Credit Card company name
Column C has the Credit Card company's phone number.
i.e. Cell A1- 456712 B1 – Master Card C1 – (800) 555-1234


I’m in the law enforcement field and thought this would save me some time
when I’m trying to figure out which credit card company to contact when I’m
investigating a credit card matter. As you may have guessed comparing the
card to a list of 551 numbers could be easier, especially if I have multiple
numbers to lookup.

Thanks
 
D

Dave O

Good morning, Tim-
Apologies for the delayed response, and thanks for the insight to your
application. I thought you might be in some sort of high-volume
production operation.

Your most efficient/effective solution is likely to be a VLOOKUP. This
formula looks for an exact match of the value in cell A2 in the range
A10:A560, so if there is a typo or a period or a space then the match
will not be found. Let's assume your 3 columns of 551 rows are located
in the range A10:C560, and you want to enter the lookup number in cell
A2. (For aesthetics add headers: A1 = "Card Number", B1= "Company", C1
= "Phone" or some such.) I'll add some additional syntax that will let
you know if the number is not found instead of returning an inscrutable
error.

The formula in cell B2 would be
=IF(ISNA(VLOOKUP(A2,A10:C560,2)),"Card number not
found",VLOOKUP(A2,A10:C560,2))

The formula in cell C2 is
=IF(ISNA(VLOOKUP(A2,A10:C560,3)),"Card number not
found",VLOOKUP(A2,A10:C560,3))
 
G

Guest

Thanks Dave O,
I was almost there. I figured out how to fix the 2 and four number combo's,
just filled the series as they were 6 numbers.

I've just created a sheet were they can fill the numbers and get their
returned results. Put the data on another sheet and hid that sheet.

Thanks for the guidance.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top