V LookUp
 

I am trying to set up a file that is user friendly. One sheet will be the
sheet to key and the other sheet will be password protected with valueable
information that will need to be inputed into another system electronically.
I would like for the user to be able to key in one number and the other
information flood in. How can I set up a v look up to accomplish this? (I
was using a seperate sheet to have the table of information but can't seem to
get it to work). I can't use the if statement because I have about 20-30
items that need to be used... Any ideas? I'm a visual person...

Example:
User keys in:

Account Number 123456

Output (All in different cells and on another sheet)

(A1)Customer Name: ABC Sales
(B1)Address: 123 Main
Etc....
--
rojobrown

V LookUp
 

Hi rojo,

If you can send me an example worksheet I will give it a go.

Regards,
Howard

"rojobrown" wrote in message
...
I am trying to set up a file that is user friendly. One sheet will be the
sheet to key and the other sheet will be password protected with valueable
information that will need to be inputed into another system
electronically.
I would like for the user to be able to key in one number and the other
information flood in. How can I set up a v look up to accomplish this?
(I
was using a seperate sheet to have the table of information but can't seem
to
get it to work). I can't use the if statement because I have about 20-30
items that need to be used... Any ideas? I'm a visual person...

Example:
User keys in:

Account Number 123456

Output (All in different cells and on another sheet)

(A1)Customer Name: ABC Sales
(B1)Address: 123 Main
Etc....
--
rojobrown

V LookUp
 

Set up a Customer List (use a named range; see
http://www.contextures.com/xlNames01.html for details) with columns like this:
A B C
1 CustNum CustName CustAddress

On your "input" spreadsheet, the user enters the Customer Number (which
could also be a drop-down list; see
http://www.contextures.com/xlDataVal01.html for details) in A1

Use the following formulas:
Cell B1: =VLOOKUP(A1,Customer_List,2,false)
Cell B2: =VLOOKUP(A1,Customer_List,3,false)

The formulas can be on the same "input" sheet (use Protect Cells to limit
what your user can type over and what he/she can't) or on a separate sheet
(in which case, your reference to A1 will have to include the sheet name).

"rojobrown" wrote:

I am trying to set up a file that is user friendly. One sheet will be the
sheet to key and the other sheet will be password protected with valueable
information that will need to be inputed into another system electronically.
I would like for the user to be able to key in one number and the other
information flood in. How can I set up a v look up to accomplish this? (I
was using a seperate sheet to have the table of information but can't seem to
get it to work). I can't use the if statement because I have about 20-30
items that need to be used... Any ideas? I'm a visual person...

Example:
User keys in:

Account Number 123456

Output (All in different cells and on another sheet)

(A1)Customer Name: ABC Sales
(B1)Address: 123 Main
Etc....
--
rojobrown