When building Numbers spreadsheets it is important to remember that a row in a table is the equivalent to a record in a database. To build a billing system, you can put clients in one table and invoice items such as billable hours in another table. Then you can build a sheet with tables that use functions to populate an invoice. The requires some advanced functions like LOOKUP, INDEX, IF, AND, COUNTIF and more. But once you are done, you can easily add more records and create invoices.
Check out Creating a Billing and Invoicing System In Numbers at YouTube for closed captioning and more options.
Download the sample numbers file.
Great post. Would it also be possible to create a filter for the Client ID field in the Invoice sheet so only the clients with open items can be selected from a drop down list?
I am coming from an excel background and just getting used to Numbers.
Craig: No. The Client ID cell is just a cell. You will be able to type anything you want in it. There's no function to "restrict" it in some way. But I don't see that as a problem... what's the issue?
Would like to see more of the same.
Thanks Gary. No issue, it was just a thought.
Keep up the great work!
Love this, great insight in those functions.Thank you
While this demonstrates insightful ways to use Numbers, wouldn't this be a more a suitable and straightforward task for FileMaker or other database app?
Tim: I agree that things like this are best done with dedicated billing software (SAS mostly now) or database software. But a lot of people don't want to pay what those solutions cost. And if you do a little consulting on the side, it is nice to have a free option.
Can we put passwords on various sections of a spreadsheet
Vikram: No. You can use the Lock command to lock cells, if you are afraid of people accidentally changing a value or formula. But they either have access to the spreadsheet or they don't.
Ok thanks Gary. How do I associate a percentile of growth chart of a child to his data of height and weight so that when I enter the data it gives me the percentile of ht and wt for that particular age.
Vikram: I'm not familiar with that type of spreadsheet. Are you comparing a ratio to a list of numbers? If so, LOOKUP, or VLOOKUP is probably what you want. If you know of an example I could look at, you may want to ask it at the forum (link above in the nav bar) and I could answer it there -- I want the comments here to stay more on-topic.
Ok, thanks Gary
Hi Gary. I've followed this tutorial real close but I'm struggling with the Due Flag column when it comes to adding it to the countif formula. I keep getting an error message saying I can't add the column. I've downloaded your example but can't see where I'm going wrong!! Can you help coz I like this invoice method using Numbers. Thanks. P.S. Love the Numbers tutorials. The more the better!!
Steve: What are you doing, exactly, to add the column to the table? And what, exactly, is the error message?
Hi Gary. I'm just trying to copy the formula you use to look for the blank Paid cell in the IF AND statement and I'm trying to add the DUE FLAG column in the COUNTIF part of the formula. Im getting an error message saying I can't use the cells that are already being used in a calculation (or words to that effect)!! On most of your videos you build the formulas as you describe them, which I find very easy to follow! Maybe on this occasion I'm just missing something simple!
Steve: I would just compare what you are doing to what I have step-by-step. It sounds like there are differences between your spreadsheet and mine, but you are trying to use the same exact formulas. COUNTIF cannot include a reference to itself, for example.
This demo was GREAT / OUTSTANDING / OVER THE TOP! Thanks Gary, you are brilliant!
Can I use ₹ symbol instead of $ in my numbers invoices?
Umesh: Of course. Just choose Currency as the data format and then under that switch from US Dollar to Indian Rupee.
Issue: Header Column
Why do you use header columns (instead of regular columns) for the client ID in the CONSULTATIONS and CLIENTS tables?
Issue: absolute reference in LOOKUP formula
Why does the LOOKUP formula on the the Consultations sheet (ID cell) contain an absolute row reference, but on the Invoice sheet, the LOOKUP formula in the name cell does not contain an absolute reference?
D. Sands: When you use a header column for a critical but of data, like ID, it will remain as you scroll horizontally in your table. Not something I show here as this video is about something else, but it is useful.
I can't remember why I included an absolute column reference in the Consultations sheet Name column. It isn't needed in the Name cell of the Invoice as there is only one cell there. Absolute references are useful if you copy/paste/duplicate a formula to other rows and columns but don't want the reference to change. With one cell in the Name table in the Invoice, there is no chance for that. See https://macmost.com/relative-and-absolute-cell-references-in-numbers.html