Discover the Best Commission Invoice Format in Excel for Production

Commission invoice format in excel for Production

hello and welcome this is Randy with Excel for Freelancers and in this week's training I'm going to show you how you can easily and effectively calculate any type of commission for any employees and I'm going to show you this incredible application where you can calculate commissions for unlimited employees for unlimited apparels and put it all together in this incredible template with mini dashboard it's going to be an incredible trading so let's get started hi and thanks so much joining me I've got a really fantastic training today I'm going to show you how you can calculate any type of commission easily with just a few formulas and better than that I'm going to put that all in an incredible application with unlimited employees unlimited payrolls we're going to be able to track multiple employee types we're going to be able to create multiple types of commissions based on employee type you can have different commission structures and of course we're going to put that into a mini dashboard it's going to be incredible training you won't want to miss a moment so go ahead and grab your beverage of choice we're going to get started right away if you do like these trainings I create these each and every week in fact this template training workbook is absolutely free all you need to do is click the link down in the description just look for the word download go ahead and click the link below that into your name and email and I'll get this sent right over to you if you do like these templates and you like the training free every week of course there are some fan fantastic ways to support our Channel and what we're doing here I bring these to you each and every Tuesday I just ask a few things if you can go ahead and click that subscribe and the notification icon Bell that'll ensure that you get alerted when these trainings go live each and every Tuesday I'm available through live chat each and every Tuesday depending upon the time here in Asia it's 7:15 p.m. but it could be in the morning if you're in the west it's I'm on live chat answering every question you have there so that's a great way also to support this channel I've got some incredible forses and workbook templates and zip packages down in the links Down Below in fact I've just got a brand new 300 workbook package for you that's 300 of my best templates all for a very low price that's a great way to support the Channel all right we're going to get started on this what we're going to do is we're going to go over just a brief overview of the application and then we're going to just get down right into some formulas how do we calculate formulas with our commissions right so how do we create a commission based on two different types so we're going to go over that and then of course we're going to get into how we created this application but I do want to make sure that you're able to get the best part we just calculating commissions and the simple formulas that we're going to use to get there and then we'll go into this mini dashboard we've got an admin screen so basically we got a pay frequency I've got employee types now the cool thing is that you can add unlimited employee types each different employee type can have a different type of commission structure we have up to 10 different tiers on a per employee type basis so we can create our own number of tiers we can create our own pay frequency whether the metric meaning net sales or gross sales or whatever your commissions are based upon that can be dynamic we have a minimum pay right sometimes there's a minimum pay maybe you want a $5,000 on this type so maybe we have that we also have two different pay types we have incremental commission types which means you earn a little bit I'll show you what that means let's go ahead and like an incremental might look like something like this where you earn commission based on the up to so for example if your total gross revenue is let's say under $50,000 you're going to earn up to 15,000 at 20% Then between 15 and 30,000 you're going to earn a certain amount all the way up so it keeps graduating it's incremental that's one type then there's another type that's flat like for this employ we have just a flat type where the gross sales was $21,000 and it fit between 20 and 30,000 so the flat he's going to earn a single commission based on that so we're going to show you both of those ways and we're going to wrap it all in this commission rate calculator with a really cool dashboard that we're going to get into all right so let's take a look at the admin we're going to go over some calculations and so we can understand so the cool thing about this is we have multiple tiers and based on our different employee type we can have different ones so when we set this employee up this employee type this is a training type employee we can add more if we want just on this list we set the number of tiers as we change the number of tiers so does our information here and all we need to do is just add additional information here and then it's automatically going to just change very very easily so we can simply add it and then we can set our percentage if we want to super simple like that okay great so now again we have two different types of earning we have a flat type and an incremental type so we're going to go over that now again a flat type would be if our uh flat type let's say our uh gross is let's put a sales here I'll just go we'll use this sheet here we're going to put a sales so let's say we're basing it off sales and let's say we do 50,000 in sales and I want to understand the commission that's focus on this $50,000 in sales so how are we going to do that well for a flat type which means I need to look up basically and find it right here and I know that because that 50,000 Falls within tier number 7 it is a basically a flat rate of 50,000 now what I would like to do is have a formula here that shows empty empty and as soon as I hit the right tier which is this one here I want 20% of $50,000 to show up so how are we going to do that well the first thing is the easiest solution is to use the lookup formula so let's say I want to use equals lookup in fact why don't we just make this I'm just going to call this sales okay just sales so that way we have a name bring we can use this in there and so let's say we're going to use lookup so I'm going to use equals lookup now lookup is going to return the last column the last column whatever the value is in the last column so if I'm going to look up here I'm going to look up all of these so what is the value that I'm going to be looking up I'm going to be looking up the sales value right here which is sales we've named it as sales and what is the array that I'm going to be looking up I'm going to look it up in this array right here all of our values and then I'm going to close parentheses and I'm going to hit enter okay so we see that 0. 2 which is 20% if I were to change the percentage we see that it found it now if I were to change this to 35,000 35,000 and I wanted to return that 177% so it's perfect that's what I found it now what I don't really want I don't necessarily want the commission percentage but what I just want is the am commission amount so I need to multiply the result times our sales so simply doing times sales we are going to get that commission amount right there and of course it's not in the percentage we are now back to a currency which is what I want so we see that our currency based on 35 is 59 but what I'd really like is that 5,950 I would like that in the correct row here so whatever roow that's been affected that's really where I would like to put it so how are we going to do that well what I'm going to do is I'm going to look it up and if it's found if this percentage is found I'm going to do the multiplication and put it here if it's not found meaning it's not returned then I don't need to put it there then I just put empty so here's what I mean we're going to use an if statement if the lookup of the value if we looking up it equals okay and also we want to make sure that we are going to make these absolute now so we're going to use the F4 key to make both of these absolute if it equals what is it this one this percentage right here then what am I going to do I'm going to take the value whatever's been found here and I'm going to multiply it times the sales so I'm going to take that m multiply time of sales otherwise what if not I'm just going to leave it blank so that's exactly what I want so notice that it's blank because it's not found which is exactly what I want but if I were to copy this and I want to paste the formulas here and paste those formulas here we see that it is now found correctly so if I format that back to a currency which is really what I want or counting either one so perfect so this is what I want it's found we see that our sales are 35,000 it's between the 30,000 and 45,000 here and it Returns the exact amount of what we want so if we were to change this to 7500 here it's going to actually pull it right here perfect that's exactly what I want so I want this amount to show up exactly where it was affected so we have that perfect that's great that's good for flat that's what we're going to call flat but what about if we want incremental now what would be incremental incremental means that what I want is for the first 5,000 I want to earn 3500 for the next 5,000 or whatever it is I want to earn uh I want to earn on that difference 5.5% so it's called incremental each increment you earn a little bit of commission based on the commission and based on the difference and let's say we are at 12,500 we we certainly don't want the entire 5,000 we only want 12,500 minus 10,000 so we're only earning 7.5% let's say let's for example let's put in that number here 12,500 so we have the 12,00 500 so let's remove this formula now we're going to change it up a little bit so I'm going to remove that formula now we're going to focus on something called incremental so if it's incremental on the first 5,000 I want to earn three and a half on the second obviously it's 5,000 here I want to earn 5.5 but now it's between 10 and 15,000 so what I want to do is I want to earn 7.5% on 12,500 minus 10,000 so it's going to be roughly 2500 and that's the amount I want to put here and then nothing in here so how do we do that in a formula well it's going to start out with really two conditions right I want to make sure if we know this value is over this value and I want to make sure that K K2 is not empty right here it's empty in that case I want to subtract K12 minus J12 that's the difference and I want to multiply that times the percentage so let's take a look at this equals and we want two conditions so we're going to write if and what are those two conditions and the first condition is that the sales our sales data has to be greater than this two amount and the other condition what's the other condition it also basically has to be K2 does not equal empty as we drag it down when it's empty I want to do something different so we want K12 excuse me K12 not to be empty so it does not equal empty so those are the two conditions when that happens what do I want to do I want to take the difference between these and I want to multiply times the commission so we're going to put it in parentheses this is if it's true we're going to take K12 minus the from to and from that's the difference and I want to multiply that times the commission rate so commission right here and if it's false we'll just put empty for now okay perfect so that's what I want so we take a look we see that 175 and as we drag this all the way down we see that we have 275 and then nothing here so the idea is that we've earned 175 we 275 but our amount is 12,000 so what if it's not if it's not then what do I want to do I want to check to see if the amount is greater greater than this here but not this then I want to make a subtraction and the difference between that will go right here so let's make the update it's going to start with another n so we're going to use if again and two conditions this time one the first condition is that our sales has to be greater than this we want to make sure then it's the from and also what is the other condition the other condition is that we want to make sure that it is less than the two amount so how do we do that we can use actually greater than or equals to okay so we're going to cover that and we also want to make sure it is less than or equal to so we could do less than or the other condition has to be that our sales is less than the from less than the from so that's what we want from a two excuse me it's less than a two so that's exactly what it when that case is if that's true what we're going to be doing is we're going to take the difference and multiply that so what is the difference the difference is our sales amount minus the from here minus the from amount here and then what we're going to do is we're going to take that and we're going to multiply that times the condition so we're going to put that in parentheses and we're going to multiply that times our commission percentage so times here the commission percentage okay so that's if it's true if it's false we're just going to put empty and close up parenthesis so now what we do is we're going to drag this down down here all the way and we see that we now have 187 so let's double check we know that 3 1/2 percentage time 5,000 is 175 we also know that 55% of 5,000 right here is 275 and now the difference is which is 2500 the difference is our sales 2500 minus 10,000 is 187 if we want to check that all we need to do is just equal our sales amount times here we can subtract that out from 10,000 so minus our 10,000 just to make sure 10,000 0 and next 01 so we're going to take that and then we multiply that times the commission rate so we see the difference here times here our commission rate which is 7.5 at this value and we get 18750 which is correct so we have if it's in a currency we see that's perfect okay perfect 49 is rounded off all right that's working just right so we see that so we see the two difference and then for anything else is showing blank now if we were to change that to 30,000 we see that it automatically updates ingly all the way down here so we see how the commission rates automatically update all the way oops that should be about 30,000 not 300,000 but it works good so 30,000 we see it's going to go up to here so it automatically updates to 30,000 perfectly oh we just need to make sure that's less than or equal to right k16 less than or equal to okay perfect because it is exactly 30,000 that's we want if to change this slightly to 31,000 we'll make sure that it automatically updates perfectly okay so all we need to do is then copy this formula and use it all the way throughout our steps pasing that formula in so perfect so we want to make sure that of course it says that the sales is less than or equal that and it multiplies the difference sales times the difference so here we have an incremental commission automatically so we have both flat and commission now if we were to combine those and I've done that on the other sheet so basically all we need to do is then combine them to create one solid formula and that's exactly what we've done here on the commission SE if we see our earnings here now keep in mind that our commissions are based on the sales right so if I select an sales associate here when we save the sales associate let's take a look at the traine we see the traine is a flat the sales associate is incremental and the outside sales is nothing I haven't done anything with that so we can save it on an employee type basis so each different employee type can have a different one which can be super helpful inside our commissions when we have an employee we can then assign that so for example John Johnson is an trainee so a trainee is on a flat rate so if I select a payroll we see that there's just one single one let's take a look at this formula here is is exactly the same as what I've just shown you now keep in mind that when I load this information up that information the earnings type is going to be saved right here notice if I select a different employee that it is incremental so we see how this changes to incremental so that earning type and I'm going to be showing you how to do that a little bit later but I want or flat so that earning type is going to be changed so our formula right in here is going to be based on that whether B8 is flat or whether it is incremental so all we did is we have to combine those formulas and we'll go over it just again so we can see that but again it's based on this earning type if it's flat we want to show just a single value on the percentage so for example if this is gross sales is 21 and between 20,000 and 30,000 I want to calculate the earnings and only those because it's a flat single earning however if it is an incremental we want to show those incrementals as we go through them okay so keep that in mind for the flat let's take a look inside this formula let's go back to the flat one with flat so I can show you an example of that and we can see how that it's working so we see that it's based B8 is flat so I take a look at this earning right here we're going to wrap it in if a if B8 equals flat we know we're going to be looking up the entire range here I'm looking up the period total now the period total is the named range called gross period total what's in k16 is that if I escape out of here and I select this we see that it's a named range called period total period total so just like we used sales here here we've used sales the sales amount here here we're using something called period total so that way the reason I do that and give it a name range is because it's much easier when we look in the formula we understand that period total so we're looking up the period total I'm looking it up in this entire range and it is going to find it's going to look inside this it's going to use the next available one it is going to find it inside because it's above remember it's using above look up uses the next one above and it's going to return 14.5 because it's found there inside that 21,000 it's found between these two ranges so we're using lookup it's found there right and it's going to be equal so we're checking to make sure if if it is equal to that we know it's equal to 14 point because it's been found if I highlight over here and we look at that lookup we see that it's .145 so we know it's been found right we can see it's been found and now we know that this is true if we look it up right we can see that if we highlight over here we can see that the value here is going to be true so we look up right we see that it's F we know l20 is of course 14.5 so it's been found there right we know that so now what we can do is simply multiply l20 by the period total if not we're just going to leave it empty okay so this is if it's flat if it's flat this is what we're going to do that means if it's not found if it's not found I'm going to show empty so that means it's not found here here here only where it is found are we going to perform that multiplication this is only if it's flat right here everything if it's a flat if it's incremental we're going to do everything here and it's exactly what I've shown you so if we let's select on one of the incremental commissions and we can show you that again we're basing it on that period total which is this right here we're going to earn 20% of up to 15,000 we're going to earn 25% between 15,000 and 30,000 30,000 to 40 we're going to earn 27.5% and then anything between 45,000 and 60,000 we're going to earn 30% on the difference now the difference is we see 489 so it's 4894 minus this that is what we're going to multiply time 30% and we get 11 184 so let's take a quick look in here this is where we're starting from on the formula here we're looking two conditions the period total must be greater than K20 must be greater than that from amount here and it also must does not equal empty cannot be equal to empty right so we so we see that K is not empty so we know that in that case we're simply going to take the difference K20 minus j20 which is 2 minus the from which we get 15,000 and we multiply that times 20% we multiply that times 20% and that's what is going to give us our 3,000 so we all we have to do is drag that down now when we get to this point here is where we come in the formula if which is here why is that because we see here that the period total is not greater than k23 right here is what's in K2 three this is 60,000 so we know our period total is 48,000 so we know that it's going to be false right here our period total greater than k23 that's not the case that is false right so this argument doesn't work right because it's false this is if it's true this is if it's false so here's our false argument now what do we need to do I'm going to check two conditions right I'm going to check if the period total is greater than j23 j23 is here right here we see that it is because our Revenue here is $ 489 and it's greater than 45,000 so we know that this portion is true so if we highlight this we know it's already a True Value so it's true the next value what we want to check here is here period to is greater this is true oh sorry and it's less than k23 so both conditions are true are 48,000 is greater than 45,000 and our 48,000 is less than 60,000 so since both of these are true through we can then perform all we need to do is take our total we subtract out j23 so we see that the difference here is going to be let's highlight over the parentheses or use F9 that works too so we see that the difference here is 39 47.99 so we're going to take that difference we're going to multiply it times the commission rate earnings for that tier and then we're going to display the results now of course if it's not we're going to show empty and otherwise if there's an error we're going to show empty so that's all we have to do and just take that formula and drag it down to all the possible tiers and that's all we have to do all right very cool so that's kind of basically how we calculate Commission on this now let's get into a little bit more of the operation of this application and see how we can put it to use for multiple employees and multiple payrolls so when creating this application I wanted something flexible that could be used for any type of company and any type of payroll structure and commission structure so that's where we have the employee types as we create multiple employee types what we can do is we can then set a different number of tiers we can set a pay frequency we can set the metric right net sales or gross sales and we can set the minimum if there's a minimum pay you know sometimes you would have that minimum pay and then earning type it's incremental as we went over so that's the first thing what we want to get into now I left this one purposely available so how are we going to do that so the idea is this we're going to set let's say we want five tiers so we're going to automatic Ally we can use conditional formuling to help us with that as we display those and then we want the default pay frequency we can set it for let's say monthly we want and let's say we're going to base it off gross sales and then the minimum pay let's say each employee going to get a minimum of 2500 in case they don't quite make their minimum using commissions and we're going to set an earning type of incremental well that's great so now all we really need to do is fill in both the two and the commission level let's say up to $5,000 we are going to set a commission of 5% and then the next $10,000 we are going to set it at let's say 7.5% then what we could do is we can use 15,000 and then set it for 10% also last once is we can set it for 20,000 in this level and then that's going to be 12.55% oops let's do 12.5% and then lastly what we can do is 20,000 on up we are going to set the percentage so notice that it says greater than and we're going to set this to 15% so that means everything above 20,000 one is going to be at 15% so how did we make this happen so this portion first thing what we want to do is we're going to use a formula the tier name is going to be automated I thought that would be quite helpful so we can automate the tier names and we also want to automate the from the first from is always going to be zero so we want to start off at zero so these are non-editable formula fields and with a tier number here so the 5,000 we're simply going to check a few conditions I want to know the first thing if K12 equals empty and K13 equals empty we're going to show empty for example in this I've got this formula that's brought all the way down here up to 10 different tiers so if we take a look at this we see here K18 here is automatically empty and we see k19 is also empty too so in this case we want to show that it's empty so basically if we're going to we're going to look at the one above notice in this one the one above is empty and the one below is empty so I'm simply going to show this empty however this one we see let's I'm not going to click on here however k16 is empty so we look at K15 we see that it's not empty and we want this one is empty so in that case what I want to do something a little bit different K15 does not equal empty k16 equals empty then what I want to do is I want to put greater than and then the text I want it formatted j16 whatever's in j16 and then I want to format as a currency here so that's exactly what we're going to do so notice it says greater than whatever is located in here so it's very easy however if they're both have values if the one above and the one below then we're simply going to put from 15,000 to 20,000 so we're simply going to combine them going to put in otherwise so here's the otherwise here so here's in case one is empty if they're both empty we're going to put it empty if one above is not empty and the one below is empty we're going to put greater than however if none of those if they both have values we're simply going to put in the text which is simply the mount field formatted using the text a space and two and then again formatted what's in here based on the currency so that's how we get this to and we just need to drag that formula all the way down okay great so we see how that formula works now we have this it's also format so basically what I want is this value here is simply equal to this plus one one cent however if this is empty we're simply going to show empty so again if K12 does not equal empty we're simply going to add a penny otherwise we're just going to EMP we're going to show empty and we just need to drag that all the way down here and that's how we're going to do that so that automates these two now what about the numbers here let's take a look at the numbers now I've got the numbers automated they're already here I've got up to 10 tiers so if we take a look in this cell we see 10 but that 10 is not showing up how do we get that not to show well let's take a look at that that's going to be based on this number right here so if I quickly change that to six we're going to see that that's six shows now remember that value seven is here it's just not showing up it's hidden how do we hide that well we can use a special conditional formatting to do just that let's go back to five on this one and I'm going to show you some conditional formatting tricks to help you hide it okay we dragged this formula down so I want to fix that border here so control one to fix that border if we want to do and that's going to bring up this and we'll set that border right there so here's what we're going to oops that's the wrong color let's change that back to it's blue we got a blue color on here and we're going to use this color right here so now what we want to do is I want to show you in the conditional formatting so all we need to do is highlight any of the cells in here we're going to go into the home conditional formatting we're going to manage rules and we see several rules now also we have some rules that I'll be showing you that haven't shown you yet but the first of one is this one that I wanted to show you that we're going to hide it so we're looking at row the current row - 12 is greater than L6 let's take a look at that that means the current row let's see the current row is b 17 If I subtract 12 we're going to get five and we see that this is five so we see that this minus 5 it should be hidden because it equals that I'm going to go back into that and we're going to take a look at that one more time so we can take a look at that rule the row -2 is greater than or equal l 16 we see it was equal to so it's greater than in that case what do I want to do I want to hide it so how do we hide it we're going to edit the rule we're going to go into the formatting and we're going to take a look at here the number we're going to look at the number we see it's a custom number and it's going to use three semicolons two or three two if it's numbers is fine or three if it's text and numbers we can format that now when we put those two semicolons it's actually going to hide the number so two semicolons will hide it three semicolon col if it's text and numbers so that's actually going to hide it so I'm going to hide it only when those conditions are met and that way as I change this so if I change this to four it's going to hide the one below if I change it to six it's going to show the one below so we can use that simple math remember we're subtracting 12 has to be greater than or equal the row at greater than or equal to this amount and that's how it's going to show so we're basically hiding these numbers based on the conditional formatting we have some other conditional formattings if the user a value that is less than what it should be I want to show something like hey that's wrong I want to let them know it should be over 20,000 under how do we do that again in conditional formatting we saw one rule that showed that yellow in this case we're going to use this is going to be for the entire range so we're going to start that range starts on row 12 so this formula is and it's going to be two conditions one we want to make sure that K starting at row 12 and any row below does not equal empty and also K12 is less than or equal to what is in J12 remember we don't have the absolute dollar sign before the 12 that means it's going to be used for any row within the range however we do have the absolute on the columns and that is going to automatically color it yellow and font so we have the font as bold and red here the font is bold and red we have the fill of this yellow so anything in this case that is less than the value in K is less than the value in J and we want to make sure that there's a value we want to highlight so as soon as we change it to something greater it is automatically going oops to one too many zeros but that's okay we get the point we see that it's going to automatically color it correctly so that's a great way to let the user know that they've entered an incorrect value lets them know right away okay very cool so we saw that we also have some additional conditional formatting rules that I wanted to show you we go into the manage rules we're also have more rules coloring the rows based on the colors so again these ones color rows we notice that we are coloring rows but in this case it's two conditions and row minus 12 is less than L6 and then it's a mod so this is for the odd row so here what I want to do is I want to color only rows that are less if we subtract the current row minus 12 as long as that's less than what's in L6 I want to color it if it's an odd Row mod of row two equals 1 so I'm going to give it that blue color now if it's going to be a little bit darker this rule I think we got some rules that are duplicated here so it looks like this rule got duplicated but that's okay it gets the point so in for even rows it's going to be relatively simple if we edit that rule we see this is for even rows and it's the same rule the the row must minus 12 must be less than whatever is in L6 and we see that L6 is absolute that's the number of tiers so that's going to help us automatically hide rows when it's not available or show the row instructing the user exactly what what to do and where to fill out that information and notice that these are a little bit darker color because that's not for user ENT these are lighter color because we want the user to inter values so that's why we have the multiple conditional formats one's for darker one's for lighter very cool so we've got that now we have some VBA which we're going to get to in a moment that's going to help us load it now the VBA basically what we want to do is because we have multiple so let's go ahead and save this one let's go ahead and add it see what it looks like so let's put in seven tiers even though I just cleared it all out we're going to set the default uh pay fre let's just make it three tier so it's a bit easier and then the default pay frequency we'll set it to let's say semimonthly we'll set the commission metric here that is going to be let's say sales here make it easy and then also we want to make sure that we have our minimum pay if we want that we can set that to 2500 we also want to save that information the earning type will set to be incremental and let's just keep this easy let's do 10,000 here and then let's do 20,000 here here and then let's do keep that open and we'll just do 10 20 and 30 okay we're going to then save an update and that's really important we want to open we need to make sure to fill in the earning type that's kind of important incremental and then save it okay so that's the required field so now that it's saved if I go back and I come back to that we see that it's now saved and that's what I want where is that actually saved if we take a look inside our commission C's database all that information is saved in here we have the frequency we have the Matrix the minimum the earning type flatter incremental we have all the tier information and values of Saved here up to 10 different tiers so we have 20 columns each tier each column you have a the the tier two which is the amount and then we have the percentage so it's all we need to save is really just this data excuse me let's pull that here is this data right here so that's all we need to have very very easy to save that and of course I'll get into VBA and show you how we're saving that but I wanted to make sure to give in the the operations of and of course we can delete a commission add a brand new we can easily just simply type in a new employee type so let's say we have field staff and then all we would need to do is just select on that we can create a brand new tier for field staff so very very easily to add additional employee types and then selecting on that and automatically the selection will put that correct staff here whatever we select and then you'll be able to add the number of tiers and add commissions and save all that so very very cool on that once we do have that saved up when we go into our commissions we can then select on an employee and then what we can do is we can then select what type for example field staff or outside sales like the one we just created and that brand new tier that we just created it's automatically going to be automated for that employee if we set a minimum we can set the minimum for this employee or it's going to be based on the default right so notice the pay frequency and the minimum pay is set here and and then we can just save and update that employee and then the next time we visit that employee that information is going to be saved with that employee very easily we can add a brand new payroll for this employee we can set a pay date let's say we want to pay 6:15 here and then we have an automated payroll name it's going to be for the previous month so let's say May 1st all the way to May 30th is when his pay dates and we see that as soon as we have entered both the starting date and the ending date we now have a payroll name that got automated and that's done with a simple formula here first of all I want to make sure that there's both a value in j15 and a value in M15 if either one of those are left empty we are going to show empty here so we can do that with the formula here if and j15 does not equal empty or M15 does not equal empty only then do I want to show the text right we're going to format it I want to format it as dates so we're going to use the text function to do that and we're going to use that that start date which is in j15 we're going to give it that date format we're going to use an and and I want a space a dash and then another space we're going to use the text function once again we're going to use M15 and then we're going to format the date however if either one of these fields are empty or if both are empty we are going to show nothing so that's how we do on this really cool formula the total earned is simply the sum I'm going to sum all of what's in here however I do want to check if for some reason our minimum pay if we've earned let's say we we've set 10,000 here and we see all of a sudden our earnings are only 1,000 but we have a minimum base pay that's our minimum pay so we're going to check if the sum of M20 through 29 is less than M9 then we're going to set to M9 otherwise we're simply going to sum there so that's what we're going to do we're just going to check to see so but as soon as our commission grows right let's say 30,000 right as soon as our our commission grows above that so we see now that our total Commission Now takes precedence over our minimum or base pay here so we can do that very good here all right very very cool so I'm glad I got to show that part to you so we kind of get a good understanding and then all we need to do is just save that payroll what that's going to do is going to save it up here it's going to be putting in a list here and now we can return back to it anytime we want we can add a new payroll or I can return back to the old payroll okay so what I'd like to do now is I'd like to go back into the ad and show you exactly how do we get this information saved into our commission tier database so how do we do that and how do we recall it so basically it's got two we' got two options we can select and that information is going to load up or what we can do is we can click save and we can save all this information into the database so we need to make sure that we're doing that how do we actually do that what we're going to do that using VBA Visual Basic to do that and we're going to get into it now it's walk you step by by step we're going to go into the developer tab then we're going to click on Visual Basic there's a shortcut alt f11 we'll get you there and inside here what we have is we've got three different modules commission tiers employee macros and paying commission macros okay we also have an admin screen here now the code on this is what's going to start the trigger notice that when I made a selection change something happened so that's going to be a selection change so we're going to start it out on F5 and we're going to go all the way down but we do want to make sure that we actually have a value in that column F so we're going to check on that on our selection change event so from the worksheet here and then the selection change events what we're going to be focusing on here and then what we're going to do is we're going to focus on a selection change from F5 through f99 and again we want to make sure that F and the target row that's the selected row there's actually a value we want to make sure it does not equal empty if both of those are conditions the user does make a selection between F5 and f99 and F and the target row is not empty then we should do a few things the first thing what I want to do is inside j6 I want to place that value so whatever we selected in j6 take a look at j6 j6 is going to take on that employee type so whatever that Target value is I want to place that directly in j6 so we can do that here we can actually just use Target value that's just as good target. value okay so that's going to place that employee type directly inside j6 and then what I want to do is inside B3 I want to place the target road now what that's going to do is trigger conditional format if we take a look inside some columns that were going to be hidden employee type row B3 notice if I change that you see it's going to change that conditional formatting when I select on something that macro is going to then put that conditional formatting row directly inside B3 if I select on it we go to a conditional formatting and manage rules we see that we've got a highlighted row based on the row of course it's that dark background with the white font and it's going to be based on the row that's in B3 and it's going to apply to all the way from F all the way down to 21 or however many you want we can put that a little bit higher okay so that's how we do that and then we have odd and even rows that are colored ingly odd rows are going to be on the white background even rows are going to be on the light blue background so that's how we get that look in there and so it's VBA that's going to set that row okay another thing what I want to do is I want to understand what datab B row that TI on if we take a look inside our commission tiers we see that we have some earning types here so oops oops let's go here commission tier types here so what I want to do is I want to know what row there's only one single employee type per row so I want to know what row it's on so we can use a named range to help us with that formulas name manager and we see that we have inside here it's here something called tier employee employe type all the way down here tier employee type so what I want to do is I want to search for that and I want to return the RO that it's located on knowing that we have a named range called tier employee type and that named range is going to grow using the offset formula so let me close that so that means when I look up here sales associate I want to know what row it is located on inside the database I want to know that it's located on row four so how do we know that what we can do is we can use a match formula so I'm going to match what's in j6 I'm going to use that named range that we just saw tier employee type and I want an exact match so we're going to use zero and I'm going to add three that's because our first row starts in row four so what that's going to do is going to return that database that's very important because I know know that it's been saved if I select something that has not been saved notice that field staff if I take a look here field staff is not in here so therefore it would return an error on the match formula but I've wrapped it around if error and then I'm going to show empty if there's an error and that is because there is if I look up field staff it's not located so that means I know that when I decide I'm going to save that I know to make sure to save it to a brand new row which would be row eight the next available row so we have that there but if it is been has been found we are going to display the row we are adding three onto that because our first row starts on row four here so we want to make sure to add three okay moving along inside VBA so we want that Row in B3 we want that employee type in j6 and then we're going to run a macro called commission tier load so commissioner that is the macro that it's going to take all that information here all the commission and all the information here and it's going to load it up it's going to take it all directly from this database that found row whether it's row four or five and it's going to load all the information up now notice that we have some data mapping here so I've mapped all the information to the associated cells L6 j7 L7 j8 has all been mapped here so we see that we have j6 we have L6 we have here j7 and so on and so forth and all the information has been mapped all the way up to the last and 10th tier it's all been mapped out so it's going to make programming a lot easier so we're going to go into this macro called com tier if I want to look for that macro to find I'll just click on the definition and what that's going to do is we're going to take us to this one commission tier load now there's two different ways that we can do that and I'm going to show you both ways one's common that we don't need to use both ways but so I've used data mapping it's just three lines of code so we've mapped it all and here's what we can do so first thing we want to do inside this macro focusing on the admin sheet we're going to clear out all the contents I want to make sure that everything has been cleared out and I want to make sure that all these now of course these are all formulas these are all formulas but I want to make sure to clear out all also from K12 all the way down to l21 so we're going to clear all of that out everything air so k 12 through 21 and all the fields Associated now if for some B4 equals empty that means we have no row right there's no row that's selected we can exit the sub right there's nothing to load that means if I select on field staff there's no row here that means there's no data to load here right there's nothing we need to add a brand new and we need to add the number of tiers and all the information however if it has previously been saved we can then load in the information so B4 is going to tell us B4 is not safe so we'll just put not saved we can exit out of there if it has been saved we're going to take that row that database row we're going to put it in the variable called com row then all we need to do is just run a loop from 2 to 26 why 26 if we look in our uh tier database here and we go all the way to the last we see column Z which is also column 26 is the last one so everything's been mapped all we need to do is just take whatever's in the found row and put it in K15 whatever's in here put it in l15 and so so on and so forth another way to do that that's exactly what I've done directly in here so we're going to run the loop from 2 to 26 we are going to take whatever's located in row one and the column the column's going to go from 2 to 26 it's variable so we're going to take whatever's in there that's the Range located k14 l14 so on and so forth we're going to put it in the admin what are we going to put in there whatever's found in the row and the column here so as we Loop through that we're going to add the data add in data so we can use a lot of data in just three lines of code another way to do that would be to run a loop based on the tier row and we can do tier quantity and then we could multiply but that's a little bit more confusing so I kind of commented out if you didn't want to map each individual one there's another way to do that using the rows and the column numbers but it's a little bit more confusing so I've commented it out okay so that's all we really need to do to load the data on selection change very good but now how do we actually save the data what if I want to add in some information here and I want to then save that data if whether it's new or whether it's existing I want to be able to save it with a macro now that macro has been let's just put in that has been saved so we can use the same macro whether we are saving or whether we are updating it we can use the same macro for that and that's called the save or update so how we going to do that if we right click here it's already been assigned we click assign macro we see that we already have a macro sign called Commission tier save or update if we edit it it's going to take us directly in this is the maer we just covered and now we're going to cover the next Macer which just save or update first of all I want to make sure that we have all the required fields in this Field's required the number of tiers are required the metric is required the earning type is required this is not required but this is required so we actually have five different fields that are required now inside VBA we could check if this is empty if this is empty but there's another way to do that what we can do is we can use count a in a formula whether it's in VBA or in Excel a worksheet within a cell we can use count a and that's exactly what I've done here so I've taken all of the fields all the cells in which data is required which is actually five total and I've added them up using count a if so you notice that this is five if one of them becomes blank it's going to go to four so what I want to do is I want to run a check if this value in B5 is less than five then we know the user has not filled out the required Fields if we click save update we're going to tell them with the message box please make sure to fill in all required Fields before saving so let's update that so that's exactly what I want to do it's before saving so if B5 is less than five please make sure to fill in all required before saving okay that's all and then we're going to exit out I need to make sure we exit out that's very important so exiting the sub because all the required field so we want them to do just that okay we're going to set the tier quantity to L6 and that's going to be be the number of tiers tiers so we're going to set that I want to know the tier quantity that could be helpful if we're not going to use data mapping here we're going to use the tier quantity but we don't have to so we can actually just change this to 26 I think it's a bit faster I was testing out both method methods and so it's unnecessary so we can comment that out tier quantity another way to do it but we're going to use data mapping much quicker so here's what we're going to do we're going to just map from 1 to 26 again we're going to do the complete opposite this time inside our database row inside our column we're going to take whatever's in that cell and we're going to save it so for example if I decide I'm going to add let's say again 2500 here and the commission is 5% and then 5,000 here and the commission not 500 I like that that conditional formatting is really nice 7% and then uh lastly we'll just do 10% so again saving and updating it it's going to make sure let's fill in that last required field that is important and then we're going to save it and it's going to automatically save we click outside and then back inside we see that everything has been saved already okay so that's all we're going to do is just run a loop okay that's going to save the tier data and all the data if we want to save another way to save that tier data we can use this but I'll keep it commented out okay great the last thing is simply to delete the tier and all we need to do is grab the database row and then just delete the entire row it's relatively simple we're going to check are you sure you want to delete this if it's no then we're going to exit the sub and then what we want to do if B4 is empty we're going to go to not saved it's going to skip right here in fact and that's just going to exit out so nothing to do we're just going to delete that entire row from the commission database very very simple and easy so that's all we need to do so let's in fact I didn't didn't tie this to the macro so let's do that right now we're going to tie it directly to the macro and it's called commission delete here commission tier and we're going to try it out deleting that one this one we just completed are you sure you want to delete this commission yes and it's going to delete the commission now we should probably clear out the contents of the the cells so I think that's important so we want to make sure that we're clearing out all of them so which is these information right here so we need to clear all that out as well so we can do that right about here very good so now that we have done that it's very very simple to clear it out there's nothing left in there and that's exactly what I want notice there's nothing left in the field T so that's pretty much for the admin and settings basic very very simple here we've got multiple pay frequencies we can just add them in however we think this background here that I've put in I've created on on Mid Journey and it's relatively simple if you like the backgrounds and the icons and everything I'll make those available on our patreon patreon is a great platform because every single week what I do is I create an updated workbook and another updated training so that means the features that you want to see that aren't in this training I add them on patreon also I may be able to focus on some items if you want me to drill down a little bit more on a specific formula or some lines of code that you didn't quite understand in the original training I'm also doing that on patreon it's also we have it on YouTu YouTube uh silver members that's the same thing as patreon silver members so there's no difference on those two membership benefits so the best thing to do is go ahead and get on I'll include the link down below patreon and that'll include all the things including this background so the background is basically we can delete the background add a background I'll insert it from a file and I'll take the background that I've been using which is this one right here you want to see what that looks like we can see it here and then I'm just going to insert it right and that's our background that I've created so that's all we need to do for that all right very good we now move our attention over to the commission rate calculator sheet in this sheet we can select on multiple employees when we select an employee the payrolls that we have previously created for that employee would get saved here we can then select on a payroll and that information will show up here so the first step would be to select on an employee when we do that we want to take the information for that employee that we be located in this database whether we've got an employee ID name the type the pay frequency and the base or minimum pay that I want to be populated inside these fields here so that of course all starts with a selection change event on our commission so that's exactly where we're going to turn our Focus to inside our commissions we're going to focus on a worksheet selection change event if the user selects more than one cell we're going to exit the sub out that helps us avoid errors on employee selection just as we did with the employee types in the admin screen if the user makes a selection change from D4 through a large number we want to make sure that D contains a value if it doesn't we're going exit out if it doesn't if it is not empty we can then continue on I want to do a few things the first thing is I want to clear out all the payroll data once I I want to clear out all the associated Fields with this and that also includes some hidden Fields I've got some hidden fields in column A and B that is selected employee row when I make a selection that row is going to change I've got also the selected payroll row when I make a selection on the payroll we're going to use conditional formatting row five is going to go here so again the employee row is going to be located in B2 the selected payroll is going to be located in B3 the selected employee ID is going to be located here now I've also got some hidden information I'm going to unhide it now so we can help us understand how this sheet works so I'm going to hold down the control I'm going to select on both columns and I've got a custom format and I'm going to change it back to General and what that's going to reveal is some numbers now this employee here in column C is the actual employee ID the number that's located in column e is the payroll ID that associated with that payroll so those are unique IDs and they can be very easily hidden using just some formatting so we're going to go down here if we remember correctly we use that custom format previously and that's exactly it that custom format those two semicolons we're going to hide those numbers but we'll keep them open for now and visible so I also want to take whatever that employee ID and I want to place it directly inside B4 I've got a named range because I want to take that employee ID so notice when I make a selection that employee ID goes directly to B4 and that is through this code here B4 here is going to take on whatever is C in the Target row and also the conditional formatting that triggers remember I want that row and I want to put that Row in B2 so that is the selected row it's going to go into B2 so all three of those things are going to happen and then we're going to run a macro called employee load but before we do that I just want to show you a little bit more information I need to know what database row that employee is located on how do I know that well if we take a look inside B5 and we can use we have a name range called employee ID if we go outside and into the form as a name manager and we select on employee ID here and we see that that's going to be an offset formula named range Dynamic on our employee IDs now I want to extract if the employee ID is one I want to know that it's on row number four and that's exactly what we've done right here using this formula very similar to what we've done previously we're using a match before employee IDs we're adding three and if if there's an error it's going to show empty that's going to ensure that we know there and I also want to know the next employee ID using the max formula plus one so that's going to get us the next one when we add a new employee we're going to need that we're going to need to know which employee ID is going to be that next available one so that's very important and then employee load is going to be false and I'll explain a little bit why that so that's it for the employee this is all for employee information so what we want to do now the next stop is that employee load macro if we take a look inside our employee macros and we scroll up a little bit we see that we have employee load right here so the first thing we're going to do is we're going to clear the contents of a bunch of sales I want to clear everything associated with the payroll so we're going to clear all of the basically all this information we have the same type of formulas that we had here so there's no difference here we've got a formula share that we alreadying over so we're just clearing this information out here that is specific to the tier so we're going to clear out columns K&L we're gonna clear any payroll data we're gonna clear any employee information and so that is all done through here see that the columns K through L here so everything's cleared out now if B5 is empty we need to make sure to select a correct employee to load right B5 is necessary I need to know the database Row for some reason there's no row we need to select on a correct employee although there should always be a correct employee so B7 is important now also I want to know that employee role that's going to be in B5 that's the employee row that's going to help us because I need to know what row to load again we've also mapped the data inside our employee days j7