00:00We are embarking on a journey to create an invoice template that looks something like
00:04this.
00:06Let me give you a brief introduction about my invoice template here.
00:09On the left-hand side is the core invoice template here, and on the right-hand side
00:15is the product against price database table on the side here.
00:19This database table can also be located on a separate worksheet or on a separate workbook
00:25altogether.
00:26I am going to start off by creating a pull-down menu on the product here, place the cursor
00:31on cell C14, and you go to data ribbon here, and under data tools, there is data validation,
00:38click on it, make sure you enable the settings tab.
00:42On the allow, you are going to select list, and you are going to click on this arrow here,
00:46and go ahead and select the product from the product database here like this, and press
00:52enter twice like this.
00:54And then you will have all the available product from your product database as a pull-down menu.
01:01Next, we are going to create or rather pull the unit price based on the product that you
01:08have selected.
01:09The unit price comes from this product database table itself.
01:11And the formula that you will be using will be this.
01:16Essentially, what this is doing is doing a VLOOKUP against the product that you have selected,
01:21and then use that product as a baseline to get the unit price.
01:26I am going to hit enter here.
01:28Next, we are going to calculate the total.
01:29So, say equal unit price multiplied by total quantity, that will give you your total price
01:35itself.
01:36Now, what I am going to do is I am going to go ahead and apply the same dropdown for
01:41all
01:42the rows by clicking on this filler handler here, and then applying like that.
01:47And then for the unit and total, I am going to select both of them and apply to all the
01:52rows like that.
01:53And at this point, your table or your invoice template should look like something like this.
01:58I am going to go ahead and test it out here.
02:00I am going to select maybe invisible paint, and the unit price is $57.09.
02:06If you look at it, it matches here.
02:09And then just as a test, I am going to enter a quantity of two, and you should calculate
02:14the total, subtotal, discount, tax, and eventually give you the total amount that is due.
02:18Next, we will be adding the row hiding feature for your invoice template here.
02:23But in order to do that, you are going to have to introduce a helper column.
02:26My column L is going to be a helper column.
02:29I am going to give the header for that column, maybe call it the helper.
02:33And the formula for that will be equal if C13 is equal to blank, then you are going to return
02:42a value of 0.
02:42If not, you are going to return a value of 1.
02:45You are going to apply the same formula right across all the way down to the very last row
02:50like this.
02:50As you can see, these first two are 1 because C13 and C14, they do have some values in it.
02:57Next, we are going to introduce or enable the filter.
03:01Put your cursor on any of this header column here and press Ctrl-Shift-L.
03:06This will give you the pull down menu.
03:09From the helper column, you are going to click on the down arrow and make only the one visible
03:15like that and click on OK.
03:17And at this point, you should have two rows like this.
03:20Next, to make it automatically increase, you are going to right click on your sheet, view
03:25code and add this VBA script in your VBA editor.
03:29After that, you are going to press Ctrl-S to save and close your VBA editor.
03:34Now just as a test, if you start introducing new products in your list like this, you can
03:39see that it does add new rows for you as you add more products in your invoice.
03:47And as part of the cleanup, you can change the font color for column L to be white so that
03:53it is not visible.
03:54And then after that, you are done.
Comments