Creating variable length invoices with Excel

Question

I am trying to create an invoice that will include a variable set and qty of part numbers.

As part of the spreadsheet, there is a complex configurator tool using a number of inputs to determine a variable set of part numbers that are required.

The result of the use input is a large table of part numbers with qty added for each required part.

The problem is how to only include the selected parts and qty on a final invoice table on a separate sheet.  I've seen this work before but am not sure what Excel functions to use to make this work.

I've been searching under a number of terms but can not seem to come up with the right description of what I want.

Can anyone point me in the right direction of how to do what I am looking for?

Thanks

Bill

Answer 1

You can use Excel as a data store, but even a modest business requires quite a bit of thought when setting up a database. You'll need a separate tab with a workbook for each table. 

Microsoft has a very simple example database called NorthWind that helps you learn the basics of making a relational database (which is what you really need here). Here's a screen shot of just one table from NorthWind in Microsoft Query on the Mac:

You will need to customize your own database with the columns you need. You will need a transactions table. You will need to track billing cycle begin and end dates. You will need queries to generate a report that has the transactions made for each customer during the billing cycle. You will use Word Mail Merge to generate your invoices.

There is a tutorial to get you started here:

http://www.agentjim.com/MVP/Excel/2011Relational1Intro.html

Most businesses wind up using a commercial database product that has a lot of the core work already done. Nevertheless, this is a very do-able project in Office for Mac should you want to take the time to really learn how the applications work with each other.

Answer 2

@ Tina - It is unreasonable to expect WAK500 to know about my answer because you still haven't marked it as Answer. Unless WAK500 is clairvoyant (which is not possible) or has subscribed to this question (which is highly unlikely) no notification will be sent to WAK500 unless you do your job as moderator (or some other moderator happens to come along does it for you) and mark my reply as Answer.

Answer 3

Hi Bill,

Have you checked Jim's suggestions? Let us know if you need further assistance.

Thanks,

Tina

Answer 4

Hi Bill,

Based on your description, you may consider using PivotTable to analyze worksheet data. Please pay attention to the sections Working with the PivotTable Fields list and PivotTable Values.

If you have any concerns, to better assist you, could you please share with us some screenshots with fake sample raw data, and a screenshot of the desired result?

Thanks,

Tina