Invoice: customer database and billing information

[Nic Daniels]

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

 

[Nic Daniels]

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

 

[JLatham]

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)),"",VLOOKUP($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,VLOOKUP($A$1,$C$1:$G$100,3,False))

Hope this helps you out.

 

[JLatham]

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.