Excel to database

[philmetz]

I have a problem I am not sure how to solve.
Basically its a invoicing system. I tried with all the products in the same excel file but then I realized if i update the product list it wont in all excel files for each customer. So i thought one external access database (or excel file) that I could refer to from an excel file.
So what I want to do now is when I want to add a new product to an invoice, I click a button (add) and it opens a small window with the list of products which has been taken from say an access database or excel file. Then I select the product and click insert.

How would I go about doing this? Are there any simple example I could follow?

[mubashir aziz]

it can be achieved thru Data validation , Vlookup and other methods but it will be better to place a sample file with some data and examples that what actually you need so that the users can easily figure out the best solution for you ....

[philmetz]

Ok check the attachment.
As you will see there are 4 sheets: Quotation, Invoice, Delivery, Products.
How it would work right now is id have one excel file per quotation per customer but as you can tell, if the product list changed, then I would have to update all the excel file product sheets which is well awkward.
Another thing the drop down menu is too small so thought if I could have a button and then a smallish window would open with the products and I can select. Would make it easier.
So Am thinking of a few things.
1) Have an external file that contains the product list and I click add product, a small window opens with the list of products from that file and I can select
2) Same as above EXCEPT with add features so that I only need one excel file and I can simple click a button so save or load the data to and from an external file.

What do you think?

[mubashir aziz]

The data and all the things are arranged properly but i think Why you are making files for every customer separate ??????
I think it will be better to make one sheet name Customer (contains: address, phone, fax etc) in same file and then in quotation file as you select Customer name against To: then PO BOX and other info get filled automatically from Customer sheet and you can select product like you are selecting.
Then if you want, you can save this file with Customer ID & invoice name ....

Your second idea is also good that you use other files range of product in a file while giving a name to range but i think first idea will be good for your as you will be able to update the list of product and customer at the same time.

[philmetz]

But you see if a customer wants a quotation I can create one. But then how do I save it, id have to save the whole file a lot of times. So i thought how can i create it so I can just click save and it saves what I have created in an external file. Then i can load the details back in if needs be?
The customer sheet is not important. Its saving/loading the data.

[mubashir aziz]

You are right and i was searching for file which i created ..... you should do following steps just like a database ....

Make a separate column sheet containing all the fields of invoice/quotation including invoice number and dates as well .... So whenever you want to create an invoice just enter whole info in that sheet and then by suing drop down menu you can call it thru invoice number and you will be able to keep data of all invoice in a single file and with less size..... Hope you got my point
But one thing are you only making these invoices for your record and send the hard copy to customer ????

[philmetz]

Bit confused what you ment there. Let me try clear things up.
I have the file (the one in the attachment).
Now a customer comes to me to get a quotation for some products yea. So i create a quotation in that file. Now I can print it out and give it to the customer.
Now what I need is some sort of way to click a button (save) and the information in the quotation is saved somewhere for reference. Thats where some sort of information storage (like database) comes into play. So say the customer likes the quote. He/She comes back to buy the products. So I click load quotation (and search for the one I made for him) then click on the invoice tab and can print him/her an invoice.

So i need someway of saving the information and loading it. But havent a clue how I would do that.

Makes more sense?

[mubashir aziz]

Just check this file. if it makes any sense to you ......

[philmetz]

Ummm hmm yea something like that but adapted to the file I got. I need to have multiple products. and I need quotation, invoice and delivery note sheets there. I just have no idea how to apply that to mine.
I have to be able to save a quotation from my file and be able to reload it.

[mubashir aziz]

Have a look on this thread as well ............

http://www.excelforum.com/excel-gene...ook-again.html

Just apply the same approach on your sheet and no need to change your all 3 sheets qutoation , invoice and delivery ... only apply on invoice sheet and linked the remaining sheet with it ....
I"ll try to work on your file ...... But the idea will be the same

[philmetz]

Thanks. Well it all starts at quotation and invoice and delivery note are linked to quotation. So only need to apply to quotation.

[philmetz]

Will what you described make it possible to save a quotation sheet and be able to load it again all in the same file?
Have got to be able to save all the info. Not sure how that would be done.

[mubashir aziz]

See attached file. Hope it will give you a clear idea how to work in excel like a database.

[philmetz]

hmm yea thats kind of what Im looking for just a little too much data, like the item number. Do i need that?
Also the convertign between 2 currencies I have is gone. Ill have a play. Its good but the formatting I had is gone which was important.

[mubashir aziz]

Items number are essential because if you have more then one items in Quotation but if you don't have then it will be very easy.
Remember you have to add all columns in Enter Data sheet so invoice and delivery note sheets will be updated automatically ...... for converting purpose you have a add another column in enter data as well ........
Just try to understand the concept and remain will be very easy .... do update me about your further work ....

[philmetz]

Alright ill be playing around, ill show you what I come up with.