1. General Description
It is necessary to create a system in Excel for quick generation of invoices.
I manually enter order data (from Viber), the contractor is selected from a list, Excel automatically pulls in their prices and quickly generates an invoice based on my template (I provide a photo).
Then I save each invoice, and it goes into the order history.
Everything must work quickly, without glitches, without macros that need to be confirmed daily (everything should be stable).
2. System Composition (Excel Sheets)
2.1. Sheet “Contractors”
Must contain a table with the following data:
- Store Name
- Address
- Contact Person / Phone
- Price Type (standard / individual)
- Individual prices for each item (if any)
There must be the ability to:
- add new contractors,
- edit,
- delete.
2.2. Sheet “Products”
Table:
- Name
- Weight
- Standard Price
- Code (if necessary)
Ability to:
- add a new product,
- edit prices,
- delete a product.
2.3. Sheet “Invoice (Form)”
This is a working form where I enter orders.
Functionality:
- Select a contractor from the list.
- Automatic filling of address, phone, price type.
- Automatic filling of prices (standard or individual).
- Entering the quantity of products next to the items.
- Automatic calculation of totals and grand total.
- Automatic calculation of “Amount in words”.
- Automatic assignment of invoice number.
- Date is entered manually once and remains until I change it myself.
After entering all data — button:
“Save Invoice”
After pressing:
- the order is recorded in the “History” sheet,
- the invoice number increases (+1),
- the form is cleared for the next order.
2.4. Sheet “History”
All created invoices are recorded:
- invoice number
- date
- contractor
- list of products and quantities
- total amount
- prices that were applied
- time of saving
Ability to:
- view
- filter
- edit from history (if necessary)
Creating an Excel system for generating and printing invoices
Must include:
- current invoice number (counter)
- last date, set manually
- service tables for formula/macro operations
3. Invoice Numbering Logic
Everything is as simple as possible:
- Date — set manually and remains until I change it myself.
- Invoice number — automatically pulled in:
- first = №1
- second = №2
- third = №3
… and so on.
The counter depends only on the number of invoices I have created consecutively.
4. Invoice Template (Important)
It is necessary to create a printed form of the invoice identical to my paper form.
I provide a photo.
Mandatory:
- preserve all frames, margins, structure
- preserve the appearance 1 to 1
- leave space for the logo
- print in the format:
A4 file, where the upper half is the original, and the lower half is a duplicate (template repeat)
that is, two identical A5 on one A4 sheet.
5. Printing
After completing the formation, all invoices should be printed in batches:
- 50–70 invoices at a time
- each — A4 with two copies of A5
- so that I can simply cut the sheet in half after printing
