Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoice: customer database and billing information
Hi,
I would like to connect an invoice sheet to a customer database (a database including names, addresses as well as billing information). I would be great to somehow be able to search for a customer in the invoice sheet, click on his/her name and the name + address of that customer would be sent to a specific cell and the billing information would be sent to another cell. For instance: Customer database (Sheet 2) A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX Invoice (Sheet 1): Search for the last name of customer (I don't know how, could be a function or something like that), it could be Anderson for instance, click on it and: the information in cell A1 in sheet 2 will be sent to cell D7 in sheet 1 the information in cell B1 in sheet 2 will be sent to D13 in sheet 1 How do I create a simple database with names, addresses and billing information and how do I connect it to the invoice? If you have any solutions I would really appreciate it! Thanks! Kind regards, Nic |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoice: customer database and billing information
Sheet 2 turned out a bit strange...I try again:
A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX -- Kind regards, Nic "Nic Daniels" wrote: Hi, I would like to connect an invoice sheet to a customer database (a database including names, addresses as well as billing information). I would be great to somehow be able to search for a customer in the invoice sheet, click on his/her name and the name + address of that customer would be sent to a specific cell and the billing information would be sent to another cell. For instance: Customer database (Sheet 2) A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX Invoice (Sheet 1): Search for the last name of customer (I don't know how, could be a function or something like that), it could be Anderson for instance, click on it and: the information in cell A1 in sheet 2 will be sent to cell D7 in sheet 1 the information in cell B1 in sheet 2 will be sent to D13 in sheet 1 How do I create a simple database with names, addresses and billing information and how do I connect it to the invoice? If you have any solutions I would really appreciate it! Thanks! Kind regards, Nic |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoice: customer database and billing information
Nic, I really kind of thought someone would chime in before now. What you
want is the VLOOKUP() function. Look at Excel's Help on the topic for more details than what I provide here. The basic format of the command is like this: =VLOOKUP($A$1,$C$1:$G$100,3,False) $A$1 would be a cell with a person's name in it that you want information about. Then $C$1:$G$100 might be the table you showed, with names in column C. This table can be on another sheet and would be referenced like: 'Sheet Name'!$C$1:$G$100 Of course the columns and rows involved can vary. The ",3," part of it is the trick. This is the piece that says: when you find a match (to A1,) in the first column of the table (column C) then return the value from the 3rd column of that table on that row, which would be from column E. The "False" says that the list of names in the table do not have to be in order. So you could have a cell ($A$1) for the name on your invoice sheet then VLOOKUP() formulas in all the other cells to bring over the address, phone #, email, etc. along with the invoice amount. Just by changing that ",3," piece of the formula. The $ symbols are to keep the addresses from changing as you copy and modify the formula into the various cells on your invoice sheet. Now, VLOOKUP() will return a #N/A error if it doesn't find a match in the table. That might be ok in this case because it'll clue you in very quickly that you typed the name into cell A1 wrong. But lots of times people don't want to see a bunch of #N/A entries cluttering up a worksheet, so we can hide those by 'wrapping' the formula in an error trap like this: =IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),"",VLO OKUP($A$1,$C$1:$G$100,3,False)) so when an #N/A would have appeared, the cell remains empty looking, or you can put words or values in place of the ,"", like this: =IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),"No match found",VLOOKUP($A$1,$C$1:$G$100,3,False)) or to return a value of zero instead of the error: =IF(ISNA(VLOOKUP($A$1,$C$1:$G$100,3,False)),0,VLOO KUP($A$1,$C$1:$G$100,3,False)) Hope this helps you out. "Nic Daniels" wrote: Sheet 2 turned out a bit strange...I try again: A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX -- Kind regards, Nic "Nic Daniels" wrote: Hi, I would like to connect an invoice sheet to a customer database (a database including names, addresses as well as billing information). I would be great to somehow be able to search for a customer in the invoice sheet, click on his/her name and the name + address of that customer would be sent to a specific cell and the billing information would be sent to another cell. For instance: Customer database (Sheet 2) A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX Invoice (Sheet 1): Search for the last name of customer (I don't know how, could be a function or something like that), it could be Anderson for instance, click on it and: the information in cell A1 in sheet 2 will be sent to cell D7 in sheet 1 the information in cell B1 in sheet 2 will be sent to D13 in sheet 1 How do I create a simple database with names, addresses and billing information and how do I connect it to the invoice? If you have any solutions I would really appreciate it! Thanks! Kind regards, Nic |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Invoice: customer database and billing information
By the way, I hope your whole name, address, zip, etc. really isn't in one
cell! That sure makes picking up the pieces of information very difficult. If they are, consider using Data--Text to columns to split it up better. "Nic Daniels" wrote: Sheet 2 turned out a bit strange...I try again: A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX -- Kind regards, Nic "Nic Daniels" wrote: Hi, I would like to connect an invoice sheet to a customer database (a database including names, addresses as well as billing information). I would be great to somehow be able to search for a customer in the invoice sheet, click on his/her name and the name + address of that customer would be sent to a specific cell and the billing information would be sent to another cell. For instance: Customer database (Sheet 2) A B 1 Anderson, J. ...Street, zip code etc $100 2 Bystrom, E. ...Street, zip code etc $34 3 XXX $XX 4 XXX $XX 5 Etc... $XX Invoice (Sheet 1): Search for the last name of customer (I don't know how, could be a function or something like that), it could be Anderson for instance, click on it and: the information in cell A1 in sheet 2 will be sent to cell D7 in sheet 1 the information in cell B1 in sheet 2 will be sent to D13 in sheet 1 How do I create a simple database with names, addresses and billing information and how do I connect it to the invoice? If you have any solutions I would really appreciate it! Thanks! Kind regards, Nic |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create a customer database with information flowing to another wor | Excel Discussion (Misc queries) | |||
Extracting invoice information into a client database | Excel Discussion (Misc queries) | |||
customer database for mailings and invoice analysis | Excel Discussion (Misc queries) | |||
How can I create a customer database to use with an invoice? | Excel Worksheet Functions | |||
customer billing | New Users to Excel |