Collaborate on Bill Template Excel for Product Management with Ease Using airSlate SignNow

Bill template excel for Product Management

a complete inventory management system inside Excel hi this is Randy with Excel for Freelancers and in this week's video I'm going to show you how to create this incredible Inventory management complete with sales orders purchases products customers suppliers and a whole lot more I'm going to show you an incredible way to add brand new customers suppliers or products that I've never taught before it's going to be an incredible trending you won't want to miss we're going to do it all from scratch every design element from scratch right in front of you starting from a blank worksheet I cannot wait so let's get started thank you all right thank you so much for joining us today I've got a really epic training today that you are not going to want to miss the inventory management system it's going to be absolutely complete with the dashboard we are going to be able to add update and sales orders we're going to have that we're going to purchase orders we'll be able to add update purchase orders products you're going to be able to add update search for products and also we are going to have suppliers and customers all of them on the same sheet and best of all we're going to build that all from scratch design completely from scratch right in front of you today so it's going to be an epic training you're not going to want to miss that in fact I asked just a few things for you if you could go ahead and click that subscribe and also make sure the notification icon Bell I create these trainings each and every Tuesday something incredible with Excel because I want to make you successful with Excel and not just teach you Excel we built these application templates each and every week it's absolutely free all you need to do just click the link in the download description there and I'll make sure to get this workbook template over to you if you do want to support the channel so many great ways to do that in fact I'll be creating an update to this template just as I do each and every week so if you have some ideas you want to add that I didn't quite add in this training you want something me to focus on or you want me to fix something I do that on our patreon platform and now also YouTube all you need to do is just go ahead and click the link for patreon and I'll create a brand new updated workbook along with a brand new updated training each and every week next Monday I'll be doing that so make sure you get your suggestions in and join the platform joining that platform also allows you to get tons of other benefits including a PDF code book that helps you know exactly what tonic type of code is in these applications along with early bird specials you'll get access to the workbook and training early and a whole bunch of other things so that's on patreon and that helps support the channel alright let's get started this is what we are going to be creating eating we're going to be creating an inventory management system we can simply search for a customer we'll have a search for customer we're going to be able to add a brand new customer if we want to in fact we're going to be using something that I've never used before adding a brand new customer is just as easy as we can do this we could just simply add it in if we want to do that and adding in a address here and then all we need to do is just click the check and that customer is going to be added in automatically so if we want to search for that we can see that we have now that search so really really cool search features not only are we going to be able to do that but we're going to be able to add update and delete records with this brand new method that I've never taught before not only for customers we're going to be able to have that same feature for suppliers if we want to add a brand new supplier we can do that and I'm also going to have that for products in fact we're going to have here's products if we want to do that for products we can search for any type of product just search in there and then the filter is going to come through so each customer supplier and product will have add new we'll have update we'll have filters and search if that's not enough for a training we're going to have purchases we're going to be able to create brand new purchase orders we'll be able to put it in the supplier we will be able to add items that we're purchasing here simply by selecting the items and we're going to be able to save and update that also we're going to be able to do the same for sales orders sales orders we're going to be able to add a customer here again adding that product here selecting from a drop down list of products we're going to be able to have that and then of course we're going to be able to save and update that we'll be able to also search for sales order search for North we want to search for previous we can simply do that we can do the same thing for purchases and sales and to top it all of course we'll be able to print the order add and save and update and top it off we're going to have a dashboard that's where it's going to show our total sales by month May profit 2000 Uh current year profit the top products on hand purchases by by month and a whole lot so we're going to be doing all the design from scratch and we'll see we're going to get to all the code as well I'm going to show you every single part it's going to be probably a little bit longer training than usual because it's a big application you're welcome to watch this training as many times as you want we're going to show you all the design aspects I'm going to start from an absolute blank workbook I've got a blank workbook right here actually it's a blank worksheet but I still have the data so we're going to go over the data inside that just a brief overview of the data we've got an admin screen with just a few settings we've got some sales tax defaults months and a date range I'm considering using we have some dashboard data this is the data that's going to cover for our dashboard I've got a customer list this is the original list of our customer we've got suppliers we've got a product list we've got an order list and we have order items so these are the items that are associated with every single order so we have all of that okay so what I'm going to do is I'm going to take this one we're going to start from scratch on this one in which we have this inventory management this is the same the main sheet that we have here and this workbook here however this workbook it's all programmed out so what I'm going to do is I'm going to take this template I'm going to slide it over to slide on another screen that I have that way we can focus on the blank one and I can use the one as a reference okay so we're gonna get started right away what's the first thing we want to do well we want to really use two columns just as I do in other applications two columns are going to be for admin I'm going to drop this down here so I can show the tabs and commands because we're going to be working a lot with the formatting and things like that these columns are going to be hidden so we're going to use these for admin purposes and then we'll hide them okay so another thing what I want to do is I want to add a title here but this title here for the group what's called Inventory management we are going to use I'm going to use it as shape a text shape for that because I don't want it cell based I don't want it cell based so we're going to call this again inventory management system and I'll probably spell a few things wrong so bear with me I'll do my best shape format I want to Center that and put it in the middle and I want to give it a probably let's say 30. giving it we're going to use this color this is going to be our standard color I'm going to give it this and then I'll bolt it and then I'll put it in just a font that's similar to something like Ariel roundabout okay I like that that looks good now we're gonna also have a logo and I'll put some shapes in and things like that in a little bit later I want button sets to go across here as you saw we're going to have six different button sets for customers so we're going to add those in the icons associated with that okay but first of all on this I do not want any fill so we're going to go shape fill on this one no fill and I want no outline on this so we'll do no outline okay so we're going to have a background that we're going to use on this a nice background we'll use a page layout and then background will be putting in a background now that what does that background look like well I've already got it stored I'm going to work offline I don't know why that always comes up and I'm going to use this background that I have okay so that's kind of nice background and we'll get rid of the grid lines in a bit so what I want to do is I want to put in some additional shapes right here saving our work as we go along as we always want to do now those shapes that we're going to be using we're just going to use this background so I'm just going to insert shape I'm just going to use this square shape and this is the one that we're going to be using for our menu okay we're going to give it a color the shade fill we're going to use this same color I don't need a line on this shape so we're going to say no outline on this I want a size so we're going to do let's say 0.6 and then we'll do a width of 2.2 okay so that's good I like the way that that that's the size of that then I want to give it some labels in here so we're going to do customers so I'm going to close customers this will be our first one and then I'm going to let's say right justify it and put it in the middle so that's about what I want but what I want to do is I want to put a little bit of a spacer in between that and also increase the font font size will set to 18. okay that looks good well we can also another way to do it is just to center it or left Justified and then move it over so maybe we want to move it over a little bit many ways we can do it we want a space we want to leave room for the icon in here so we're going to add that in what we'll do is we'll go ahead and right just fine then we'll give it a nice margin so we're going to use Ctrl F1 to add that and we'll go into the text options here here we're going to give it a right margin so what we want to do is right around 0.2 okay that's nice okay so I want to have a room for the icon so there we've got our customers now what we're going to be doing is I'm going to duplicate that and then we're going to create it for each button so I'm going to use Ctrl D and then I'm going to space it out ingly right about like here and then Ctrl d one more time d d and d okay I like the way that looks so we've got this one's going to be for customers we're going to add this one for suppliers so we'll put in suppliers then we want another one button for products so put in the products here we need another we want to show this one's going to be for our purchases our purchase orders is another way to do it and then another one we want to use for our sales order and lastly for our dashboard okay so now that we've got all that we're going to put in our icons also so now that we have everything set up just the way we like it as far as the button is concerned we also want connector lines that's going to help us too I also want to give these unique names so we can work on that and that's going to help us win the code so this one's going to be called menu item one and then I'm just going to copy the menu item text here and hit enter that's going to name that button I'm going to go here to the second one and put two likewise this one's going to be three this is going to help us in the code because we're going to be looping through these buttons we want to change the color so by having very similar names will help us run a loop through that and I'll be showing you that and this one last one is five so we have six different buttons all with very similar names just the number of them is different so this is one two three four five and six I'll add a little bit more spacing on the right side so we all have unique spacing on that we can format that that's another way to do that going into the text options we're going to make this 0.3 okay so that moves them over a little bit to the right margins we have a little more spacing we're going to add in our icons and we're going to add in some connector lines so I'm going to insert shapes and then a line and then what we'll do is we'll connect it here and here although they don't necessarily have to be connected we are going to give the shape outline the same color and also I want the size the weight of it's going to have to be right around let's say three point or something like that that looks pretty good so what I'm going to do is I'm going to copy this and I'm going to paste it and then I'll move it over here and we can do the same thing we can use connectors and that way when we space them out everything has to be connected you can also use Ctrl D that's also going to work again just connecting these and that way when we space them out they're all going to be although they're relatively spaced out just about right so we're just connecting them to the shapes and that's the last one now that they're all connected we're want to make sure that they're all in the same line I'm going to hold down the control selecting all the items I want to make sure they're all in the same line here and I want to make sure that they're all horizontally spaced okay that looks good we've got our all of our information here that we need for our menus we're going to add in our icons so I'm going to go insert picture this device I've got some icons saved right in here we're going to add them all so I'm going to adding them all all the ones that I've saved I'm going to insert that okay very good I'm going to set them all height right around 0.3 or maybe a little bit bigger than that I think we'll use a little bit and then some of them will size a little bit smaller okay so they're all size right about 4.2 now all we're going to do is just move from this add new I'm not going to use this just yet we're going to use these add new okay so we have this one this is going to be used for our customers so I'm going to bring this over here that's our customers here and then now what we want to have here is our printer this one's going to be we're going to use this a little bit later on this one here is with the dollar sign that's going to be used for our purchase this we'll drag that up over to purchases next up I want the sales order and we also have our dashboard so we've got our dashboard icon right about here we've got here this is going to be our supplier so an icon for the suppliers and we also have one check this one's going to be used in our form so I'll drag that over here this one's going to be for our products and lastly we have for our sales order okay that's looking really good I like the way that that looks now we just want to make sure again everything gets lined up and then we're going to give them very unique names so what I'm going to do is I'm going to line everything up just right vertically so that they're lined up vertically okay I'm going to undo the selection tool right here now I want to give it a very specific name it's important that we're going to name these These are going to call menu icon one and two and three and so on so forth so menu icon and then I'm going to copy this and then I'm going to add a 1 to this so it's going to be our first now keep in mind that the same number this is going to be important menu icon has eight characters plus the number menu item also has eight characters and that's important okay so this is going can be called menu icon one this one's going to be called menu icon 2 and you can assume that we're what we're going to add for the rest now this is very important then because we don't know when we select a menu item we don't know exactly what they're going to be selecting on whether it's the button or the icon but we want to extract that number from each one so it's very important that we can extract the number whether they're selecting on the icon or they're selecting on the button okay so I've named each one menu I come one menu item icon two and so on and so forth all the way to the dashboard menu icon 6 and menu item six okay everything looks really good that's just the way I want it now what I'm going to be doing is we're going to be selecting on these now I want to group everything together and I think that's really important so that it doesn't move we're going to be hiding and not hiding certain columns so what I want to do is I want to make sure that that group the menu along with the title always stays in the same place and of course at the same size so I'm going to select everything in my selection tool and I'm going to group it and the next important thing is we're going to use control 1 and we're going to make sure that the properties portion of this always does not move besides with cell so we don't want it to size ourselves but we do want it to move that's for the whole group okay so we can undo the selection tool now we've got our menu uh pretty much ready so let's go ahead and start with adding some information in the first one I really want to add is the sales and purchases so the purchases and sales order both of those are going to add in they're going to use the same exact screen so in fact I'm going to let's just add this and put a make this Center there we go I like that a little bit of one space too many all right remember I mentioned columns A and B we're going to use for admin use we're going to start adding some information that's going to be important for that now when each tab is selected I want to know what tab has been selected so I'm going to put that right here in B2 so we're going to put this we're going to call this selected Tab and whatever tab has been selected is going to go right here so maybe this is going to be something buy purchases and it'll be VBA that takes care of that for us okay so what else do I want I want to know when the order is going to be loading now order load so that means when the when we search for an order whether it's a purchase order or it's a sales order it's going to load so it's going to be a true or false I want to know that okay so now what I want to do is I want to know the order number order both purchases and sales have ordered numbers so I want to know what is the order number and I want to put that in here I also want to be able to have what is the row that's associated with the order row and that's going to be here so if we take a look at orders I want to know what rows associated with that that's going to be important we're going to go over some name ranges that are going to help us let's get this worked out what is the next order number so when we create a brand new purchase order or sales order I want to know the next order number so we're going to put that right here inside B6 I also want to know the selected order row Dot and then also I want to know the search order row selected order row we have Midnight use search order row so when the user runs a search we're going to put a search up here I want to know what rows Associated if they put in number one or order number two or whatever I want to know the row that's associated with that I also want to know what the customer or supplier remember in a purchases we're going to be selecting suppliers that we're purchasing from so I want to know the row associated with that if it's a sales order they're going to select a customer and I want to know the customer row so again we need to extract either the supplier row or we're going to select the customer row so we need to extract those and I want to know what rows associated with that I'm going to call this the customer the supplier row Associated okay next up I want to know the selected customer row so we're going to call this selected customer row now that basically means when we're selecting a customer I want to know what row when we have a list of customers I don't know what row the customer is on here okay so I also want to know the next customer ID when I create a brand new customer I want to know what id is going to be associated with that customer so we're going to call that the next customer ID now I also want to know the supplier row so remember we had a list of suppliers here I wonder what row has been selected so we're going to call this the selected supplier row next up I want to know the next supplier I used to kind of get the hang of the next supplier ID what is the next supplier ID number we need to know that lastly we're going to focus on products right so we want to know the selected product row and also the next product ID okay so this can help us so we've got all those let's give these a color here a unique color so we can differentiate those and also I'm going to give these a color here let's do this with in blue so we can these are associated with the order so I'm going to give those a blue color and also this one's going to be yellow because this one's going to be for every tab okay so here's the idea so let's let's justify these here we will right justify this here and I'm going to put borders all the way around the color is insignificant of the Border okay so that's it so that's all the admin we really need for this so basically when I make a selection on customers this changes to customers when I make when I click suppliers this goes to suppliers so that's how it's going to work that's going to be that's going to let us know so we can always look here we know what tap has been selected these we're going to be using very soon when we create the customer buttons and things like that okay so what is it that we want let's focus on the order right that's going to be both again we're going to use certain columns I'm going to use certain columns both for the purchases and sales order so it's going to be almost the same we do not need to use separate columns because I just need to change a few texts they're almost all the same so how do we do that well the first thing what I want to do is I want to set aside some information I want to know what is the number are we in purchases or sales order so that's going to be help important so we're going to use a formula here and I'm going to just basically run an outline of all the rows that I want to focus on so we're going to go all the way through H and maybe down to let's say seven here so I'm going to bring that so I'm going to format those this is going to be the top of our orders so that's going to be for both purchases and sales I'm going to give it a border we're going to use that unique color that we've been using that blue that's our theme color for this week we're going to wrap some borders around that so that's going to be the top here now we're going to move these over here we don't need them quite yet but very very soon we're going to be using them okay so what do I want here well I'm going to put some information up here so I want to put the order date here I want to put the sub total on the right and I want to put the tax if there's any tax and I want to put the total here all right so now what I'm going to do is I'm going to right justify these fields and I want to put these here in white so I'm going to make sure the fill Fields the user is going to be adding in so I want to make sure those are all in white and I'm going to format those cells and also give it that inside border of our color and not there and then here I'm going to give it that dotted line here that's to be sufficient and we can do a full line okay so now we've got our order date here so I've got I also want a supplier now I want to put either in this field so here is also going to be white I'm going to color this white here inside here what I would like to have is I would like to have some either the customer drop down list or the supplier here I want to have the label so either it's going to say supplier or customer how do we know the difference well when I select here I know it's going to be customer so if B2 is customers I know we're going to enter a customer here so how do we know that well we can do is just use if then statement so we're going to say if equals if B2 equals we'll put in customers let's do capitals because that's we're going to basically do customers then we're just going to put in customer that's easy that's all we need to do otherwise supplier and because we're going to use remember we're using the same thing for both customers and suppliers so I'm going to right justify that and so basically when this changes to customers right this is going to change otherwise it's going to be so that way we know what we can use I'll just change this to customer actually because we're just a single customer and a single supplier this is going to be a drop down list I'm going to format this cells I'm going to put a border at the bottom just to separate this here and we can use the top let's go ahead and use our blue color here I'm going to use the outline all the way around clicking ok so I want the name here and I want the address here so I'm going to format those cells and then also the border on the left and the right we'll get rid of those grid lines because they don't look too pretty here so view grid lines okay so now it's looking a little bit more cleaner all right so here what I want is the name I want to know what's it going to be is it going to be a purchase order and I'm going to expand this just a little bit so we can have a little bit larger so I want to know this is going to be called either purchase order or it's going to be called sales order right so we can use something similar to this I'm just going to copy this and so what is it if it's a customer's we know it's going to be a sales order so I'm going to put in sales order otherwise we're going to call it purchase order if it's a supplier we're going to purchase purchase order okay so now that we have that purchase order I'm going to give it a little bit of a color so I'm going to Center that and Center it and I'm going to increase the font a little bit change it to our our color so we can see that bold it up a little bit bigger okay I like that that looks good so it's going to be so basically again all this is It's a sales order or if it's purchases or anything else doesn't matter it's going to change the purchase order that's exactly what I want okay perfect so remember this field B2 is going to change when we click on those menu items to the top it's going to change automatically perfect so I like that that's looking really good so here is going to be we're going to have our I want to put the product in here I want to put the description in here next up we are going to have the quantity now when we have a sales order it's going to be the sales price when we have a purchase order it's going to be the purchase price right so we need to make a dynamic column header on this one again so I'll just copy this here and paste that right down here and then in this get into sales order right if it's if it's customers we know it's going to be sales price otherwise if we're actually purchasing something it's going to be the purchase price so it's going to be a dynamic how to purchase price okay perfect I like that again so go sales price and then also we're going to have the total here okay we're going to format that so what I'm going to do I'm going to highlight all the rows so I'm going to Center that we're going to format the cell sorry it's off the screen you can use control 1 as well and also I'm going to add in some borders here in the middle here here and here I want to make the font I'm going to put that keep it black but put it bold and then also I want the fill and I'm going to use the fill effects and we're going to use some theme colors for this and we're going to add this here this middle theme color over and uh yeah like that I'll just do it actually a little bit dark we'll do this medium and then to light so it's gonna be a light blue theme clicking okay all right so I like that product description quantity sales price again sales price if it's a customer suppliers will get the purchase price okay so see how we can dynamically change it based on whatever is located in B2 okay very good we also want to be able to search for an order so search Now search order this won't change regardless if it's a purchase order or sales order search order and then I want to have a white here so that's just going to be allowing the user to search the order I'm going to format again format those cells or control 1 works just fine we want to format those so the border around it giving that our distinct color border all the way around and then a dotted line in the center okay so basically what they want to do is they're going to put in the order number here it's going to search and display that order now also we've got to put some button sets in here so I'm just going to copy it gotta I got the right I'm going to control C this and then Ctrl V I'm not going to duplicate it because I don't want it in that group I'm going to bring it down here and of course I'm going to reduce the font size here is where we're going to have let's just say 12 is sufficient for us this is going to be our add new order so add new order we'll shrink it down I'm going to right justify that here and then also we can get rid of the format this shape we'll just use the standard on the text options we'll use the standard margin here so it will do .15 is fine and then bringing it a little bit less and then just reducing it to about 0.2 okay so I like that there that looks pretty good we want to make sure that we have room for the icon we've already added the icons here so we can have add new order print order save and then update so these are where those four icons are going to come into handy so we'll bring that right about up here okay that's looking very good let's change that to point one okay we're a little bit uh constrained with space okay looking good add new order so I'm going to just control that we need one for duplicate that I'm going to duplicate right down here right down here we're going to have this called favor update so just call it oops save or update and we're gonna use this button for saving new records or updating existing ones so saver updates sufficient enough if you want to have that's more of a center you can Center that and then we can just add some spaces here to do that that gives a little okay so what I'm going to do is I'm going to duplicate these buttons using control D it's going to duplicate them and I'm going to bring them a little bit over here now these buttons don't need the same width so I'm going to go in there a little bit smaller buttons so the width will do 1.35 on that one this one's going to be called print order so we're going to change this to print order and this one's going to be called delete order so we can delete order okay very good now these icons here I'm going to use control and I'm going to resize them and then we're going to change that to 0.2 and then we'll just go okay so that's fishing I'm going to bring all of them to the top now because we created those button first if I move them over they would be under these buttons so I want to move them to the top so all we need to do is just bring them right up to the top okay good so now that they're we just need to move them over okay we're going to go into right justify this one and get some space so we got a delete order we're going to print this one can be centered and then we can add another space here to give it some spacing proper spacing Okay add new order we'll take on the plus save update we'll take on the okay so that's looking really good we'll line things up make sure that the icons are all lined up vertically and horizontally I'm going to do the same thing for this everything good now we're going to make sure that they're vertically lined here everything look good including the buttons here we're going to just simply use that okay I like that that's the way it looks looks very good so now I'm going to save our work we can undo the selection and now we're going to group them ingly holding the control down I'm going to group them individually and then I'm also going to group them as a whole together that's important so I'm going to use that grouping them and we're going to give them a very specific name to this group and the reason we want to do that is because we need to hide this group if it's not a purchase order or not a sales order we need to hide the grip so very important so again holding down the control and all these if there are any other shapes with it we'd also group that I'm going to group them right here and I'm going to call this order group order group okay so we want to make sure to hide this group unless we're on purchase we don't when we're showing customers suppliers products we don't want or dashboard we don't want this group to show up okay close it making sure that we've got our spacing right that looks pretty good we've got a nice look to the screen already okay very good we can do a little bit of formatting we know this is going to be a short date format so we can change that to short date and we know this is going to be their currency or we can use accounting either one it's fine for that all right very good and the same thing for the purchase price and the total these are all we'll go down to let's say 108 that'll give us about 100 lines which is sufficient enough also giving them the currency account and then the product description now what I'd like to do is when I start adding some information here I want some conditional formatting show up here so I've got some different rules when I want to know exactly we're going to have really three different rules one is going to be for alternating rows and then I want to associate when I create a brand new one or a select a row I want to have a conditional formatting on that as well okay so let's add some conditional formatting for that and we're going to do that right now so now what I want to do is I want to add multiple rules and it's going to be all the way from D Starting 9 all the way to 8. so I'm going to do conditional formatting manage rules we're going to add some new rules so a new rule I'm going to use a formula now it's going to be under two conditions so it's going to equal and there's two conditions the first one is I want to make sure that there is a value in of course D9 however in this case I'm going to move one row up to d8 and that means that we're going to color every single row and of course it's every single row so we don't want the dollar sign before the eight and that means I want the first blank row colored and I'll show you why that's important d8 does not equal empty and the next condition is we're going to use a different color for both odd and even rows so for even rows mod row two so for those I want to give them a color so I'm going to do a format and we're going to just do a fill we'll do a fill we'll do let's do a little bit lighter color so I've got some lighter colors here I'm going to use this as a lighter color dollar here and that actually that's about the same and we'll click OK and I want some borders on that so we're going to use our color here I'm going to use our consistent color and I'm going to use a dotted line on the left the bottom and the right okay so this is going to be I'm going to copy this this is going to be only for even rows and so there has to be a value in the row above clicking OK and now what I'm going to do is I'm going to do a one another new row but this is going to be for even row so I'm going to paste in that excuse me for odd rows basically what I want to do is this is going to be a one so we're going to do is let's do backspace one okay good so now what I want to do is I want to format those very similar except the fill is going to be white and the border is going to be that same color border also on the left using the dotted line the left the bottom and the right okay so it's going to give us a nice look and feel so as we add data the rows will automatically and we're going to click apply okay so now by using row eight you see how this row is colored I want that that means as soon as I put something in here the next row so it shows the user the next rows available for that product we'll be putting in the product names here and that's what I want now I actually want to add one more rule when I select a row I want that row to show up right there's additional features that we can add that row is going to be based in what's in B7 so if I do 12 that means I want that row to show up very differently so how do we do that I'm going to go ahead and manage those rules and I actually have to make this I'm going to make this all the way to 108 and then 108 that's going to give us 100 rows so 108. okay so that'll apply to all the rows so now I'm just going to copy that that applies to and I'm going to do a brand new rule and then use a formula and it's going to be equals based on the row where's the row it's in B7 so B7 equals the row okay now what I'm going to do is I'm going to format that but very distinctly we're going to give it a fill and the fill effects and we want to make sure that we've got a fill effects of something based very close to our our color I like this this looks pretty good we'll go down to about here and that's a nice color I'm going to go dark color and then a font of bold and then White and the only other thing when you have a selected row which is going to be our selected row as we select it you want to make sure that this is on the top you don't want you don't want it down here you want that selected where to take precedence all the way on the top so clicking OK all right that looks really good okay so we can see as we change that we know that selected row now I'm not sure how let's change that to 11. okay the only other thing we needed to do is we needed to make sure that we apply to all the rows up to 108 so I want to do that now copying this and pasting it in here is going to ensure that it's also the row 108. okay clicking okay because there's a lot of features I want to add to this in the selected row now when we sold make a selection change that row is going to change we're going to use VBA to do that okay very good so we've got that as we clear we see that we have the information all right and we can clear this out too so everything's looking really good we've got the emphasis the most but I also want to put the order number here and I want to put is it a purchase or is it a put the order number now we know know the associated order is going to be here so we have the order here so if I know the order I want to put that order number here how can we do that okay so what I want to do is basically just put in whatever's here and I want to put in so purchases let's equals if I forgot the questions this equals customers right then we know excuse me if we did purchases then we know it's going to be a purchase order right equals purchases we'll do quotation mark purchases then we know it's going to be a purchase order so that I'm going to do purchase order purchase because I want to put that purchase order number in there purchase order and then we'll do a space and then number okay otherwise what otherwise it's going to be a sales owner sales order number we could use the number and okay so then all we need to do close it out and I want to put what is what is that sales a little space if we want to add probably no space then what we can do is whatever the number is where's that number located it's located in B4 okay and then that's all we need to do so now we see it's a sales order number one so I'm going to right justify it and then I'm going to give it a bold here give it a look Put It Center here yeah that's I like it centered here increase the font so we see the sales order number one so if this were to change to let's say purchases it would say purchase order number on purchase okay so then it's going to change the purchase order number one so we see how it becomes Dynamic again based on that all right very good so we've got the basis of it what I'd like to do is I'd like to put in some named ranges here and I like put in some formulas here before we do that let's go over some of the named ranges that exist just a brief overview here year again a list of customers here we have we have a list of suppliers here of course with a supplier ID and all the information we have a list of products here and some name ranges associated with that we have a list of orders here with the order date the customer or the supplier basically the name date and the total and we have order items these are individual items associated with the order the order number the order type is at a purchase or a sale the product description quantity price and the row and the database which we'll be going over so that's it so I've got some named ranges that it's Associated let's go over some of those named ranges right now so formulas name manager and we'll take a look at some of them we're going to skip the criterias we have a customer ID so basically all these are using offset formula which we've gone over in the past but basically offset is going to allow us to dynamically increase that name range as we add so we've got customer ID I've got customer name here I've got let's take a look at order ID so we got an order ID for based on all the orders I have the order total so this is going to be we're using these for our graphs in order to type here so we've got an order type and we've got order item quantity and we'll go over these a little bit later on I want to go over the product ID Products ID product name that's going to be helpful and lastly we're going to go over suppliers supplier ID and supplier name okay so now that we kind of understand we just have different named ranges we can start to fill those in what is the order row if I take a look at the orders I see the first order row starts in row four so if I'm going to return if I want to know what row in order number one is I can use a match to do just that so we're going to do that equals if air and we're going to run a match what are we matching and I'm matching on whatever is located in B4 matching I'm looking up whatever's in B4 and I'm going to use the looking up I wanted in the order ID so order ID here this is the one that I'm looking up and I want an exact match so we're going to use zero on that now we're going to add 3 because we want the row Associated if there's an error we're going to double spaces and that's going to tell us order number one is on row four what is the next order row if you've seen my videos before you kind of understand this but the repetition helps you can use it if there in case there's no data I'm going to use the max of all the orders ID as long as they're numerical it's fine I want to know the max number and of course I want to know the next one because that's going to get us our next if there's no data we need to put in something so we'll put in one that's okay perfect so the selected order row we went over that already the search order row is going to be based on one so if I put in one here and I'm searching for one here let's left justify that I want to know what row is associated to make sure there's a row so it's called the search order row it's maximize this and it's going to be basically exactly the same as this except we're using a different source so we're going to copy that I'm going to go right down here but instead of looking at B4 we're going to look right here into K4 so I want to know the row that's associated right so if I change this to 2 this is going to change to 5. that's how we know as long as there's a row here we know that there's an order that they're looking up okay now I want to know the customer supplier row so we need to use again I'm going to use an if statement because we don't know if it's a purchase order it's going to be a supplier if it is a sales order it's going to be a customer so I want to know the row that's associated so we need to use an if there so again let's do that equals if this is purchases so then we know we're looking up a vendor vendor right so then what we're going to be doing is we're going to use what I want I want to know the row that's associated with that based on the name so we're going to use match again match what am I matching here I'm matching whatever the supplier is located in here and I'm going to look it up in the supplier name right supplier name we're looking up the supplier name and we want an exact match and we want to add three because it's the row that's associated with that otherwise what if it's false if it's not purchased we're looking up a customer and we're going to have to wrap that in all if air as well so then in that case we're going to match based on a customer so we're looking at the customer name here and it's going to be based on the customer name range and we want an exact match and again we're adding three okay so that's going to be the end of our if statement but I do want to in case there's an error I'm going to wrap it all in a fair if error and then what we'll want to do is just going to show empty okay perfect okay so now as long as we have a this should be supplier right so if we take a look at a supplier and we see ABC companies one of our suppliers and we know that they're on row four and I put an ABC company here we know we want to make sure that this row goes to 4 which is correct that's exactly what I want okay very good now we're going to get into the selected custom Bureau the next customer ID we can use this select the custom row when I have a list of customers and I select a customer it's going to show up here the net customer ID we can we can know that if air we can add that in now it's gonna be basically all the customer IDs plus one if there's no value we're going to return one okay so we need to know that oops customer ID the max forgot the max if error Max we're looking for the max the customer ID Max that's what I want okay so now that we have the max and I also want to do the same thing for the supplier and the same thing for the product so again equals if air we're going to use the max based on the supplier ID supplier ID plus one gives us the max if there's no value we're going to return one skipping two we want the next product ID we're going to use equals if error same thing maximum product ID plus one there's an error okay perfect okay so now we have the next that we that's gonna help us because when we add new customers new suppliers or new product we need to assign that brand new ID so we need to know what the next one available is okay very good saving our work now what we want to do is I'm going to add some named ranges remember I've got a product name here so I'm going to go to let's say d108 d108 and then I'm going to go all the way up and I'm going to add here add some data validation so here data validation here I'm going to make a list value and I want it based on the product name if we're not sure we remember we use F3 is going to tell us what it is so F3 will do that we can look up product name clicking OK and we want an error alert so that's going to look good okay so that's a drop down data validation list for the product ID so when I select a product now we can see that we can select the product of course we want to automate the description and we'll be getting to that soon okay so great so we can see how that's going to work out just fine everything looks good on our order all right now we're ready to add in some of our customer information again when we're going to be doing this when we run these tabs what I want to do is I want to hide certain columns and then show certain columns so what we're going to do is we're going to slide all the way over here and starting out right around P column P here that's okay we're gonna I'm gonna expand this column here so in column p is where we're going to put that first customer information so we're going to have up here inside let's start around right here I want to copy and paste so I'm going to go into customers and I pretty much want all of this here so I'm going to copy some data too and some conditional formatting and that's going to help us so I'm going to copy this and back inside our inventory management right around P6 is where we're going to add that in so P6 I'm just going to paste it right here okay so here's where our customers now up here we're going to ask some additional information starting also in up here I want to add in a search customer and a button up here so that's where we're going to play in as far as from P all the way through X so we're going to go down here I'm just going to add some borders we're going go into the format cells F1 will do we're going to use our color here our current color I can use a thick border all the way around clicking ok ok so our customers information is going to be here now don't worry about this background because that's all going to change so the idea is this basically what we want to do is I want to have a search customer so here we're going to search customer uh just one just one customer that we'll be searching for or searching for and then inside here we're going to have this is going to be white so I'm going to add this to White this is gonna be a color putting some borders around here so we can format those cells again once again going in adding we're left lower and the right and then of course the dotted line in the middle okay so the users are going to be able to search and then what have those customers come here now we're going to add some conditional formatting in here which we're going to get to in a little bit but I wanted the additional information here so I like that just the basic information will have a button here called add new customer so that's going to be important so why don't we do that right now we can just use this so we're gonna I'm going to copy this and this and I'm going to copy it I'm just going to bring it right over here I'm going to paste it right in here so that's going to be our ad name we're just going to call this customer instead of order so customer okay we'll also give it a name so it's a distinct name for this one and we'll bring it over here adding enough space in here okay so holding that control makes your space and then we're going to group that together and we're simply going to call this add new customer so add new customer that's the button that we're going to be using for that that's button that will show up when we select the customer okay I like that that looks very very good so now what we want to do is pretty much do the same thing for our next which is going to be suppliers now where do we want the suppliers to go we want to leave a certain amount of space and we're going to put those probably right around a a so here's where we're going to put suppliers again you're going to do just the same I'm going to take the supplier I'm going to copy this maybe the first two supplies copy this and then go back into our inventory manager inside A6 or actually right down here aa6 pasting that here so now we have got our suppliers here and then the same thing so I'm just going to copy and paste all of this and paste it right in here so instead of this it's going to be search supplier and then of course our button is going to change as well so this we're going to be search supplier and then both of those uh we're going to write justify here and we're going to change this to add new supplier okay very good things are moving really fast here now all right so continuing on so we're going to size that button ingly and we're going to make sure this icon is uh 0.2 all the way around that's the way we want it if you see that it changes all you need to do is just make sure that we're not locking the aspect ratio so we can change them both at the same time of course when you change the group if they're grouped as a size of course it's going to change well that's not what we want 0.2 not two okay very good so we've got our supplier button here ready to go so we're going to change the name to this we need not this to be add new we're going to change it to supplier make things easy for us and consistent so that's going to change the supplier and as you can imagine we are going to do almost exactly the same four products products also important so there's going to be some consistency with what we're doing again so we're going into the products here inside the product sheet I'm going to scroll all the way up I'm going to copy that and along with the first row so I'm going to copy that going back into our inventory management and then we're going to slide over here to right around let's say a l and I'm going to paste that directly in here now we've got our suppliers again copying this here we're going to update that and then pasting that right here so we're going to search product here this is not going to be supplier but product where we're going to search for that and then we can add that in now what we want to do is we also want to make sure that we have the name of the button here so it's not going to be obviously add new it's not going to be supplier we're going to stretch that out to make it look just right and we're going to change this to product and then of course the button name as well needs to be updated to instead of add new supplier it's going to be add new product okay very good I like that we'll get everything centered just right so that's what we're going to do that no so the idea is when we change or edit or clear this list we want all the products to show up and of course everything's going to be showing up directly here as we select it so those columns are going to be changing let me highlight those and we can Auto size those columns here we're going to do the same thing here and then also if we don't want this size and we can always hold down the control and select all these buttons and making sure we're going to use also control one and want to make sure that inside the properties and we go down here move but don't size with cells we certainly don't want these buttons moving or sizing perfect I just like the way that that is we do want to add some consistency to the borders here so we're going to do the control all the way and make sure that we have our borders that are consistent that blue border should go all the way around adding that consistency it's very important in these applications and we can also put in some black Borders or blue borders however we want inside there so we'll go ahead and change this and then I'm going to highlight all three of those and we're going to add a consistent inside border going to be that blue so formatting those cells we want that consistent using that line inside borders here and we can set the top here to that blue okay that's looking really good clicking okay they're all the same I'm going to save it now we're just about ready to create and make those menus active right so when we want to select on those menus not only do I want to change but I want to change the look and the color of these menus as well meaning that that selected menu the color is going to change to a darker color because I want to differentiate between the other menus and the selected alright so let's go in we're going to go into VBA and we're going to make that happen so we can start to work with whatever selected tab we have now since this is a bit longer training I've already created some of the macros that are going to help us so let's go ahead and take a look inside the VBA developer alt f11 is the shortcut Visual Basic and we're going to take a look at some of those so we've got again this is our sample one that's what we've been working on I've got that handy on the other screen this is the one we're going to be working on we've got some code on the invoice manager that I've commented out I've got customer macros menu macros order macros product macros and supplier macros so they're actually relatively customer on them product and supplier super simple right because it's almost the same thing menu macros is what we want to go on so menu select this is the macro that's going to run now I'm going to go over every step of this code sometimes I like to write out code but this is a little bit longer one so we can speed things up but don't worry I'll go over every line of of code so basically the idea is this I want to use a single macro so I'm going to hold down the control button on regardless of whether button or whatever icon I select I automatically want to use the same macro and have that happen so I'm going to right click here I'm going to click assign macro and that is the macro that we're going to use I'm going to use this workbook only and we're going to hit menu select now what's going to happen when I select it well when I select it we want the menu number I want to extract the menu number the menu number is going to be in this case 1 2 3. now we can get that menu number from either the button or the icon how are we going to do that well if we take a look here we see it's menu icon what if I get rid of the first eight characters what's it going to leave me with it's going to leave me with the number what if I do the same thing here what if I know the button I've click on I get rid of the same the first eight characters it's going to leave me with the menu number and so by knowing the menu number I can easily figure out which one was was clicked and and also I can take the tax whatever text is in the button here and I can figure that out now that text of course is only in the one that says menu item so if they click on here I can't take the text from whatever they clicked on but I can take it from here okay so understanding that let's move forward with that we're going to Dimension the menu numbers long and I want to know the menu items along the menu item okay so the menu number is what I said we're going to remove the first eight characters using the replace statement now the application caller is the name of the shape that called it now the name of the shape could be in this case it could be menu icon one or it could be menu item one so regardless we're still going to take away getting the first eight characters and we're going to replacing them the