Excellent tutorial, but noticed Contact and Terms should be switched, N40 for L40 when adding the tax. Really impressed with the way in which you present this in a simple to follow format. Thanks !
Hi i follow all the instruction and I made it all correct but I want to know how I will change those information that you give example, I want to know how to change all the dATA. please let me know.. thanks Im new here
Thanks you, I'm glad you found it useful. XLOOKUP is not available in OO that I am aware of. You could always arrange the tables such that you can use VLOOKUP instead :-)
Great tutorial! I was setting up a template for my business and I need to figure out how to set this up so that sales tax is not charged on each item. I do not need the discount columns so I left that out, and my thought was to in column M to Choose to tax(T) or not to tax(0). The formula that I tried was =IF(M17="T",+L17*N40,O40). This formula gives me the correct amount for that line, but I can't pull down that formula for the other lines because I need the true and false part of the formula to remain constant. I am not sure how to get the +L_ would change, but the *N40,040 needs to remain those same letters and numbers. Is there a way to do this? I appreciate your help, figuring this out. I have subscribed to your channel and will be watching more of your content. If there is a different video that would explain this senerio if you could point me in that direction.
Your tutorial is wonderful. The version of excel I am using does not have XLOOKUP. I saw in a reply you advised using VLOOKUP. Could you possibly give an example of how to use VLOOKUP in place of XLOOKUP? I tried changing the X to a V but that did not work. Thank you!
Hi Kimberly, you would need to combine the various tables to use VLOOKUP. unfortunately this is not an easy task given the existing parameters. Perhaps you should migrate to a more current version of Excel.
Hi Ian, That's fine for the UK, but other countries have different rates and different ways of calculating Sales Tax / VAT. This template allows you to choose your methodology :-)
@@MrSpreadSheet Hello, I did! I liked and followed your Facebook page and left my email address ( Sam.S******gmail**m) - I also liked and subscribed to this channel - what else do i need to do?
The formula “=XLOOKUP(C6,CustomerName,Add_1) does not work even though I have made no mistakes. I keep getting #VALUE” error. Any ideas why this is not working?
Thank you for this. Could somebody answer a question? Our invoice is very similar but we have a master tab that has an extra 20 columns. One for each product, each cell in the customers row will show the product name if they have purchased it or be blank if they haven't. Is there a way to auto fill what they have purchased, on the invoice, from this master tab? I have managed to do it but not without leaving lots of gaps in the invoice from when an item hasn't been purchased. Im trying to find a way to automate the products purchased as the master tab is huge and isnt something we can edit so flicking back and forth to find what product each customer has bought can be very difficult. With this I would be able to choose just the customers name and the entire invoice would auto fill including what they have purchased. Any advice or examples of auto filling products without leaving gaps would help so much. I know this may not be possible as our master tab is very specific and large.
I’m loving your video, and love the layout of this invoice! I’m not very computer savvy, so I’m trying to figure this out, but at 8 minutes, 15 seconds, when you switch from invoice to tables tab, how do you get those tables in order to fill ranges? I only have the information on my tables page from where we left off at the 4 minutes 33 second mark on my tables page. I feel like I might have missed a previous video?
Hi Jenny, I have pre-populated the Tables worksheet with the data shown at 8:15. So, just expand on the entries where I left off at 4:33. You have not missed anything. :-)
@@MrSpreadSheet I followed your guide at the end of the video - I followed you on Instagram, left a message with my contact information and signed up for your newsletter. The only way I see of obtaining the template is to purchase it for $49.99 from the website. Please advise.
Thank you for such a great tutorial! However on the very last step after VIEW>PAGEBREAKVIEW>NORMAL - when I got to print or save as pdf - it has the 15% tax workout on the second page. How do I get rid of that ?
I have a hp chromebook. I use the online version of excel and when i try to do the formula for vlookup it wont work. I have tried everything. I would like my client names be in the dropdown box.
I'm stuck... Do we need 2 Tables sheets? Or did you write over the CustomerName table coz Im getting an error when trying to put in th xlookup c8 customerName formular
wouldn't be nicer to combine the formula like this instead of cell reference =ROUND(IF($B16="",0,(VLOOKUP($B16,ProductTable,2,0)*(1-(XLOOKUP($C$8,Customer,Discount))))),2)
@@MrSpreadSheet Absolutely 💯% you are right... I am confident I will learn a lot from you and I am glad I am taking advantage 🙏 from your knowledge, Time you spent creating those videos, and hardworking 🙏 Thanks in a Million.
Hi Gilda, try examining your formula using the 'Insert Function' tool next to the formula bar. This should help you to isolate the incorrect component in your formula :-)
![How to keep a Sales Day Book with Analysis in Excel - [2021]](http://i.ytimg.com/vi/ZwIOov32dE4/mqdefault.jpg)
![How to keep a Sales Day Book with Analysis in Excel - [2021]](/img/tr.png)
![How to keep Inventory using an Excel Spreadsheet - [create your own Template]](/img/n.gif)
Excellent tutorial, but noticed Contact and Terms should be switched, N40 for L40 when adding the tax. Really impressed with the way in which you present this in a simple to follow format. Thanks !
Thanks for for comments Martin :-)
I love the way you set it up! It is fun to watch AND interactive! You got a like from me :)
Thanks, glad you liked it 😊
Oh this incredible!! Id like to use this personally. Thank u.
Thanks, glad you found it useful :-)
"Terms" is "Harry Both" and "Our Contact" is "45 Days". Else it is a very nice invoice.
Great, glad you liked it. :-)
Respected Mr.SpreadSheet, you're really a very intelligent instructor, Thanks a lot.
You are very welcome
can I save this into my yearly Exel work books
Hi Roy, Anything is possible in Excel, but this would involve a lot of VBA programming. :-)
Thank you for this great job. Well done!
Glad you enjoyed it!
Świetny przykład i super uproszczona forma
Thanks :-)
Thanks
Welcome
excellent tutorial!!! thanks
Glad it was helpful!
another cool video, keep it up Mr Spreadsheet
Appreciate it!
Hi i follow all the instruction and I made it all correct but I want to know how I will change those information that you give example, I want to know how to change all the dATA. please let me know.. thanks Im new here
Hi Bryan, well done, just repeat the various processes using your own data. :-)
Simply amazing.
Thank you! Cheers! :-)
Thank you so much..
You're most welcome
How do I add a credit to the invoice? the program just adds every amount when I want to subtract one line from the total as a credit?
Hi, Have you tried entering your values as negatives?
Very nicely done! Is there a way to use the xlookup function for Open Office?
Thanks you, I'm glad you found it useful. XLOOKUP is not available in OO that I am aware of. You could always arrange the tables such that you can use VLOOKUP instead :-)
Great tutorial! I was setting up a template for my business and I need to figure out how to set this up so that sales tax is not charged on each item. I do not need the discount columns so I left that out, and my thought was to in column M to Choose to tax(T) or not to tax(0). The formula that I tried was =IF(M17="T",+L17*N40,O40). This formula gives me the correct amount for that line, but I can't pull down that formula for the other lines because I need the true and false part of the formula to remain constant. I am not sure how to get the +L_ would change, but the *N40,040 needs to remain those same letters and numbers. Is there a way to do this? I appreciate your help, figuring this out. I have subscribed to your channel and will be watching more of your content. If there is a different video that would explain this senerio if you could point me in that direction.
Hi Cindy, without going into depth, have you tried an IF Statement nested in the formula.
Why am I getting "Value!" alerts on the customer inputs? I am not very computer savvy. Can you help?
Hi, You can check your work against the original. See the download guide video ua-cam.com/video/m7Zf8_UWPGQ/v-deo.html 😊
Your tutorial is wonderful. The version of excel I am using does not have XLOOKUP. I saw in a reply you advised using VLOOKUP. Could you possibly give an example of how to use VLOOKUP in place of XLOOKUP? I tried changing the X to a V but that did not work. Thank you!
Hi Kimberly, you would need to combine the various tables to use VLOOKUP. unfortunately this is not an easy task given the existing parameters. Perhaps you should migrate to a more current version of Excel.
Why would you need a fix value in o 40 Im sure it would just be 20% in the UK sorry if that sounds rude!
Hi Ian, That's fine for the UK, but other countries have different rates and different ways of calculating Sales Tax / VAT. This template allows you to choose your methodology :-)
Loved following this speadsheet - if I am not too late can I get a copy to 'play' with please?
Hi HCSNI, glad you liked it. Please follow the guide at the end of the video :-)
@@MrSpreadSheet Hello, I did! I liked and followed your Facebook page and left my email address ( Sam.S******gmail**m) - I also liked and subscribed to this channel - what else do i need to do?
@@HCSNI I did as well, have not received.
Following your video but keep getting #NAME? when it's time to input address...
You can check your work against the original. See the download guide video ua-cam.com/video/m7Zf8_UWPGQ/v-deo.html 😊
How to use multiple VLOOK multiple time in a cell, Like City,provine, postal code
Hi Ehan, use a fresh VLOOKUP for each component. :-)
hello sir i have a error in term cell of invoice what is the reason #N/A
Hi, You can download a copy of the completed template and then check your work against this. Go to www.MrSpreadSheet.com :-)
The formula “=XLOOKUP(C6,CustomerName,Add_1) does not work even though I have made no mistakes.
I keep getting #VALUE” error.
Any ideas why this is not working?
Hi, You can check your work against the original. A download is available at www.MrSpreadSheet.com :-)
Any automated invoice please?
Hi Bawa, not sure what you mean ?
Thanks very helpful, Question : how to add a second page to invoice and bring all the logo, Invoice# etc to second page.
Hi lakshmanan, this would entail a lot of programming and goes way beyond the preparation of a simple invoice. :-)
@@MrSpreadSheet thank you appreciate your help and reply
thanks for the videos. would like a template of these
Hi Kuhunya, please follow the guide at the end of the viseo :-)
I am following up your instruction on this topic but i did not understand which key is the today command key to click to please help me
Hi Abdul, the Excel command is TODAY() and the quick key is Control + ;
Subscribed! First video that I have watched from you and it is just great!!! Can I please please have a template for this? :)
Hi Mykolas, Glad you enjoyed it. Please see the guide at the end of the video :-)
May I ask if what is the "2,0)" in the formula bar for the price in 16:29?
Hi Mikhaela, The 2 is the second column in the Products Table, the zero is the same as the word False [exact match required] :-)
Thank you for this. Could somebody answer a question?
Our invoice is very similar but we have a master tab that has an extra 20 columns. One for each product, each cell in the customers row will show the product name if they have purchased it or be blank if they haven't.
Is there a way to auto fill what they have purchased, on the invoice, from this master tab? I have managed to do it but not without leaving lots of gaps in the invoice from when an item hasn't been purchased.
Im trying to find a way to automate the products purchased as the master tab is huge and isnt something we can edit so flicking back and forth to find what product each customer has bought can be very difficult.
With this I would be able to choose just the customers name and the entire invoice would auto fill including what they have purchased.
Any advice or examples of auto filling products without leaving gaps would help so much.
I know this may not be possible as our master tab is very specific and large.
Hi Crossed, sounds like a job for the VLOOKUP command coupled with an IFERROR statement to ignore the blanks.
I’m loving your video, and love the layout of this invoice! I’m not very computer savvy, so I’m trying to figure this out, but at 8 minutes, 15 seconds, when you switch from invoice to tables tab, how do you get those tables in order to fill ranges? I only have the information on my tables page from where we left off at the 4 minutes 33 second mark on my tables page. I feel like I might have missed a previous video?
Hi Jenny, I have pre-populated the Tables worksheet with the data shown at 8:15. So, just expand on the entries where I left off at 4:33. You have not missed anything. :-)
O
O
Very useful. But how can I get the template?
Please refer to the guide at the end of the video :-)
@@MrSpreadSheet I followed your guide at the end of the video - I followed you on Instagram, left a message with my contact information and signed up for your newsletter. The only way I see of obtaining the template is to purchase it for $49.99 from the website. Please advise.
Thank you for such a great tutorial!
However on the very last step after VIEW>PAGEBREAKVIEW>NORMAL - when I got to print or save as pdf - it has the 15% tax workout on the second page.
How do I get rid of that ?
Hi Kim, sounds like your print co-ordinates are misplaced. You need to make sure that the Tax Boxes are NOT included in the printing grid. :-)
I have a hp chromebook. I use the online version of excel and when i try to do the formula for vlookup it wont work. I have tried everything. I would like my client names be in the dropdown box.
Hi Seana, Unfortunately the online version does not support many of Excels formulae and functions :-(
Hi! I dont have the xlookup function in excel 2016. What formula can i use?
Hi Ella, Use VLOOKUP :-)
Whats the formula going to be to fill out the customer info? Thanks!
I'm stuck... Do we need 2 Tables sheets? Or did you write over the CustomerName table coz Im getting an error when trying to put in th xlookup c8 customerName formular
Hi Baba, why not check your errors against the original, see the instructions at the end of the video to get a copy :-)
Good morning how do you link an invoice to your income and expenses sheet in excel please
Hi Derek, we have a 'Paid For' product to do this, please see our website for ndetails. :-)
Hello Mr spreadsheet, what version of excel are you using?
Hi, Excel 365 :-)
Hi MrSpreadSheet, I keep getting an error when I went into Name Manager and hight customer Name, when I added $B$8. What is the $8 represent?
Hi P Pam, please message me on FB :-)
I'm not sure if you realize but your terms and contact are mixed up
Thanks Lorie :-)
If 1 item having 2 different prices, how will u do...?
Hi Umakar, that would depend of your selection criteria, but I would probably use an IF statement.
I would like this invoice
Hi Pierre, please see your FB messages :-)
i would like the template
Please see my message on FB
👍🏻👍🏻👍🏻
?
please need your help
Hi, please see my reply to your other query :-)
wouldn't be nicer to combine the formula like this instead of cell reference
=ROUND(IF($B16="",0,(VLOOKUP($B16,ProductTable,2,0)*(1-(XLOOKUP($C$8,Customer,Discount))))),2)
Hi Abdi, Yes, there are many ways to achieve results in Excel. I think it is a matter of what you understand and what you are comfortable with :-)
@@MrSpreadSheet Absolutely 💯% you are right... I am confident I will learn a lot from you and I am glad I am taking advantage 🙏 from your knowledge, Time you spent creating those videos, and hardworking 🙏 Thanks in a Million.
send me a copy of invoice thank
Hi Abdullah, please see the guide at the end of the video :-)
Complicated
Hi Steve, sorry to hear that...
My first time using excel , I’m trying to great am invoice for a client and I’m getting error VALUE while using XLOOKUP. Formular
What can I do
Hi Gilda, try examining your formula using the 'Insert Function' tool next to the formula bar. This should help you to isolate the incorrect component in your formula :-)