Subscribe to learn more about Excel Spreadsheet and Excel VBA. Subscribe to learn How to create a Dynamic Delivery Order without using VBA in Excel? As an Excel programmer, I have seen how one of my customers once created a Delivery Order with Excel Spreadsheet. She has a ready blank sheet of the Delivery Order. She will create a second blank sheet. In this blank sheet, she will key all related particulars and print accordingly. She continues to repeat the step for the next Delivery Order and so on. At the end of the day, she will have many sheets of the Delivery Order in one workbook. In short, she uses Excel spreadsheet as a type-writer instead of as a computer. So she does not fully utilize the capability of Excel spreadsheet. So it is worth to learn of how to create dynamic delivery order. So, without the technique of dynamic delivery order or the like, most people will face difficulties in house-keeping, periodical summary or details reports, quickness respond to the customers checking about their delivery status, and lastly more manpower is required to perform many manuals works. 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 them in a systematic order that produce the best cook. So cook your way through with Excel Spreadsheet. Subscribe not only to learn about dynamic delivery order, 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 delivery order is below. Technique 1. Create 3 sheets with names as DO Keyin, delivery order and computation. That means we need only 3 sheets to perform the job. 2. The DO Keyin sheet is for you to key in all the particulars of your DO. 3. The rule of thumb in key in these particulars are a. DO No must be at the first column of the range. b. Make sure DO No are in ascending order. c. For DO No with few items, key in all the DO No for each item down the row. Let say we have 4 items, then key in the same DO No in 4 rows. d. Make sure DO No are in a grouping that is no break in between the rows. 4. The DO Key-in sheet will be your raw data for your delivery order. This data can be used not only for producing DO printout, but also can be used to produce periodical reports. 5. The delivery order sheet is for you create your DO and make a printout. Once complete, this sheet is an active delivery order format. 6. Initially, start with the blank DO format without all the particulars. 7. In the DO No cell, key in the first DO 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 delivery order. 8. The computation sheet is used to create your hidden formulas for the dynamic delivery order to work. Once completed, usually it is hidden and cannot be seen by users. 9. In this sheet, link the first cell, A1, to the DO No cell of the delivery order sheet. 10. Once we have the link cell, the next things to do are a. To know how items are there in the given DO No. For this we make use of the COUNTIF function to scan through the DO Keyin sheet. b. To know the row position of the first data in the raw data. For this we make use of the MATCH function to point through the DO Keyin sheet. Make sure the range scan must include the heading. c. To know the row positions of the rest of the items if any. For this we make use of the IF function to check whether the item count is higher than the total items as in step (a). For this we must allocate enough rows for the maximum items found in a DO No to avoid left out items. 11. Once ready, we will extract the respective data related to the given DO No. For this we make use of HLOOKUP functions. 12. Perform locking using absolute cells or ranges to avoid error when copying. 13. Since we allocated enough rows as in (10c), we need to perform blanking so as not to see error. We can make use of IF function or IFERROR function. 14. Once completed, go back to the delivery order sheet. 15. Link all the respective data to the DO No format. 16. Perform necessary formatting. 17. You can test the delivery order by changing the DO No cell value. It is worth to learn about this dynamic delivery order. It will surely help you in your presentation. Please enjoy yourself in the video. Subscribe to learn on How to create a Dynamic Delivery Order without using VBA in Excel Spreadsheet.

• published: 26 May 2020
• views: 14000