Software

Ask something
Discussion Locked

Get addresses into Excel sheet

0 helpful
8 replies

by Member 33712138 1 view

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Im trying to create myself an easier way to do invoices. I've converted from Word to Excel to do this and started using templates, rather than just editing old invoices to create new ones.

The problem i have is that by using an excel template i have to enter the customers name & address each time i write an invoice, with takes a long time and effecitvely negates the entire point of this exercise - i.e to SAVE time.

What i would like to be able to do is import external data (the name and address mainly) from another excel sheet or someting like Outlooks contacts database straight into this excel template. kinda like you can do with a mail merge in word. I have access installed, but am a complete novice with that so id like to avoid it unless absolutely necessary.

If it helps ive got Office 2k and windows 2k.

8 replies

by dklippert

Don't give up on Word. You can export your contacts to Excel (Import and Export Export to a file and choose Excel as the format.
Use Excel to do the mathmatical heavy lifting and the merge to a well formatted Invoice form in Word.
As part of the merge you can specify particular records or records that meet a certain criteria.
Access can also do a great job relating numerous records and then be used as a Word data base.
Here's a good Access tutorial

http://tinyurl.com/o36t
from Florida Gulf Coast University

by an_it_guy

If you can get OfficeXP, it would help you a lot with this scenario. It has something called as SmartTags, which can help you.
You could enter contacts of all your clients in Outlook Contacts and these smart tags would help you auto-fill(kindof) for your contacts.
You need to find more info on this and see if it helps. Best of luck.

by mailbarrow

Assuming your database of addresses doesn't change too often, what about doing it this way:

1. Import all the company names and addresses into an Excel file (this could be an external file or simply part of your template).

2. Create a dropdown box that lists all the company names. You simply select from this and then your template formulas simply pull the other relevant fields from the database list into the template. These could be VLOOKUP's or OFFSET formulas.

Wouldn't take too much to do.

by firstpeter

It can be done - I've done something simliar with the Invoice template for a demo - but it's not quite as easy as it may sound.

Assume you have a spreadsheet with your customer information like so:
Column A Customer Names
Column B Customer Address
Column C Customer City
Column D Customer State
Column E Customer Zip

We'll call this saved file "Customers.xls".

If you open your invoice and go down BELOW where the invoice itself sits; let's assume row 500. Select cell A500 and go to the "Data" menu, and select "Import External Data" and then "Import Data". Select the "Customers.xls" file and hit "Next". Check the box if the first row contains headers (it probably should). Hit "OK" and make sure that cell A500 and "Existing Worksheet" are your locations. Hit "FINISH" and your data should download.

What this gives you is the data from the "Customers" spreadsheet - dynamically updated and pulled on-the-fly. That allows you to set up validation on your "Customer Name" blank to pull data from column A (Customer Name), and then do a VLOOKUP statement for the address, city, state, and zip as so (assuming there are 100 names in the list):

To get address:
VLOOKUP(,A500:E599,2)

To get state:
VLOOKUP(,A500:E599,4)

and so on...

E-mail me if you'd like more information or clarification.

by firstpeter

On the VLOOKUPs, before the closing paren, add ",FALSE" (without the quotes). That will ensure an exact match, not a "close match".

by jeanem

Here is what I did.

Create invoice on sheet 1.
Create customer list on sheet 2 with these 6 fields (or more if you need them): Number, Contact (last name, first name; ie: Mabry, Sam); Address, City, State, Zip
Sort list by contact.
Add a form combo box to sheet 1 (View/Toolbars/Form, click on combo box an draw a square on the form)
Set the combo box to reference to sheet 2
(Right click on the newly drawn combo box and select "Format control", go to the Control tab and set the input range to the Contact column on sheet two, ie: Sheet2!$A$2:$A$8 - don't include the header row), set the Cell Link to a cell on sheet 1 ie: Sheet1!$A$3. The result is as you select a name in the combo box, the row number of that record shows in A3. (Just double check to make sure they do match.)

Then do a VLookup on cell A3 on sheet 1 for the info needed to fill in the name, address, city, state, zip fields on sheet 1. (ie: Cell B4/Sheet 1 should hold the address, so formula in cell B4 is =VLOOKUP(A3,Sheet2!$A$1:$G$6,3)

So, by selecting a customer in the combo box, pertinent data autofills on the invoice.

I then do a sheet copy to a new book or an existing book. Then on the copy do a select all, paste special-values, delete the combo box and the number in cell A3. Actually I wrote a macro to do this last part.

This leaves my original for another day and I have copies of all my invoices. If I add more names to the list, I update the combo box control to reflect the new list range.

Hope this is of some help to you. I'd be happy to answer any questions you may have.

This discussion is closed to new replies.