Excel Invoice

[DuMoore]

I would like to create an invoice entirely in excel.(Data and invoice layout) Is there a way to make each record in the table merge into the invoice and print on separate sheets?

Thanks so much in advance for your input.

 

[SkipVought]

DuMoore,

Depending on the organization of you data and your invoice, it may be as simple as using the Subtotal Wizard with page breaks selected or somethin more complex that uses coding similar to faq68-4223 Mail Merge in Excel.

Tell us more.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com

 

[fkegley]

I would probably construct the form itself as a Word MailMerge document. Then tell Word to use your Excel spreadsheet as the Data Source. Then just run Mail Merge to produce your invoices.

Frank kegley
fkegley@hotmail.com

 

[Zathras]

If you truly want it ENTIRELY in Excel, take a good look at the INDEX function. If your data are in a "normal" Excel table (or list), then designate a cell to contain the row reference and populate your invoice "form" with INDEX functions referencing the appropriate columns and the cell with the row number.
Change the row number and print.
Change the row number and print.
Change the row number and print.
Change the row number and print.
etc.

You should have no problem recording a simple macro to do that for you. (with just a little bit of tweaking of the recorded code.)

However, give serious consideration to the above recommendations using mail merge. You may find that path easier to tread.

 

[DuMoore]

Thank you all for the help. The problem is getting the tables in normal form. I've slept a few times since my database theory class in college. I think I need a couple of tables...please let me know if i'm incorrect.

Table 1
Customer Table(id, name, address, etc. etc.)

Table 2
Product Table(id, name, description, price)

Having said that I know that I should be doing this w/ a database program like access or alpha, but my customer does not have either of those pieces of software.

I like the idea of merging everything into word. I do mail merges all the time, so that shouldn't be a problem. I guess my question is this: how do I normalize the tables, yet still make input easy for the customer.

Thanks,

dm

 

[Zathras]

Is you "Product Table" a master table of just product information as the name implies? If so, where do you have the stuff from which to make up an invoice? Something that combines date, product ID, quantity, price and customer ID?

Call that the SALES table. The use the SALES Table as the primary driver for the invoicing operation (with INDEX formulas) and use VLOOKUP formulas in the invoice "form" to get the appropriate descriptions from the other two tables.

It would be simpler in Access, but it can be done in Excel if you stick to it.

 

[SkipVought]

You can use Excel tables as a database and join then as you would any other table using MS Query as a means. Save you Excel database with a table on each sheet. Then use your Invoice workbook or even the SAME workbook, for that matter to query the Customer and Product tables, returning the results to the Invoice sheet. Then Macro Record editing the query and massage the resulting code to accept the parameters required.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

Skip,

http://www.TheOfficeExperts.com