Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Looking to create a simple user form with lookup
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 |
#2
|
|||
|
|||
Looking to create a simple user form with lookup
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! |
#3
|
|||
|
|||
Looking to create a simple user form with lookup
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 Im in the law enforcement field and thought this would save me some time when Im trying to figure out which credit card company to contact when Im 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 "Dave O" wrote: 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! |
#4
|
|||
|
|||
Looking to create a simple user form with lookup
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)) |
#5
|
|||
|
|||
Looking to create a simple user form with lookup
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. "Tim" wrote: 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 |
#6
|
|||
|
|||
Looking to create a simple user form with lookup
Glad to help!
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a lookup table with an added varable? | Excel Worksheet Functions | |||
Help with a simple form | Excel Discussion (Misc queries) | |||
Help with a simple form | Excel Discussion (Misc queries) | |||
Example User Form Required | Excel Discussion (Misc queries) | |||
Use a text box to calculate and show results in a user form | Excel Discussion (Misc queries) |