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

[adasca]

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

[Hercules1946]

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

[royUK]

Have a look at this

[adasca]

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

[adasca]

Thanks

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

[Hercules1946]

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

[adasca]

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

[adasca]

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

[royUK]

I think the formula approach requires you to dump the addressbook into Excel so you would probably require VBA for that.

[Hercules1946]

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