Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up information
I have two small spreadsheets. One is a spreadsheet that
I use to produc quotes. The other is a spreadsheet that has a list of customers. I would like to enter the customers account number on the quote and have all of the rest of the information (name, address, city, state, etc.) entered automatically. I have looked at the vlookup function but I don't seem to be doing it right. Could someone please let me know if this the the right function to use and how to use it correctly. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up information
Vlookup would require that the data is arrange with the account number as
the leftmost column in your table. assume acctno name address city state A2 B2 C2 D2 E2 A3 B3 C3 D3 E3 A4 B4 C4 D4 E4 assume the above is on sheet1. Assume A2 represents both the location A2 and the account Number is A2, the name is B2 in cell b2, etc just for simplicity. on sheet2, A1: A3 (A1 holds the acct number A3) B1: =Vlookup($A1,Sheet1!$A$2:$E$4,2,False) C1: =Vlookup($A1,Sheet1!$A$2:$E$4,3,False) -- Regards, Tom Ogilvy "dyowell" wrote in message ... I have two small spreadsheets. One is a spreadsheet that I use to produc quotes. The other is a spreadsheet that has a list of customers. I would like to enter the customers account number on the quote and have all of the rest of the information (name, address, city, state, etc.) entered automatically. I have looked at the vlookup function but I don't seem to be doing it right. Could someone please let me know if this the the right function to use and how to use it correctly. Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looking up information
dyowell
First, on Sheet2 you would have a table with 5 columns containing your list of customers under these headings.... Code, Name, Address, City, State Select that table and InsertNameDefine. Call it mylist. Now, assuming you enter the code number in A1 of Sheet1 and you want name in B1, address in C1, city in D1, state in E1. In B1 enter =VLOOKUP($A$1,mylist,2,FALSE) In C1 enter =VLOOKUP($A$1,mylist,3,FALSE) For D1 and E1 just advance the number to 4 and 5 In all cases, your cell references may differ. Adjust to suit. Gord Dibben Excel MVP On Tue, 20 Jul 2004 11:06:58 -0700, "dyowell" wrote: I have two small spreadsheets. One is a spreadsheet that I use to produc quotes. The other is a spreadsheet that has a list of customers. I would like to enter the customers account number on the quote and have all of the rest of the information (name, address, city, state, etc.) entered automatically. I have looked at the vlookup function but I don't seem to be doing it right. Could someone please let me know if this the the right function to use and how to use it correctly. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot table cutting information of a part of the information | Excel Discussion (Misc queries) | |||
Help!! using sheet 1 information to change information on sheet 2 | Excel Worksheet Functions | |||
information | Excel Discussion (Misc queries) | |||
Information | Excel Discussion (Misc queries) | |||
LOOK UP INFORMATION | Excel Worksheet Functions |