Page 1 of 5 1 2 3 ... LastLast
Results 1 to 10 of 41

Thread: have order form in excel 2003 and want to link customer details to an address databas

  1. #1

    have order form in excel 2003 and want to link customer details to an address databas



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi Iam new to this and hope someone can point me in the right direction. I have a customer order template in excel 2003 and I want to be able to link the customer details section of the template to an external database which is currently an outlook contacts list.

    Say I have a customer, Mr Wright, I would want to be able to type in Wright in the surname cell on my order template and for it to, either show me all the Wrights on the Outlook contacts list and allow me to autofill the details into the template or, if not there, to be able to enter his details creating a new customer record at the same time.

    In an ideal world it would be preferable to keep the customer data on the Outlook contacts list but it would be acceptable to export the OUtlook list to a worksheet that could link to the template.


    I am not really familiar with Access but if there was an easy way to import the excel template into Access and handle the project within Access then I suppose I might be prepared to go that route but for the time being if I can solve within Excel then at least I am on more familiar territory.

    Thanks

    Andrew
    Chichester
    England

  2. #2
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Hi Adasca
    Have a look at the attachment, which shows an array formula that should work if you can inport your database into Excel. In my example, your database would be columns A to E, which is a list of employees. Details of all database records for a given name (cell J18) are placed in the extract table (your template?) - Cols J to M. It can handle multiple rows and columns from the database. You can either enter the lookup reference (name) or select from a drop down list as in the example. If you haven't used an array formula before, don't worry. They are identified by the curly brackets around the formula, which are put in place by Excel as a final step when you have entered or amended the formula. You instruct Excel to complete this step by pressing the key combination Control+Shift+Enter.
    Once you have the array formula however you can copy and paste it to other cells normally including the curly brackets.


    HTH Hercules
    Attached Files Attached Files

  3. #3
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    Have a look at this
    Hope that helps

    Roy

  4. #4
    Quote Originally Posted by Hercules1946 View Post
    Hi Adasca
    Have a look at the attachment, which shows an array formula that should work if you can inport your database into Excel. In my example, your database would be columns A to E, which is a list of employees. Details of all database records for a given name (cell J18) are placed in the extract table (your template?) - Cols J to M. It can handle multiple rows and columns from the database. You can either enter the lookup reference (name) or select from a drop down list as in the example. If you haven't used an array formula before, don't worry. They are identified by the curly brackets around the formula, which are put in place by Excel as a final step when you have entered or amended the formula. You instruct Excel to complete this step by pressing the key combination Control+Shift+Enter.
    Once you have the array formula however you can copy and paste it to other cells normally including the curly brackets.


    HTH Hercules

    Thanks you.. Can I get back to you once I have had a chance to play with it? Andrew

  5. #5
    Thanks

    I need some time to digest but will get back to let you know how I get on. Andrew

  6. #6
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Andrew
    Great - If you have any problems, let me know. If you want to upload some sample data, I can try to adapt my example, though if your like me, I like to do it on my own.

    Good luck

    Hercules

  7. #7
    Thanks Hercules, can you send as an Excel 2003 file as the file conversion has restricted functionality? I thought it would be helpful if I attached my order form so you can see the section for customer details.

    say i have a customer george smith, i would need to be able to start typing smith in the name field and for the form to give me several records that i can choose from if he is on the databse and if not for me to be able to fill in george smith and (hopefully) his details to be added to the databse.

    Thanks again Hercules
    Attached Files Attached Files

  8. #8
    Thanks Roy but I have no knowledge of VB and do not really have the time to go through a steep learning curve. Will it take time do you think as I am not really a programmer?

    Regards Andrew

  9. #9
    Conjurer royUK's Avatar
    Join Date
    Mar 2011
    Location
    Derbyshire, UK
    Posts
    155
    Articles
    0
    Excel Version
    most versions
    I think the formula approach requires you to dump the addressbook into Excel so you would probably require VBA for that.
    Hope that helps

    Roy

  10. #10
    Magician Hercules1946's Avatar
    Join Date
    Mar 2013
    Location
    York, England
    Posts
    772
    Articles
    0
    Excel Version
    2010
    Have a look at this for getting your contact list into Excel. I don't use Outlook contacts to any extent, but exporting to Excel is possible and doesn't sound too difficult.

    http://www.howto-outlook.com/howto/exportcontacts.htm

Page 1 of 5 1 2 3 ... LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •