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

adasca

New member
Joined
Apr 13, 2013
Messages
17
Reaction score
0
Points
0
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
 
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
 

Attachments

  • Test.xlsx
    15.2 KB · Views: 61
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
 
Thanks

I need some time to digest but will get back to let you know how I get on. Andrew
 
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. :smile:

Good luck

Hercules
 
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
 

Attachments

  • invoice templateP.xls
    104.5 KB · Views: 31
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
 
I think the formula approach requires you to dump the addressbook into Excel so you would probably require VBA for that.
 
Thanks - I do not have a problem getting the list into Excel. What I am wondering is how to interface the customer section of my order form so that it opens up a data entry box that allows autofill from the customer database as well as the ability to add a new record. I could of course have both worksheets open but it would be nice to have it linked. Am I making sense? Andrew
 
thanks Roy

see my reply to hercules as I have no problem with the database being in excel but would like to link the data entry when serving a customer to the existing databse.

Say you are my customer Roy Smith and there are 14 Smiths on my database I would like to be able to autofill your record if you are already on the database or to be able to create a new record if you are not from my order form template.

Thanks Andrew
 
This is confusing. You started with an outlook contacts list, now you will be using an Excel Database.

You have no skills with VBA so I don't know how you expect to create a form. You can check out the DataBaseForm here
 
Sorry Roy - I didnt mean to confuse. Whilst my customer list is in Outlook, if I had to export it to excel to make it work easily I would have been happy to. Thank you for the database form link which I will check out and thank you for the advice re VBA. It sounds as if I may need to get someone with experience of VBA to design the form for me.

Thanks again

Andrew
 
This is not commercially critical at the moment and I should work with the order form in situ for a while before committing to commissioning some professional work but would be able to get in touch through excel-IT I guess? Depends on the cost I suppose.

Thanks for all your input so far.

Andrew
 
Andrew
Have a look at the attachment. You enter your name in N5, and the idea is to get down to just one entry in the extract table which is the one that is referenced in the invoice. I created a dummy list of names and addresses that seems to work ok. If you need any help with getting the data from Outlook refer to the link I posted earlier.

Herc
 

Attachments

  • invoice templateP.xls
    219 KB · Views: 22
Thank you but I think I broke it on first attempt. I typed T in the N5 cell and hit return and came up with #NAME? across every cell in columns Q to AA.

I am running Office 2003
 
Thats a nuisance. It must be that Im using a function thats not in 2003. I will check out and get back to you.
 
Back
Top