Excel Expert needed

Lifeisbutadream

I asked this question a while ago on here, but I think I asked the question wrong, so didnt really get the answer I was looking for, so I am trying again

I run a small business and have set up a 'quoting' spreadsheet and an 'invoicing' spreadsheet. Each sheet has a list of customers as a page in the worksheet, and I have set up so that I can click on a drop down list to get the customer details.

I have to re-type the details in when I want to invoice as the quoting sheet is seperate.

My question is, is there any way that I can set up a 'central' customer list and be able to pick it up from other worksheets?? if not, does anyone have any suggestions as to make life easier?

(even perhaps purchasing a system that does it all for me!)

isofa

You could, but you are using the wrong technology for the problem.

You need a relational database, with a table of customers, linked by IDs to invoices and quotes.

sandra_nz

Yep, a relational database is what you need. Would probably be worth picking up a copy of Microsoft Access if you don't already have it installed.

The good thing is that they have several 'templates' and you will probably find one that will do the job for you!

espresso

If you want to use Excel, your customer data can be stored in one worksheet and referenced by the quoting and invoicing worksheets.

Lifeisbutadream

espresso wrote: »

If you want to use Excel, your customer data can be stored in one worksheet and referenced by the quoting and invoicing worksheets.

How do I do this??

Lifeisbutadream

isofa wrote:

You could, but you are using the wrong technology for the problem.

You need a relational database, with a table of customers, linked by IDs to invoices and quotes.

sandra_nz wrote: »

Yep, a relational database is what you need. Would probably be worth picking up a copy of Microsoft Access if you don't already have it installed.

The good thing is that they have several 'templates' and you will probably find one that will do the job for you!

Thank you. I dont have Access - how easy is it to add it??

espresso

Lifeisbutadream wrote: »

How do I do this??

Press F1 for help or Google e.g. here

PhilCo_2

If you combine your invoice & quote files into one you can use the same customer list to fill in both using a combination of dropdowns and vlookups to bring back the required data. Once you've done a quote or invoice you could copy the sheet to a new book without the formulas or create a pdf file of it.

Lifeisbutadream

PhilCo wrote: »

If you combine your invoice & quote files into one you can use the same customer list to fill in both using a combination of dropdowns and vlookups to bring back the required data. Once you've done a quote or invoice you could copy the sheet to a new book without the formulas or create a pdf file of it.

How do you copy to a new book without the formulas? I dont have PDF.

PhilCo_2

You can either
1) highlight all the sheet by clicking the grey square before A and above 1, copy, go to a new file, right click on the mouse and click Paste special values & format.

2) right click on the sheet tab name, select Move or Copy. In the To book: select new book and tick Create a copy and Ok. Then in the new copy highlight all and copy paste special as above.

3) create a macro to do the above and attach it to a button on your quote/invoice sheet so that you just need to click a button to do it.

Thomsk

Lifeisbutadream wrote: »

How do I do this??

=vlookup would be a good place to start. Excel help will show you how.

Thom