Скачать или смотреть How to create Dynamic Invoice with Dynamic Total just below without using VBA in Excel

Subscribe to learn more about Excel Spreadsheet and Excel VBA.
Subscribe to learn How to create a Dynamic Invoice with Dynamic Total just below without using VBA in Excel?
The technique of creating a Dynamic Invoice is similar to my video on how to create Dynamic Delivery Order. However, for this video we will be able to see for the second time. And also this will help those who have not seen my video on Dynamic Delivery Order will be able to learn the same technique here.
Why need to create dynamic total below the invoice? The reason sometimes we have seen computer printing receipt is just a few lines with dynamic total. Maybe this is about formatting or saving paper. Anyhow it is good to learn a new technique which I can say is easy to learn. And this technique can be useful for other similar requirement.
Usually the total placed fixed below the all the allocated particulars. Of course with the proper formatting. In dynamic total we also learn how to create dynamic formatting. So it is worth to learn up this technique.
The technique given in this video is easy to learn and master. I believe you can master it within few hours or maybe less than that. This is because there is no VBA involved. Purely just Excel functions and formulas. We just used simple functions and formula which I believe you already know. In this technique, it is not just knowing the functions and formulas, but just like in cooking, the way to use in a systematic order that produce the best cook. So cook your way through with Excel Spreadsheet.
Subscribe not only to learn about dynamic invoice with dynamic total, but also to learn more of the techniques in Excel Spreadsheet and Excel VBA which I will upload in the future.
The technique of how to create dynamic invoice with dynamic total just below is as below.
Technique
1. Create 3 sheets with names as dataentry, invoice and computation. That means we need only 3 sheets to perform the job.
2. The dataentry sheet is for you to key in all the particulars of your billing.
3. The rule of thumb in key in these particulars are
a. Invoice No must be at the first column of the range.
b. Make sure the Invoice No are in ascending order.
c. For Invoice No with few items, key in all the Invoice No for each item down the row. Let say we have 4 items, then key in the same Invoice No in 4 rows.
d. Make sure the Invoice No are in a grouping that is no break in between the rows.
4. The dataentry sheet will be your raw data for your billing. This data can be used not only for producing Invoice printout, but also can be used to produce periodical reports.
5. The invoice sheet is for you create your Invoice and make a printout. Once complete, this sheet is an active Invoice format.
6. Make sure there is nothing below the headings.
7. In the Invoice no cell, F7, key in the first Invoice no. This cell will be the only cell that you can make changes, and the rests are protected. And also this cell will act as a master key to your dynamic billing.
8. The computation sheet is used to create your hidden formulas for the dynamic invoice to work.
9. In this sheet, link the first cell, A1, to the Invoice no cell of the invoice sheet.
10. Once we have the link cell, the next things to do are
a. Find the number of item in the given Invoice no.
b. Find the row position for the first item related to the given Invoice no.
c. Find the row position for the rest of the item related to the given Invoice no.
11. Once ready, we will extract the respective data related to the given Invoice no. For this we make use of HLOOKUP functions.
12. Perform locking using absolute cells or ranges to avoid error when copying.
13. Use IFERROR function to perform blanking.
14. The extracted data in step (11) is then use as a data input to the dynamic total technique.
15. Put an asterisk in cell B13 the same row as the total. This is to make the total exist.
16. Create multi-pointer to select all the data which is not blank.
17. Compress the data.
18. Extract the data from the first extract range.
19. Perform locking using absolute cells or ranges to avoid error when copying.
20. Use IFERROR function to perform blanking.
21. Link the result of step (18) to the invoice sheet.
22. Perform conditional formatting for borders.
23. Test with different Invoice no.

It is worth to learn about this dynamic invoice with dynamic total. It will surely help you in your presentation. Please enjoy yourself in the video.
Subscribe to learn on How to create a Dynamic Invoice with Dynamic Total just below without using VBA in Excel Spreadsheet.