Help in designing an invoicing database.

Good morning all!

Okay - so I have this workbook. My supervisor wants us to have an excel invoice, but then wants all of the info inputted in the Invoice to self populate, building two (2) separate sheets of databases.

I know I need macros, I know I need links. And honestly, I haven't worked on stuff like this in a really long time, and I am completely out of practice. I know this is all possible.

Can someone help me please? Please.

I'm continuing to power through, however, I am uploading the base of this project here, in hopes that while I struggle bus, that some magical Excel Knight will come to save me.

Anyone, Bueller?

Hi and welcome to the forum.

Please note our rules about thread titles which should give some indication of what you want. Since you are new here I'll change it for you but please note for the future.

Original Title. Out of Practice -HALP!

First question.
Is the Invoice Database meant to hold the details of the Total invoice cost on a single unique row?
At the moment it caters for individual part numbers suggesting that you might be thinking of a row for every line item on an invoice rather than the total for the invoice.

If the Invoice database is meant to hold line item details then I suggest that this should be the input sheet where you capture the details for the invoice. Typically I'd have a single row above the database in which you enter every line item, then click a button to add it to the database. When you want to create an invoice you could select an invoice number from the database in a cell drop down and then have a button to copy the relevant details to the proforma invoice - alternatively I often use a double click event on a database row to achieve the same effect.

The Vehicle Database is presumably a stand alone set of data and other than the customer name / service date and would not change unless an Invoice was just for a service. Is that correct or would every invoice generate a record on the vehicle database?

If I understand the requirement the key to this boils down to where do you start capturing the data.

I suggest it's captured directly in the Invoice Database sheet. As I said it's more efficient to have a single dedicated date entry row above the data where you would enter a line item. I suggest you also include the comments box as a field in the database.

Typically you'd hold a table of part numbers, part costs and perhaps labour rates in separate tables on another sheet, along with a table for customer name and ID so that in the data entry row you could have data validation cells to pick from the tables.

Then you'd use a macro to transfer the data entry row to the database, zero the data entry row and onto the next line item number, and when you've completed all the line items for an invoice have another macro that would populate just the Invoice with just the relevant lines from the database.

That would be my preferred method. You could of course do it the other way around and add details to the Invoice Database with a macro. The way I'd do that would be to have a macro loop down the invoice rows and transfer each row to the Invoice database. I think that's slightly more complicated than the first approach

If you have it available, I would urge you to consider doing this in Access which is a Database and may at the end of the day be more productive for you. Look at this link for a basic tutorial on Data Bases.

http://www.accessmvp.com/strive4peace/

and the attached.

Why not read/write to access from excel. I kept away from this for years and now its a firm favourite of the bosses here..

Invoices printed from an excel template - all record details stored in access

Access or some other database is the way to go. However, with a bit of coding Excel can be made to behave like a limited database (very little flexibility).

I've done this kind of work before, but you'll have to be patient with me because my time is limited.

Here is what you will need:

A Customer "Table" with the customer's phone number as the primary key - Once the customer has given you this number, they are stuck with it even if they change their number. You can have a secondary field for primary contact number. You will need functionality to add new customers, look up existing customers and edit existing customer information. You might also want functionality to delete customers.

A vehicle table that contains the vehicles a customer owns. You will need add vehicle and delete vehicle functions for this table.

An invoice table to summarize work done. The invoice needs to be triple-indexed so that it matches customer and vehicle. I assume that you would want to be able to pull up invoices for tax purposes or pull up open invoices by customer or pull up all invoices by vehicle so you know what past work has been done on a car.

Most of this is done automatically in a database. All of it has to be programmed in Excel. It's a big project. I might do a generalize version of it so anyone with a repair shop can use it, but it would be a "spare time" project with an indefinite completion date. I will touch base with you if I get it completed.

BTW: I grew up on the corner of Willoughby Street and St. Nicholas Ave. It's about a mile hike from your shop.

Here is a "Proof of Concept."

This has mainly been an exercise in Excel Forms. I was a bit rusty on them so it did me some good.

'Bare bones framework' - Default tables with 'Customer/Vehicle' I.D. is VIN # - 'Parts Table' and 'Labor Table'
Button in 'A1' of Customer & Parts tables brings up data entry form - to add an entry first press 'Clear' - add your data then press 'Add' - the rest should be intuitive.
To enter an invoice first press 'New Invoice' this clears the form and fills the next invoice number - then click in VIN (yellow cell) - selecting VIN fills in customer associated with it.
In column 'B' select yellow cells to input your 'parts' then fill yellow quantity box - similar for the two yellow 'labor' boxes in column 'B' then fill the labor hours.
The cursor should go to the next cell after selecting item in column 'B' complete each row before going to the next.
There are no on-sheet formula - the sheet should calculate at each row stage.
Have a trial, there is very little error checking code as it is just the operation concept that is of interest at this point in time.