Fill in address on invoice from contact list

[jen]

I’m trying to easily fill in a clients name and address into an invoice from a separate sheet that has a list of all my clients. Client List is standard name, address, city… in header. I have figured out how to have a list of client names on a dropdown list but cannot figure out how to fill in the other cells based on the name chosen from the dropdown.
This is how it looks on the invoice and Contacts sheet:

On invoice, cell E14=name, cell E15=address 1, and cell E16=address 2
I hope I’ve made sense of what I’m looking for, I cannot seem to find exactly what I want in searches and no way can I code this on my own.

Thanks in advance!
Jen

[joshua4]

You might edit your question and add an upload of your workbook with a few fake client entries. A spreadsheet program really isn’t the path of least resistance for doing invoicing. But I’ve attached a simple version that does the trick for this part.

The key is the following formula for the name/address header on the invoice:

=INDEX(ClientData,MATCH($F$4,INDEX(ClientData,,1),0),MATCH("Name",OFFSET(INDEX(ClientData,1),-1,0),0))

This finds the column related to the column name in the header for the client data then combines that with the row of the selected individual in the client data to pick out the required datum.

I’ve made several quick choices, such as naming the client data without the header and using OFFSET rather than including the header and subtracting 1 from each lookup. Taste varies on such things. But in general this approach lets you add more columns then look them up by name in quotes rather than having to worry about the position number of the column changing, such as if you insert “Company” between “Name” and “Address 1”.

Simple Address Finder.ods (12.3 KB)

[Wanderer]

Just a general remark: Depending on the size of your business you will face some day the task to handle two customers with the same name. Searching for the adress using the name will make it impossible to invoice one of them.

As a work-around one can introduce an UNIQUE id and add it to the name-selection or use it instead.