General discussion

  • Creator
    Topic
  • #2312882

    Get addresses into Excel sheet

    Locked

    by simon@srn ·

    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.

All Comments

  • Author
    Replies
    • #2742218

      Reply To: Get addresses into Excel sheet

      by maxwell edison ·

      In reply to Get addresses into Excel sheet

      To import data into Excel:

      Take a look at these links. (Cut and paste into your browser.) They will provide the information yo need to do that.

      http://tinyurl.com/o1rw

      How to import access tables into excel:

      http://tinyurl.com/o1s8

    • #2743242

      Reply To: Get addresses into Excel sheet

      by dklippert ·

      In reply to Get addresses into Excel sheet

      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

    • #2743220

      Reply To: Get addresses into Excel sheet

      by an_it_guy ·

      In reply to Get addresses into Excel sheet

      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.

    • #2743099

      Reply To: Get addresses into Excel sheet

      by mailbarrow ·

      In reply to Get addresses into Excel sheet

      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.

    • #2742191

      Reply To: Get addresses into Excel sheet

      by firstpeter ·

      In reply to Get addresses into Excel sheet

      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.

      • #2742173

        Reply To: Get addresses into Excel sheet

        by firstpeter ·

        In reply to Reply To: Get addresses into Excel sheet

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

    • #2737635

      Reply To: Get addresses into Excel sheet

      by jeanem ·

      In reply to Get addresses into Excel sheet

      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.

Viewing 5 reply threads