Create This AMAZING Excel Application that Tracks Purchases, Sales AND Inventory [Part 1]

hi this is Randy with Excel for freelancer and welcome in this week's training we're going to be going over an inventory manager and once again we'll be creating it from the ground up starting with a blank workbook and then going over every field every table every conditional formatting until we create this amazing inventory manager this will be part one of a two-part series so I'm looking forward to bringing this to you let's get started ok I'm really excited to bring this inventory manager to you I've got a lot of requests so let's get to it we're gonna start with a blank workbook and the first thing we want to do just like we did last week in the contact manager we want to create our first two columns that are reserved for admin so we're gonna color those gray those are always going to be in my videos often used for admin and we can hide those columns later on when we complete our project so that's an important distinction that we have often next up what I want to do is I want to create a title so we'll increase column excuse me increased row one and then we're gonna put our title in there we'll call this inventory manager and then now we'll go ahead and change the font to my rate probably it's a pretty clear font and we willtake will probably increase the size as well my black that's pretty good one and then we'll center that and then maybe if forty-eight or so about that's pretty good size and then italicize and we're gonna give this a color I want a theme on this we'll make this one green so we'll call that a dark green then I'm gonna give this all the way a fade let's just highlight that and give this a fades to the background color so we're gonna for Matt those cells and we're going to color the background so when we go to fill then we're going to go to fill effects and we'll give it a slight probably a three step background so this will contain two and the will contain one so we'll go from the dark green to a medium green on this one darker to lighter and then on the row below it we're gonna go another fade and then we're gonna go to our main background color so format dis row as well we're gonna go to those next green so we're gonna con the fill effects on this one we're gonna go from that medium green to the lightest green and that lightest green is going to be our main background color here so we'll go down one step below and then we'll go ahead and give our base color here we can raise this up a little bit in a moment so we'll give it that base color here and then that'll just be the light green which is our main base color which is that light green so now we get this kind of fade effect which is nice and so on this I'm gonna keep C column empty just as a space so you can increase your decrease and I'm going to start our our data here in column D there and then what I want to do is I want to create fields for our inventory manager the first is I want to create like let's say a customer or a vendor so we'll start out a customer and in this one I want to create two different types I want to create both the purchase and the sales I want to use the same screen for both of those so this field could be customer or could be vendor and I'll show you how that's gonna work later and we'll skip a row and then we'll just move to the product number product number and then I want to add some product details in there so let's go ahead and add some product details let's go ahead and call it product product name and then let's see the standard purchase standard purchase quantity I want to know you know if we set a product so we want a standard purchase quantity and these are product details so that's gonna be very important for products so we're gonna have both sales purchases and products all in the same screen and I'll show you how that's going to work and then sales quantity here so that's going to be our first column of data and let's just kind of make these why just so we can see that there they're gonna be feels we're users into that and then and then I want to do another column over in let's say G so we're gonna skip one have one space or column and then G we're gonna put in some more details so want to put it in let's say a transaction date here and then skip to and I also want to put in the sales price that's gonna be the item price there and then I also want for the product information these two rows are gonna be for product details right so we're gonna be able to hide that information or show it so I'll make that distinction very soon but let's go put in the product type because I want to know the product type and I also want to know a product description product description here so that's very important I want that and then I want to put the total here I want to have the total because it's gonna be the quantity times oh so this would be a formula and the idea is what I want to do is I want to have both sales and purchases here so but I want to alternate between purchase and sales so we can do that with a kind of a button type of thing so let's go ahead and create that button now we're gonna use shapes on that so let's insert a shape here and let's go ahead and enter the background of that button we'll use an oval here and we'll create just a background of that and let me size that how about let's say about a height of point two two and then we'll give it a width of maybe point six five or so and then I want to also zoom in I want to round those I want to round it out so let's go ahead and create that kind of round shape there that's the kind of one I want I want to own a round shape let's code the background our border similar to our theme so we'll use the shape outline the same of our our theme color which is the dark green and we'll give it a background maybe so of a grey or something like that because it's not going to be the main focal point the button itself will be and the button is going to be a circle so let's go ahead and insert that create a circle on that and let's go ahead and call it that and then we'll just give that circle let's say point let's create make sure it's even point one eight because I want it to fit within that and I'll show you how that works not eighteen point one eight and then also 0.18 on that and then we'll give it a shape color of let's say green that'll be good and so border shape fill of green let's go let's go to filled green and die let's do then outline a bit a little bit darker color there okay so that'll look good and not so much of a not so much of an outline really almost no outline but just a very light outline there we go okay so what I want to do is basically I want to be able to click this button and have it move from here to here to here to here right and switching between purchase and sales so we have to designate what is purchased and what is a sale and so let's go ahead and do that let's shrink this border we don't need this border it's so large we can shrink it down the outline of this can be also smaller it's a little bit big right now we'll set it to a weight of just let's say 3/4 a point that looks good okay so the idea is to use this as a switch so we can switch between purchase and sales so let's go ahead and make that distinction now and we'll go ahead and add in a text label here so this is going to be for purchases and I'll do it all in caps purchase I want to show purchases on that there and we'll sender that and we don't need any background or any any fill or any border on that it's just gonna be for label purposes so no outline no fill purchase and we want it centered so that's gonna be for purchase and also let me go ahead and we don't need any padding on that so let's under size and properties move along to and we'll set that to the text box we can we can set these to zero so because we have no border so it's no need for that alright so we've got that good let's go ahead and copy and paste that we can use it for sales this one's going to be sales on this so I want to switch between sales and purchases and if we move when we click here it's going to go to the right for sales if we click here it's going to go for purchases then we're gonna make changes based on that alright that'll be good so we'll use VBA to make that but right now I want to set up our admin area so the first is I want to know what transaction type so let's it Tran transaction type and we'll start it off at purchase purchase so we can set that up and we also want to know the selected row we're gonna have a table of transactions below here and I want to know what the row is for that so list is the selected row for the transaction so we'll put transaction row and we also want to know if it's a new transaction new trans and then that can be true or false so a market true for now we also want to know if it's the transactions going to be loading that's very important those three items if the transaction is loading or not when we select a transaction from the items from the table below we want all the data to appear here so we need to know if it's loading or not so we will set its if it's a loading transaction load and that's gonna be true or false all right so we've got that let's size it up and let's color those differently so we know that their admin will give it a say an orange background so we know give it their admin color and also put some borders on it so it's clear that that those are going to be admin I also want to designate for the products we're gonna be adding new products and updating products so we need some product information on that so let's go ahead and add in some their product row we need to know that we also need to know if it's a new product and we also need to know if its products loading so kind of the same three types of fields product load and that's gonna be all true or false so let's just say you know well the product row here true this is gonna be true or false and this is gonna be true or false as well so those items are important so let's go ahead and color those give it the same information and let's say we're going to use what table is a tip will probably start right on 17 so let's I'm going to use 16 for the mapping so let's call this a hydro and we're gonna have we're gonna use this for our table so our table we're gonna leave this blank C is gonna be 4 spacer so we're gonna start our table right here on 17 and our mapping will go in 16 so we'll color this gray all the way to say K and I want to call this gray because I'm gonna hide this we're gonna use a firm mapping and I'll show you how that works in just a little bit so let's go ahead and color this gray this this row is intended to be hidden our actual table headers were started on 17 so let's put in that information we want transaction type and we want a vendor or customer they're gonna go in the same row and then next up we want probably transaction date we all need all the information from the above fields into this table below product number we want that the price of that as well as the quantity and the total and then maybe some notes so that's going to be our table and then we're gonna have our data below here so let's clear that comes clear the colors out of that and we'll add in some information some data conditional formatting as well let's format this Center those and that will adjust the column Heights that's too much let's bring it over here the reason it's too much is because we have to manage the center this so we can do that merge and center now if we automatic and we need a little bit bigger now if we automatically click those there we go we're almost there we got to just increase some rows notes of course is going to be larger we'll need some for that the total so we're good we'll just well size them accordingly a little bit later on but they're okay for now well you will hi this is going to be our header row so let's color that accordingly will format the cells that's off the screen here let's scroll up a little bit we will highlight this format the cells here and then we'll color those we'll give it a dark green border same with our theme color and we'll go a little thicker on the outsides and the insides we will keep the thin line here and here and then we'll give it a filter color here on the fill color here fill effects a little bit of a fade as well working with our theme all right that'll good be good there you can see it there okay so we've got we've got our our make it bold and green color that green the dark green so now we've got our theme and our header covered we will add our border in here as well so right click that same border and I'm gonna format those cells sorry that was off the screen a little bit and then we use that same color but thicker borders on the side and we'll use the dotted line for our center dividers we won't use any horizontal I'll use conditional formatting for that let's add in our conditional formatting right now for our table new rule we're gonna use mod and that's gonna be our formula that colors equal mod and this is going to color our even rows we'll give it a light green format that and I know there's quicker ways to format a table but I want to show you how it's done here we'll go with that light green but maybe a little bit lighter than that so that it's contrasting and we'll go with this little bit lighter there okay so now we've colored even rows using mod row 2 equals 0 and there we go okay so our tables set and we have our let's go ahead and work on our information here this increased in size which we don't want because we need to set it point six five it's what we wanted that the reason is is we have to make sure that we size do not size cells so let's go ahead and set that up right click properties we always want to make sure that we do not size with the cells so that we don't have to continue changing them move the don't size with cells that's the setting we want so now we can reset the size back to what we want zoom in and shrink this down now that it won't now as we increase or decrease our column widths our shapes won't move around so that's good we can bring all this over a little bit so that it's within the area that we're focused on all right there we go and bring that over here this way everything centered over we're gonna use this area for our for our header row so put our buttons here we're gonna create add new new transaction buttons delete transactions add photos and things like that this area will be for our title and our title is going to be based on our or types so for example if this says purchase it's gonna say purchase transaction so let's go ahead and write that in now I'll put it somewhere about here and let's call this equals because it's going to be based on the type so equals if and then say we say this equals purchase then we want this to equal purchase purchase transaction otherwise sales transaction so that way we can have both okay good now we are set okay so now our title is going to let's increase that we want we want this I want it middle and I want it bold and I'm going to increase the font so that we can alright that's good and italicized bold and will underline it too there we go so now we know it's a purchase transaction but if this changes to sales then the title is going to change as well we can even make that a little bit bigger so it's clear I want to make sure it's very clear what type of transaction that user so when they change this when they change it it's going to change automatically in fact let's go ahead and write that Macker right now so we can quickly change it back and forth we're gonna go into the VBA and the Visual Basic and you know what before we do that let's go ahead and save this project we don't want to lose any of the work we've been doing always save it all right inventory manager and then we'll give it an XLS or manager and then make sure it's got XLS then this will contain macro so we want to make sure we give it that there we go in alright so now we've done that and let's go create a module insert module and we will call this module let's give it some the sheet macros because I want to create some macros based on the sheet so we will give it a name and the properties and sheet macros alright so those are macros that we're gonna focus on the sheep so that's gonna that's gonna be our starting point all right so let's now that we have that module we can create a button sub move but button we can use the same macro for both loops left or right so that's what we want to do now so we've got a macro before we continue with our macro we want a name our shape so it's always a good idea the part that we want to move let's give it a name back into that we'll call this little icon we'll call that move button so that we know okay now that's done we can set up with sheet 1 we're gonna be working with that so we can focus on that let's go ahead and say if we need to know if we're gonna be moving our shape back and forth we need to know what the current condition is in other words I need to know if it's purchase that I need to move it to sales if it's currently sales I need to move it back to purchase so from left to right right to left we need to know that so let's go ahead and first determine what our current status is so we can write an if-then statement if dot range B b1 equals purchase then do something okay else do something else okay so if it's purchased what are we going to do well what we want to do is we want to move it to the right if it's purchased so let's go ahead and write that in dot shapes move button dot increment increment left 30 that's going to move it to the right to the right left would be negative 30 and then we also want to set B 1 2 sales right if it's currently purchased we need to make it sale so dot range e 1 dot value equals sales all right so we've got that covered else what if it's not if it's else it's gonna be pretty much the opposite we're gonna move it negative 30 else negative 30 right the other direction and then we want to set this to purchase B 1 should be purchased so copy and paste that and set it to purchase that'll that'll set it up right there let's take a look at that and see how we have but there's more we need to add in this macro a little bit later on but for now we just want the simple button movement and we need to assign that macro to this button I'm gonna sign this macro to both dish shape and the shape to both of them so I'm holding down the control on both right click assign macro we only have one macro created so move button all right let's take a look all right let's set is currently on purchases so let's set it to purchases and now we can click on it all right that's perfect now that's the one we want and we see at this name changes so good now we've got that let's move this over a little bit to the left just to line it up let's take a look now soo-min take a look so we can see how that looks zoomed in it's not quite centered as I like it but we'll fix that in a second all right let's Center everything highlight over it for a line middle there we go now it looks good that looks just right okay perfect good now we've got our now we've got our macro that switches between purchase and sales and we're going to need that because there's gonna be a sales transaction or a purchase transaction okay so we're good with that let's continue on all right let's color our fields in white I will select on our fields here and we want to make sure that we have just those fields where values are gonna go in white so we can clearly see them apart okay and now also what I want to do is I want to make the distinction of what is a purchase and what is a sales not only by the title but I also want the colors so let's go in set these colors for sales in blue and purchases in red that's kind of a theme that I want to give it so that's gonna be important and let's set the product description a little bit wider do that and that's we will merge and center that where it's the center and then give it off to the left so it's got left out of justification note and we also need notes here too that's important notes we'll use the same thing for here I want it all alright good notes should also be merged merge and center and left accent merge Center and then left justified okay that's good that'll give us our fields as we know we're gonna put a picture in here and some buttons in here so that we can add pictures and I also want to put the quantity quantity on hand quantity on hand so we know how much we have and that'll go in here let's just put a spacer like five and that'll be a formula very soon all right quantity on hand all right so in order to differentiate between these the purchase and the sales let's cut make some colors and then we'll use conditional formatting I'd like to color the font of these blue on sales and a darker red on purchase I look like color these right now blue let's give it a blue color and let's choose a color it's a little bit standing out maybe this blue here that kind of stands out okay we'll use that blue because I really want to differentiate and we're gonna use both sales and purchases we need to differentiate between those colors now I'll also give it a border color which is also going to be different for both purchases and sales so let's create a border and the reason is I'm highlighting these fields in a way that it's independent so when we right-click and format those cells everyone's going to be formatted the same and let's give it a border use the same color which is this blue one here and I'll give it a all the way around plus a dotted line in the center which is going to be our divider that is our data I'll keep good that's what I want for sales and now for purchases I'm gonna have a darker red but I also want to color the background of it so let's give it this faded background I'm gonna do a slight fade of a blue so we're gonna go from green to blue I don't know if it's gonna be the most beautiful but I want to differentiate between sales and purchases and I want to make it very clear because just that the title is not enough so fill effects we're gonna start out with this green here which is what we already have and then we'll go on to a little bit of a blue or contrast maybe this blue here yeah that looks good okay so the rest is going to be with this blue and then we can use conditional formatting to color it blue so here we have this blue and I'm gonna bring this blue all the way through all the way through here and then everything but the fields in which the user is going to enter data we're going to give it that blue that blue is going to change to red on purchases on purchases of that blue it's gonna change to red so keep that in mind and as I highlight all the cells that are going to be affected and I also will use fields in which there is a constant in which the user doesn't edit so that's the color I want to give let's go ahead and give it that blue that color let's go ahead and give it that blue which is this one right here all right that's a good distinction now it's kind of looks odd with the color change here so I'm gonna give it a border around that I kind of a circular border so we'll insert the shapes and I'll use this rounded rectangle and I'll bring it all the way up here and I'll just surround that and then it will bring the corners in very tight so they're not very well rounded and we don't want to fill on that so no fill and what I want to do is I want to hug this and I'm going to use our theme color which is that dark green and there therefore that would give it a nice blend there and it doesn't look good so let's go ahead and put that in green and we'll give us the outline of that dark green which is our theme color okay good so now we have we see what it's going to look like for our sales purchases are going to be red but we'll use conditional formatting to to do that I think we've got it covered now we can products products can or cannot be colored I'm not sure about that we can we'll see how it looks in other words these are specifically for products products and we're gonna be able to hide and show these let's give it a shape up we can start adding in buttons soon so we can see how this will come and insert some shapes for buttons we're gonna use our theme color and our theme shape I'm gonna round it rectangle that color is going to be a darker green we'll use maybe a dark green shape fill gonna use that dark green here we use that as our theme color here we can create multiple buttons for that we don't want too many we're gonna add the photo we want to delete a transaction and we'll use a we want to delete it all right so let's go we'll just create these buttons there and we got a few too many there this will be add photo I can bring in our shapes and we can bring in our a difference you I want a different button to hide this is product information right here these rose nine to ten our product information so I want to do I want the ability if you hide those rows but I wanted to do is I want to do this I'm gonna hide product information sometimes we want to see details on the project sometimes we don't so I want to hide actually it's gonna be four rows total so what we'll do probably also high 12 so the idea is is to just have your transaction information here and then when you want to see the product information you can enter the product you can into the sales price but when you want to see or update the product information you'll unhide rows nine through twelve so we're gonna do that and I'm gonna do that through VBA but I want to create some shapes that we can control the hiding and unhiding of that so let's enter a shape something like not an arrow but probably like this will enter what this one one for hiding and one for showing so insert and then we'll do it just the opposite of that shape which is the up here which is here so these are our two these are the two shapes and let's size them and also color them we know the fill and we know the the style is here so let's fill that again alright so that's it when we duplicated that we don't need that all right okay so we have our shapes here let's go ahead and give it a theme that we like which is the green and then the fill that we also are using so that's consistent and now we'll put it a size on that I'll selecting these and set them point six one and let's say point nine eight it'll give us a good they're at least the same so we can align them and what I want to do is I want to use one to hide and want to show and then we'll put them on top of those so we'll use this one to hide details and this one to show the details show details alright of course we're gonna set the text so that there's no padding around it so that all the text shows up go back into the format's here and under the text box we can set the write for a very little bit point zero one and then perhaps zero on the left I'll put the icon on the left the top and the bottom don't need it we will Center that vertically so that should be fine let's Center that all right and now let put it off to the right and exhale center it and then I'll use spaces cuz I want to move it over to the right but I want to keep basically the same format I just want to give some room for the icon that we're gonna add in there so that's all I want to do there we go so and then I'll do the same thing for the height I want to I want to leave some room for the icon and we'll import those icons in just a moment alright so now we've got our two buttons show details high details let's bring in the shape that we're going to be putting in in inserting pictures I've got a folder here with a some preset picture so we don't have to go searching around for them alright here they are let's go we're gonna select all pictures I've got some icons in there so we want to select all and I'll just use just these icons that we're going to use the shirts are gonna come in later as we add packages so I've added all of the pictures that we're going to be adding in I'll use this one for the ads make that point point to bring them smaller and I think the others as well this is going to be our main icon for the alright for the inventory the top part so that's going to be our icon here so we can zoom that and we'll use that as our header as our head our main icon to use for our shape alright that looks good bring it down just a little bit get a little bit more spacing onto that and that'll make this a little bit smaller now what I want to do is I want to add buttons here but let's size them all together and also bring them to the actually we're gonna use just these set those two alright point two will give us a decent height for that and that we will create our buttons we want one for new transaction new transaction we want another word for deletes transaction and I also want another one cancel new and then save transaction it'll be two different button sets you have a new button set and existing and we can format them all the same set the middle and off to the right because we're going to be adding icons on the right side so we can set up we'll use this one for our new bring that over there for our cancel we'll use this cancel new or bring a little size that button according this will use first save and this will use for delete which we need to increase this okay now let's fix our button sizes increase delete a little bit so we can fit that in there and we'll also group these buttons to ok and cancel we can shrink that button that button doesn't need to be that big and save a little bit bigger on that but it's almost perfect alright that's good and we can Center let's go ahead and Center can highlight those those buttons here and then what we'll do is we'll just format align although the middle so all the icons and buttons are lined up accordingly now we're ready to group them bring this canceling a little bit smaller there and holding the control and then we'll group at first and then we'll name it cancel we use cancel transaction actually cancel transaction button we want that we want save transaction we want that grouped and we'll name that save trance button and then we're going to group these two because this is for new is giving a new transaction group so these two are only going to be visible when we have a new trend when we have a new transaction we can save it and cancel it those are going to be our only options so we want to group these two so let's group them and then give it a name for that group so we're going to call this new transaction group and likewise these two we're going to name them and then group them so let's go ahead and name that group them first group into a single button then new trend transaction button alright and then this one here as well group it and name it and then we'll call that delete delete' trend de else fine small okay so we've got that now we're gonna group these two buttons together group those and then we're gonna call that existing that's existing transact elective so we can hide show these now these two groups are not going to be shown at the same time so we can make them aligned I'll line the top so they both get it to the top and then on line the middle as well we'll align the left probably all right because they're not going to be shown at the same time it's gonna be either all right now we can align the center that way they're centered to the same those two groups are not going to be shown at the same time so now we've got that I want to create one more button for show image of out of buttons let's go ahead and copy this control see if we do ctrl D it's going to group it but it's gonna stay in the same group we don't want it I want it I want to do it new so I want to create a new button I don't want to add this for add picture all right good now we're gonna bring this over here and I'm gonna bring that to the front notice it's behind so we need to bring it to the front so bring to the front and that perfect okay that looks good align that make sure they're aligned in the middle group them as we do and give it a name ad pick button I always like the button cuz when you when you click on all the buns you want to see a nice I want to know the names of all these I want to see them so I can see exactly what is what if I have just things like text box or up down arrow that's not really helpful I want to know what they are so I tried to name each and every one as much as possible now we'll create our show details all right point to six let's go ahead and set that size all right that looks good and we'll copy and paste that we use the same icon for both I'm cheating alright good hide details show details we're going to group that and we'll call this show detail button and then we're gonna group this and call this high detail button so you see a pattern how it's working so that you can use the same pattern yours high detail button a group it's a group but either way what do they name the other one I forgot now too busy talking okay button and we should keep them consistent button not group button alright fair enough they're both the same now show detail button high detail button and what we can do is we're going to put those right on top of each other because I don't they're not gonna be shown at the same time so we can align the middle then I'm gonna bring that up it looks kind of funny the way it is but of course only one is gonna be shown at the same time we're gonna be hiding 8 9 through 12 9 through 12 so we can bring them down just and we want to highlight them both we just draw square around them and bring them down so we're gonna sign match to them and they're gonna be hiding so we know that that so we got our buttons now we've got already they're gonna be only shown at the same time all right what's next let's do our conditional formatting for our purchases because we got sales - let's do our purchases now next up let's go ahead and set those conditional formats what I want to do is I want to create the font colors to dark red so let's highlight those and those are just on the labels here on purchase so that the font becomes red for these items on purchases at least for the labels as we selected it we can go ahead and click on conditional formatting and we'll add a new rule it's gonna be a very simple rule here we'll use a formula and we want b1 equal to purchases and in that case we want the font to be a dark red color probably this color let's go ahead and click the font and we'll use the second from the bottom here almost edited this click OK alright now we've got that let's click on the ok it's working well now we've gone from blue to red I also want to set the background I want to change the background as well let's go ahead and highlight the background just as we did but this time we're gonna use conditional formatting on it and we'll add some rule Ted can add two rules for the background one for the upper fade and one for the main background color but first I want to select all of the affected cells so we can change the background on those alright now I've got everything but the white so we can create a new conditional formatting under new rules and use a formula again we'll use the same formula a fill of a light red here we'll use this color here and that'll be fine and we can give it a rule let's click on b1 equal to purchase alright and apply that click OK and apply that and we see it changes to red alright that's good now we just have to set the upper fade to the same same royal conditional formatting new rule but we can use the same formula use a formula b1 equalling purchases equal purchase all right and then this time we're gonna use a fade here from the green to the red so Phil effects because this one will have that fade using our our basic background green color and then we're gonna fade it right into the red color so that's the one green to red click okay okay and there we go now we're good now when we click on sales purchases and sales now they look very different and very distinct we can even change the font I like that we could even do a font color here let's change the let's change the main font to blue we can even go a darker blue on that a little bit darker here and then we can do a conditional formatting for that as well for a darker red so we can really differentiate between sales and purchases and the same rule here equals purchase and then we'll give it a dark red font on this one so that it ties in with the theme of the purchases so we'll go with this color here and make it bold that's to be bold already regular is fine okay and click OK now go purchases purchases have different names and now here we've got customer we've got a formula here let's copy that formula I want that formula but in this case customer is going to be on purchases but on sales is going to be vendors so let's change that to a formula if he goes purchase then I want it to be a customer however if it's if it's a purchase excuse me if it's a purchase and we'll paste that in there all right so if it's a purchase if you're picking a purchase I want it to be a vendor and if it's sales I want it to be a customer okay we sell to customers we buy from vendors so we want it to vendor all right that looks good let's just add some cones on to that and then we'll write justify it as well all of the fields same thing here and we also need to add in our border so let's get those borders red too we want to make it look alright now we can write justify it all of our fields so that everything is consistent and then right justify it let's do the borders - all right holding down the control let's do that again holding down the control and then we're gonna make our borders on maroon as well so that everything is very consistent alright so what I'll do is I'm going to use the borders for conditional formatting to conditional formatting new rule we're gonna use the same rule if the equals purchases we're gonna make it a dark red border that is going to be B 1 equals purchase and then the border here border is going to be a dark red so let's go ahead and set that all the way around give it that color and then go all the way around okay there we go now it's gonna give us some kind of style all right now we see now we see everything changes blue now it's very distinct so I like that now our purchases and sales look very different all right we're good with conditional formatting and let's move on to some additional mapping for data will move on to data mapping all right the idea is that this data the transaction data which is in these fields and these fields I want to get into here so we need to map those data's with the with the data above so we can start in the transaction type that's going to be b1 the vendor and customer that will be e5 so we're mapping with the cell addresses there the transaction date that's gonna be h5 while the product is going to be e7 the price of course will be h7 I've got h7 for the price there with for the quantity we have e 13 that's important and for the total we're gonna put that we're gonna leave that blank out it's gonna be a formula and the notes is going to be J 13 so now we've got that map cop that's gonna help us let's go ahead and send it out we're gonna hide it when we save our information to the table that's gonna help us map the data into the table here and I've got some sample data we'll put it we also want to map at the other direction so let's copy and paste all of this and move it over we don't necessarily need move it over here to n 5 starting there and n 5 and we're gonna paste it and we'll can we're going to remove the conditional formatting of course we don't need it here and then conditional formatting we'll just clear those from the selected cells we don't need the pictures as well so we can hide and hide those we can delete those pictures that came up those are not necessary I'm just looking for the particular cell spacing so we can put in our columns here so we can map those columns accordingly as we map them and I'll show you how that's gonna work a little bit later but basically we want to size them with the columns accordingly so that when we bring the data it's automatically coming in so we can do that let's set those up now the customer will be in column 5 right that's gonna be here transaction excuse me he is calling five right half of six just seven and so forth so we can set those up now the product number is going to be seven while the transaction date will be six so we can put in that and the sales price is going to be a so this is a mapping our data and I'll show you how that works as well we're going to leave the product information empty for now but we're gonna put that in a little bit the sales quantity is gonna be nine and the total is going to be ten I also want to place for our picture link so let's put that here picture link cuz we're gonna add pictures and I want to make sure that that has a place as well so let's make that white so we can put in a picture link there and we know that our picture for the products is gonna go there all right so we're set up there and next up we have let's just move that over and we can shrink this we don't need this too much okay so we've got our information and we can shrink these are just gonna cane small numbers so we don't need a lot of it a lot of space there and what we want to do now is we've got our data mapping let's name our sheets here let's call this transactions gonna have three total sheets here and the next up I want to have another sheet for products gonna keep our products on a separate table so let's do that and our third sheet for setup which is going to contain two lists I want in our in our here I want our customers and our vendors in a list so let's go ahead and add those in customers and then vendors will just set up some sample lists here very very simple I just looking for the name because I want to create a drop-down list so I have some data another let's bring in the data here but let's map out our products first so that we can get our products table located here we'll start out in D here just first and call that products and while our products contain multiple criteria so we're gonna first have a product number and a product duck name we also want a sales price wanna type there as well and quantity on hand this is going to be a formula so we know how many we have which is of course is going to be the total purchases minus the sales it's pretty simple using some ifs we'll get into that a little bit one the purchased quantity is so if we purchase it often want to know how much were person purchasing what is a normal amount or the default amount that we're purchasing and product description and of course the picture link we want okay so we've got our basic database titles header set up here let's space it out accordingly and I'll merge and center this let's capitalize that oh and create a increase this and let's go ahead and make it bold and choose another font to separate it give it a can do a format so we can easily see it will go from maybe a blue fill it effects and no we use a blue fade on this going to maybe medium blue to a lighter blue and then we'll go in there header we'll just choose a different fate as well so give it a nice look okay and then on our on our headers we'll also do the same on another fate but just a bit lighter of a blue so we can set that up the fill effects will go from medium to light blue so here's our medium and then our light blue is here that's how we give it a nice fade effect from all the way from the header for the title to the header okay so now we're set up with that and we can give it a border format cells and we'll get a thick border blue border just kind of give it a table a nice look it's it's not really important for for creation but if you want to I'll show you how to give it a nice look of course you can format as a table to that's very easy but I wanted to show you some different ways so that you can see how to how to do that if you want to increase your data and add formatting a table is easy but I like I have a very specific like and I don't necessarily like they look kind of boring the format's table so we'll give it a border there and there there okay so now we're good and add some conditional formatting alternate lines lets us help see the data easier so we're going to add a new rule here and use mod so we can color the even rows in equals mod okay that's gonna automate and then we'll give it a again a blue light blue that's our theme for our products so we'll use this blue and then and then some a little bit lighter color no no fade on that so a little bit lighter just so it's contrast so we can easily see the data accordingly okay all right good and now I have some data let's go and save it and import bringing some data I've created some data there's no reason for me to add in all that data it's going to be so I've got some sample data here we can bring in their customers we'll put in here and we'll just I'll just go ahead and copy and paste that into our bring setup at the first row and then vendors bring that in as well just so it's a faster this way we can we can quickly get to the important fun parts about it and I've got some transaction here some transaction information here that's gonna help us out so bring that into the transaction sheet so we can quickly and I'm just gonna paste values here and also for products I've got some products so we can go back in there and add some products as well so that this a little bit quicker save some time and copy that and I'll paste that and then we're gonna be done with our our data this will help us get to the good parts of it alright don't worry about that's not important all right we're good there we also want to map the data on our project and we want to map it from the beginning so I want to make sure so remember we want to map our product information here so we know how to map it back to that so our product number is in e7 our product name is an e5 let's go ahead and make those capitals e7 and then e5 we also want our sale price that is in h7 and our type is in h9 then our quantity on hand that is located that's going to be a formula as well so we don't need a thing on that we don't need mapping on that and then 11 for our purchase quantity e 11 product description is H 11 back in the sheet 1 and then our is gonna be I believe o 14 let me just double check that on that o 14 yes for our picture link is going to be o 14 so we want to map it so we've got all that mapped we can send it that's a little bit easier we know what column it's over and everything but the quantity on hand let's go ahead and double click on the column so we can size that accordingly but not the picture link all right so we are good there now we can move back into the transactions and we can start building this out but before we get into the VBA I want to name some ranges here so that's gonna help us in VBA and in our formulas so let's do that in the setup screen I want to use a named range for customers so we can add that and I want use offset so that that grows as we grow our customer list than that name grows so into the formulas name manager and we'll create a new one and we'll call this customers and we'll give it an offset so we can highlight this for now and I want to use offset and then set up starting at c4 and I'm gonna click the comma three times count a and we're gonna count all the way we can increase this to 99 so I'm going to count all the way count all the values with text and then we're gonna use that as our dynamic comma one just one column there and that is it let's click OK and now we're gonna tab over and just check it alright good it looks good it's set it's a highlight at all of our customers we're gonna do the same thing for vendors new vendors and then for what this is going to actually be e so can use e starting off at E for and then using offset and then it's gonna can make this e and then copy that so we you don't have to write it again then we're going to add three commas in after the four count a again and then what are we counting all the way from E to nine nine nine now it's fine comma one okay there we go efore three nine nine now we're going to click OK we're going to tab over check it to make sure that it encompasses all of our vendors good so now we've got vendors and customers set up I also want to do product number so let's go in to products and I want to know I want to create a named range for product numbers so let's highlight that also going to use offset each time name manager new and then we'll create product numbers okay that's good and we're gonna use offset here copy let's go ahead and copy that that range there I'm gonna use offset again offset and then again three commas count day then parentheses and then we're gonna paste in that range increase it so encompasses all the possible data at comma 1 close parenthesis okay so now we've got that in there let's go ahead and tab over and check to make sure that it's correct that encompasses all of your data okay good so now we have it now we're going to add in some named ranges for the transactions I want to know the transaction types I want to know the product number and I want to know the quantity also the quantity so I'm going to create named ranges for all of those but I'm gonna use this as our main I'm gonna use this as our main counting the transaction since this is always required in every single one we're gonna use this and then we're gonna we're gonna count these but use these as are so let's go ahead and show you how to do that under the name manager again create new this time we're going to call it trans trans and then we'll have each won one for quantity so let's do try action-type that's the first one that's the one we're on now we're going to highlight this keep that copy and save that count excuse me offset offset transaction starting at D three commas again comma comma comma count a paste in our values and then those go to nine nine nine nine and then we can do comma one okay good so we're set let's go ahead and copy and paste this we're gonna cut we're gonna be using this for the next three except the only difference is our starting point our starting cell is going to be different so in fact we can can't we can copy the entire offset click OK and now tap over make sure it's correct okay good let's go ahead and create new ones for the quantity and for the product number so trans underlord QQ a quantity and then we're going to paste in the entire formula but we're going to change quantity is located in column I so let's change just this the starting point I and click OK now tab over and we'll make sure that it encompasses all right good now we only have our product number to do so new trans product number okay so now we've got that and we're gonna that is of course in I call them G so we're gonna paste over paste it all keep d as well but just change D here in the first part to call them G our starting point click OK tab over to check to make sure that all of our data is included perfect okay alright I'm now moving a little quick we got a lot to cover so we're good now we've got all of our named ranges let's just take a look at them all just so we can we have customers we have vendors both in the setup screen we have product number and that's in our products we also have the transaction quantity the transaction of product number and transaction type all part of the transaction I need that for our formulas in our database so we've got all of that done now so now we can go on let's go ahead and save alright moving on we also want to ready to start VBA let's go ahead and get into that and go with the on sheep macros now the idea is when I select a particular line here I want that information to show up here and I also want the transaction row here to show up so let's go ahead and put in a row let's just put in 21 here and we also want two conditional format that so we know what row selected we can highlight this table and add conditional formatting for our selected row in there home conditional formatting new rule will use our theme green use a formula equals that's going to be B - ro ro ro and then beginning in close parenthesis I'm gonna format that cond give it a contrasting fill effect so that it's very different than what it looks like in normal state so we'll give it a fade color from dark green to light green or vice versa and also we'll take the font bold and put it to white so we can clearly see that that's been selected white okay all right and that looks different there we go so now we can see very well which is one so when we select on it it's going to change this to our selected row so we can easily see which one has been selected alright we've covered a ton already in this first part of the inventory manager so I look forward to bringing you a lot more in next week's part to including all the VBA thank you very much for joining me today

Channel: Excel For Freelancers

Views: 1,660,345

Rating: 4.917244 out of 5

Keywords: Excel Inventory Manager, Inventory in Excel, Sales & Purchases in Excel, Tracking Inventory with Excel, Excel Inventory Tracker, Manage Inventory With Excel, inventory manager, Managing Inventory with Excel, Track Inventory With Excel, Excel Inventory, Inventory With Excel

Id: -1N0L-FDWCs

Length: 63min 10sec (3790 seconds)

Published: Tue Aug 28 2018