Creating a Billing and Invoicing System In Numbers (MacMost

[@patricksmit2982]

Thank you Gary for this tutorial. Based on this I created a Due Invoice tracking system. Only once a week I have to run and print the reports now. Enormous time consuming efforts are ended now. Thank you for pointing in the right direction.

[@photonatjag]

I've just gone from excel (office 2008) to numbers as my previous mac died. This and a few of your other tutorials has really helped. I certainly won't need to consider the microsoft subscription to get excel now you've shown what I need to use numbers. Thank you.

[@utnietje]

Thanks so much for this. I tried this and after seeing this video several times over and over again I got it. Now I can make nice invoices, I used my logo in it and I placed the adres with the name. Super, you helped me a lot.

[@solkinnis3574]

Thank-you! Your tutorials are so helpful and easy to follow. They’ve really helped me with creating the systems I need for my small farm.

[@amhjetten]

Thanks, I will try some of the useful formules

[@jghutzel537]

The new format is better than the old one

[@bunniesforever5731]

Enjoy your videos Gary. I have learned a lot about utilizing many mac features. Still struggling with the lookup feature in numbers. In my invoice sheet, my formula only reads one row.

[@matthewackatz2102]

I am a brand new Mac user. I am going to use it for a small business. The invoicing with numbers really looked awesome. Could you dumb it down for a newbie like myself?

[@macmost]

I think this is as basic as it gets. Just don't try to take it all on at once. Maybe learn some basic Numbers skills first. Then maybe try just parts of this if the whole thing is too much.

[@saulmejia5443]

Can I add and do you recommend I merge this billing system with your basic spread sheet tutorial? and finally how do I do that?

[@szfarrah]

Your videos are super helpful! I have a document will all my product info(sku, name, price). Would I be able to auto fill an invoice with a drop down of all the products name I offer and the rest of the information auto fills from the document with all the information?

[@macmost]

You can format the cell as a Pop-Up Menu if you like, but you'd have to add new products to it manually. Easier to just use the autofill function that is built in. For filling in the other data, use Lookup Tables. See kzbin.info/www/bejne/gH68eX5re7-IoLc

[@frankvet1]

I'm working through the formulas... I'll get there but, a) to make the filter a ROW, you have to give the table a column and row header (otherwise it only allows a Column filter), and, b) when I filter by row "greater than zero", my table does NOT include the total at the bottom since column A has a blank in that row. (did you put a value in the last column and change the text to WHITE by any chance?). Thanks again. Great fun working through the formulas.

[@martinschulze4578]

Vielen Dank! Would be great to show how to to calculate VAT and shares:))

[@BleddynMerddyn]

Hi, thank you so much for the videos question how can I add another column to put a description of work to the invoice? Thanks again

[@macmost]

Just insert a new column wherever you wish.

[@Chrisdgallego]

Thanks for this tutorial. Probably because of my poor knowledge about numbers and my poor English, I'm not be able to applying it. Do you have another tutorial where you apply this technique? just to try with a different example. thanks. Edit: In other words, what formula is the one who brings the different rows from Consultations table into the Invoice one? Because I just can bring the first row for each client.

[@donaldgendron1658]

Need help...Trying to set up an Invoice, my" LOOKUP" Function is not working, the caution sign says" Cell 2 has an error" I have an ID number There (1; one). Where should I put the functional how do I fix this problem? I am working on a Mac the OPS is Big Sur (11.4)

[@scooter3026]

Got a headache after 2 minutes LOL..

[@ghgomez2]

Thanks, but how do you protect the formulas from being overwritten by the user? I guess what I am asking is there a way to lock a cell, row, or column individually? Thanks again. you are great.

[@macmost]

You can't lock cells. But you can lock tables. So you would have to design your document with that in mind. Data would be in unlocked tables and formulas in others. May just be easier to trust/educate the user.

[@altwellhealth]

This was great but I am trying to figure out how to generate invoice numbers that increase sequentially with each invoice I create. Right now I just duplicate the invoice and go back to the previous one to add the number. I would much rather generate a new invoice with all the same info except for the current date, date range, and invoice number. I'll look on your website.

[@macmost]

How often do you create invoices? You probably can build a system, but unless you are generating many invoices every single day I don't see that saving you any time over just manually creating a new invoice number. If you really need it, maybe it is time to move to a pro billing solution that others in your industry use?

[@rosieschannelforkids9969]

Hi there, this is a great tutorial and when I first downloaded the spreadsheets they worked beautifully. Now something has gone wrong and I can't get the invoice to fill more than one row, even though the consultations sheet shows more than one due flag for that client- any advice?

[@macmost]

Maybe start over from the beginning with a new download? Hard for me to guess where you may have gone wrong.

[@benswire4282]

Hi Gary, enjoying the tutorials, but having a problem with the find value command I don’t have this option. Only have find or findv and can’t get the invoice working.

[@macmost]

The Find command? Not sure what you are referring to. Can you point me to a time in the video?

[@macmost]

@@vivianb5777 This is for Numbers, not Google Docs, yes. So things will be different in Google Docs. Not sure how as I've never tried it.

[@noremac022]

Hey Gary, thanks for your vids, they’re awesome and I’m learning a lot from your vids. Do you have any vids that steps through using the LOOKUP function with calculations in it also? I’m using the LOOKUP function to populate a field depending on the value of another field, but once the other field reaches an upper limit, it has to perform a calculation rather than retrieve a number from within the table the LOOKUP function uses. For example, Column A row 1 is a currency field with the LOOKUP function Column B row 1 is a currency field that fluctuates based on end result of SUM function LOOKUP TABLE Column C & D row 1 is a range, ie C1 = $10000.01, D1 = $25000 Column C & D row 2 is a range, ie C2 = $25000.01, D2 = $50000 Column E row 1 is a currency field with a fixed amount, ie $10 Column E row 2 is a percentage field with a fixed %, ie 0.15% Obviously there are more rows that make up the range but I’m keeping this as short as I can but descriptive enough to make sense (I hope) Once a number is entered into B1, the LOOKUP function in A1 retrieves the relevant $ amount from column E, however if the number in B1 hits an upper limit, rather than retrieving the fixed amount from column E through the LOOKUP function, it needs to calculate the number from B1 by the percentage number in E2. I’m sure it’s a matter of having SUM/SUMIF within the LOOKUP function but everything I have tried fails with syntax error. Really hoping that makes sense and I’d be stoked if you could point me in the directing of any vids you have on such problem. Thanks again for your vids, they’re great!

[@macmost]

Sounds like you need to use IF functions and LOOKUP functions. I cover the LOOKUP function many times. See kzbin.info/www/bejne/gH68eX5re7-IoLc and kzbin.info/www/bejne/en6roIKnptdjrZI

[@macmost]

If you are still having trouble, ask in the "Forum" at macmost.com as it is easier to answer these types of questions there.

[@noremac022]

Thanks Gary, I checked out those vids and they’re not quite what I need. I’ll check out your forum page. Thanks again.

[@amhjetten]

Hi, it’s me again. I tried this formula and it works. But I got one thing, I copy the formula but then on the blank cells (no number in it) I have a warning sign. But when I type a number it will show the right client. Is there a solution so that the cell will be empty and not showing something without deleting the formular?

[@macmost]

The best way is to simply not have empty rows. Only add rows when you need them for a new entry. But otherwise, learn and master the IFERROR function and wrap your formula in that.

[@misstahnia]

Thanks for the Tute, I've created a client table that contains address details, what is the best way to include the address on the invoice?

[@macmost]

Look at how I include the name in this example. Add a column to that table with the address and do the same thing with the address to get it on the invoice.

[@misstahnia]

@@macmost thank you, after I messaged you I worked it out. My flag count won't count above 2 IF(AND(A5=Tax Invoice::Table 1::$B$1,ISBLANK(J5)),1+COUNTIF(K$1:K4,">0"),0)

[@md1997]

I have a question, I have two lists, one for client lists where each row contains specific client information (address, amount of square feet I used, etc). I have an invoice sheet that is looking up their information to make invoicing easier for me. It calculates the square footage and does some math for me. Once I have my invoice, how can I have those billed numbers and date invoiced, be transferred back to my list?

[@macmost]

No easy way to do that since the invoice sheet will change the next time you use it. So you'll just have to copy the data over to another table with that information. At some point it gets complex enough that you really want to consider using pro billing software instead.

[@AllenReza]

Hello, thanks for the video! But wanted to point out that I can error message for the formula you used in “Due Flag” column in the Consultations table. The error message says the following: “This formula can’t reference its own cell, or depend on another formula that references this cell.” This is happening for the CountIF function using H$1::H11 range in your example. I would appreciate any suggestion to fix this error. Thanks!

[@macmost]

Check your formula to make sure the cell references in it do not include the cell itself. Look closely at how I do it.

[@proteus1]

Hi, how would i put in a £ symbol, instead of the $ sign. Many Thanks On th Invoice tab, i can't unhide columbs ? How do you activate this as the video shows you doing this, but there must be smething hidden in my Numbers area.Also is there a way to add a columb that takes into acount parts used for a job?

[@macmost]

Table, Unhide All Rows. As for using £, just type that, or change the cell formatting to use that symbol instead.

[@LucaSpina]

Thanks for the video. Is there a way to automatically create a sheet per invoice? Or is there a way to print all the invoices or export all of them in a pdf file?

[@macmost]

This method doesn't create a sheet, it just displays the invoice in a sheet. If you print (save as PDF) the sheet then you can keep that as a copy while also sending it along. But each would be an individual invoice that you would make one at a time. It sounds like what you want may be dedicated business invoicing software, not a spreadsheet.

[@LucaSpina]

@@macmost Thank you for your reply!!

[@geegeemcgee9135]

"Creating a Billing and Invoicing System"... How am I learning how to create a system with the first three minutes talking about a client number and client name. I'd like to know if it's possible to create a printable invoice from Numbers.

[@macmost]

Watch the video. That's what I'm showing. But if you just need to create a printable invoice where you fill in the blanks for each invoice, you can just make a blank template in Pages and use that. This is showing how to do it from a table of items.

[@jainejackson8897]

@@macmost well you never demonstrated the actual invoice merging with the data you created....please do so that we can see the whole process

[@macmost]

@@jainejackson8897 Not sure what you mean. I demonstrate it in action at around 9:00 and on. You can just download the sample file and try what you want to do on your own.

[@jainejackson8897]

@@macmost thankyou but that does not look like an invoice = no invoice number or other required info? can you please direct me to the sample file? i could not locate it. I do appreciate your knowledge sharing

[@macmost]

@@jainejackson8897 click on the link in the description to go to my site and the download link is there. If you need things like invoice numbers and such, you’ll have to further customize this to fit you exact needs.

[@donaldgendron1658]

Reference: MacMost #1897 - I keep getting this message when I finish entering the "Lookup" function; "This formula can’t reference its own cell, or depend on another formula that references this cell." Where am I putting the function?

[@macmost]

So are you having a problem with the example in episode 1897? (then why leave a comment here?) Did you compare your file to the example file I make available (macmost.com/creating-a-billing-and-invoicing-system-in-numbers.html)

[@xjared666]

Great video, super helpful. Thank you! I have a question about the due flag column. Once that column is filled I'm not able to resort the table. For example if I want to look at the table alphabetically the reorganization breaks up the chain the due flag column relied on. Is there away round this? This comes up around 4:30 in the video. Thanks!

[@macmost]

Not sure what you mean. Sorting shouldn't be affected. Are you sure you have cleared out the previous sort or maybe you are using Categories?

[@xjared666]

@@macmost I appreciate the quick response. Sorry, I might not have the correct vocabulary to explain my question. I made a 37 second video showing the problem. kzbin.info/www/bejne/fZPHdaunqqiLqtE

[@xjared666]

@macmostvideo I've been thinking about this a bit. Would it be better to put the due flag column in a separate column so it doesn't rearrange when you sort the rest of the data?

[@macmost]

@@xjared666 What do you mean by "put the due flag column in a separate column?"

[@xjared666]

@@macmost Whoops sorry, I mean separate table not column

[@hugopereira7519]

I'm trying to create a database using Numbers. My goal is to have a sheet where I have all the information about the customer, but then I want to look for that same information in another table, that is, I would like to put the customer's name and for the table to automatically assume all the information regarding that client. However the formula is always giving error, is it necessary to enable something? I'm using the lookup function the same way it's shown in the video, but I always get the following error message: "Argument 3 of FIND is waiting for a number, but found “Number”."

[@macmost]

Looks like you simply haven't set up your formula right. You are using the FIND function for something? Look at the functions help and make sure you understand each parameter and set them all up right.

[@hugopereira7519]

@@macmost I managed to solve it with the proc function. Thanks

[@JT_India]

Very complex

[@roxind]

For the due flag, I cannot get the formula to work, it give me a message that I'm not allowed to reference its own cell. Weird.

[@macmost]

Sounds like you have the formula wrong. You should not have the cell referencing itself. Check it carefully.

[@lexbene16]

Thank you very much for your tutorials. Is there a way for Numbers to automatically generate invoice numbers? Like macro function in Excel.

[@macmost]

Not quite sure what you are looking for. If the previous invoice number is in cell D8, then =D8+1 would give you a new number. Something like that?

[@lexbene16]

@@macmost Yes, I was looking at other videos but they were using excel and it involves macro-enabled workbook

[@StupidTeenageVideos]

Was this a tutorial? Or a demonstration?

[@macmost]

Both. Why do you ask?

[@user-on8wl6qx5i]

great videos! how can i save data from an invoice automatically? For example after filling an invoice i want to press, write or check something so the data (name, date, item, etc) gets saved in another sheet then it clears out for a new input. thanks in advance and sorry for my bad english

[@macmost]

Watch the video closely. You're looking at it backwards. You add the data to the other sheet first.

[@user-on8wl6qx5i]

macmostvideo thank you for your response. what i’m trying to do is a data entry like you showed on your video “data entry with ios numbers” but more personalized like an invoice, since my document has a lot of columns it’s difficult to input data on ios

[@Trackhoe075]

Interesting idea! I guess but I’ll stick with my FileMaker

[@stefangeudens4250]

Indeed you can not use Numbers as a database... but it is for free... I try to get a good "database" solution for free

[@jenniferrossehamm8891]

@@stefangeudens4250 Try notion.

[@Triplecrownrealtydfw]

Can this be done in google sheets? If so, I'd like to see it for clients with lines in invoice itemized out w. totals and allowing for both, credits and taxes.

[@macmost]

Not sure. This really takes advantage of how Numbers gives you nice table formatting and such. You could probably work out the formulas in Sheets, but it will be harder to get something printable. Adding credits and taxes will make it more complex, but it should be doable if you are good with spreadsheets.