Be able to save those orders search for individual orders very easily just by turning that on and also we are going to be able to have this really cool dashboard dispatching orders is another cool thing very similar to receiving orders but dispatching orders of course we can take an order from any existing warehouse here and then transfer it from here or maybe we want to transfer it to another warehouse so we can do that as well so we've got lots of options on these and of course assign a driver and a truck so we have individual trucks we can choose from anything we want so there's a lot of choices that we have in this application on top of that once we of course.
Receive and dispatch orders we're going to be able to manage that inventory we're going to be able to adjust the inventory on him be able to change the quantity of it as those quantities come in go and as we check and count those quantities we're going to be able to save those adjustments and of course we are going to be able to navigate to previously saved adjustments with just a click of a button so there's a lot we're going to go on to that i cannot wait to share that with you we'll be able to add brand new adjustments automatically add pictures and barcodes onto that we're going to be able to select a specific warehouse so if we only want specific items in stock we want to adjust the items in stock on just one warehouse we can do that with just a few clicks and of.
Course saving that adjustment as well all right we also have a lot more in store for you on top of that we're going to have a really cool reports dashboard we're going to be using pivot tables we're going to be able to use this really cool timeline here we can adjust the information displayed drivers by date we'll also be able to show customers by period based on a specific quarter or maybe based on all quarters of the entire year just with a few clicks so it's going to be really great and of course we have sales by location depending upon the warehouse we want to know which warehouse is doing good we can do just that or select a few warehouses and i'm.
Going to show you how to do that with some really cool slices so i've got a ton in store i hope you're going to stick with us it's going to be a comprehensive training and as they all are this ones will so i do want you to grab your beverage of choice coffee tea or whatever you prefer and we're going to get started right now okay before i do that i want to let you know a few really cool things if you do love developing excel applications and i want to show you how to do that for passive income i'm doing that with a comprehensive course called the mentorship program this is an incredible 132 hour course where i take you through all 12 phases of the defining.
Development design and deployment process on how to get your applications out there and get them going through passive income so you can earn regardless of whether you're sleeping or on vacation excel can do that and along with the mentorship program so i hope you'll join us for that i'll include the links down below of course my excel mentor will get you there myexcelmentor.com that will get you there all right thanks so much let's get started on this and i'll go over just a little bit more of an overview of the sheets and components of this application and then we're going to get right into the design components we'll go through different functions we'll go through conditional formatting we'll be going of course through extensive vba and along with how to integrate databases and data mapping so we have a dashboard here we have our home sheet.
And we can get to them linking through just some small macros here going to each individual sheet so we've got the dashboard here receiving orders this is a sheet that we're going to be able to receive those orders our dispatch orders here which you saw briefly we can add notes to that an order list now this is how we save our orders right now whether they're received orders or whether their dispatch orders are going to be saved here all we need to do then is simply separate them by an order type everything else is relatively the same we have the from or two from could be from a supplier or from a customer or it could be from a warehouse and we could also again go to a warehouse to a customer so we can do that with here so individual.
And we have the received location right so from location and the received location from can be of course any type of warehouse from could be a supplier if you buy parts from a supplier it's going to come from that supplier if you buy if you want to transfer parts from one warehouse to the other you can do that with the from and then the received location you could receive them into another warehouse or your customers can receive them we have individual drivers assigned to any type of order and then of course we have a truck that's assigned to that and then of course we have our pivot data this is going to help us for our dashboard we've got several different types of pivot data we'll be getting into that and i'll show you the four different types and also we have order items these are the individual items for these orders so we've got an order list so.
We got order number one and then assigned to order number one we have here five different individual items with that order number one each one we have of course the from we know it's the from or two type it's coming from a supplier so we know that and that means from location is from a supplier i'm going to transfer to the south warehouse so each individual item can be to a different so maybe you pick up from a supplier and you want to deliver some supplies to a certain location and another set of supplies to another warehouse you can do that because in our received orders right we can then set which where if we buy from an acme computer and we want three of them to go to the.
South warehouse and we want another few of them to go to the west warehouse all we need to do is just make those changes and save that order and we know these items are going to go to the west warehouse while these items are going to go to the south warehouse so that's going to be really important so we have these order items as individual we have a list of warehouse locations warehouse locations come with an address city state relatively simple for our warehouse names and locations we also of course have suppliers our suppliers or our vendors the people we buy our products from those are suppliers sometimes called vendors or companies and they got a phone number email just some basic contact information not a whole lot going on with our suppliers we have our inventory.
Items now this is going to come of course comprehensive our inventory items there's a lot that can be done with this we've got item id the type lots of information for for inventory items we'll get into some of this and of course i've got some custom fields which might help you here and inside our admin screen we've got an adm screen where we're going to be going over a few different items inside that and then of course we have a customers customers comes with address city state just some general basic information for customers i'd like to see what you can do with this template of course you can grow that as well and drivers the same thing we've got drivers here drivers include a picture right those pictures are attached to a folder and inside that folder we have our driver pictures here so we've got a set of pictures inside a specific driver so keep that.
In mind that's where they're going to come from i've got a truck list right truck id the name a truck type how much tons it is and then a picture truck pictures as well we've got them saved in a
Folder so also if you do want to pick up these particular other resources including these pictures and icons that's going to be through our patreon platform i'll include the links down below just a few dollars a month and you can get all of that and a whole lot more inventory manager we briefly saw this this is going to help us adjust items on hand for individual locations so we can do that if we want to save for the warehouse right we can save the adjustments here and it's going to save and then we'll be able to navigate to those and of course if we want a new adjustment.It's going to list all those parts we can then navigate back to the previously saved adjustment and we can see what we've done here so there's a lot of features here so we can navigate to previously saved adjustments just like that okay so saving we can also print that worksheet okay adjustment site this is the individual adjustments we've made three individual adjustments so those are the individual adjustments and then the items that we're actually adjusting notice inventory item adjustment number one has two different items we adjusted the quantities on those and then two has two and then three is three so we've this is going to enable us to do that all right and lastly i think in the beginning of it we've got an admin screen that's the only one i didn't show you admin is relatively simple but there's a lot you can build out with it we've.
Got a specific folder for our item pictures notice we had some pictures on the items right of course every screen we have the ability to add items and of course we got this option we can show or hide the picture just by selecting that it's going to allow us to show or hide the pictures i'll be showing you that and our pictures are going to come from this direct folder here item picture and we can browse for the individual folders using this driver picture and truck picture i've got all those located in the folders here right here so we've got our driver pictures our item pictures and our truck pictures all located here and i've also got this really cool background this is actually a background that's how i get this color here that background is going to be based on the page layout if i delete the background it's going to be gone it's all white right but if i go ahead and add that background in you're going to find it on that folder i'll include that as well.
If you want to see that that's just going to be this basic blue background that i've got here and that's going to add that nice blue background so it's really cool it gives us a nice look custom fields i've got 10 different field custom fields this is going to help us we're not going to use this too much but it does i have linked it to the item list so that you when you get this it's a good start for you to customize those fields so your individual items can have custom names right i've got some defaults if we create a brand new receive from what are we normally receiving it from a supplier or are we normally receiving it from a warehouse and i think that's important because when we receive those orders and we do add new order i want to set this from right what is this from we're going to set it from supplier or warehouse so that way if we change this to warehouse and we normally get our supplies from another warehouse all we need to do is when we go.
Into the receive orders and we click add new order it's automatically going to change that default to from and it's very very similar on the on the send to when we generally send materials send parts are we sending them to another warehouse or are we sending them to a customer now if we send them to another warehouse of course we're going to have the warehouse drop down list here if we decide to send them to a customer right to the customer we want a list of customers to drop down this so we're going to show you how to change that dynamic named range there that data validation here using that named range and then we're going to show you how to do that all right so we're going to say we want to send them to some default locations there all right so that's what's else in the admin and then the rest is we just have some custom field names those custom field names are also located.
In the item those are the last items and they're just simply linked inside the inventory items here we have some custom fields starting here in column p and basically i've just used a formula to grab that particular custom item type otherwise we're just going to show custom field right so notice this one says custom field 4. if inside the admin screen we were then going to change that to something else and we wanted to like the item type let's say item color right maybe we want that and then automatically inside that it's automatically going to be linked so inside our inventory items our now column s now shows that item color so it's simply linked through this formula so that's a great way to have it done all right so what we're going to do is we're going to start off.
On the home screen of course i've got this home screen here this kind of ties everything together and so on every individual screen i have this home button it's simply linked to that home sheet this home sheet there so it's just a single line macro that's going to let us quickly go to our home screen and then so we'll start off on the received order so this is a really great now received orders is very similar to dispatch orders and as i mentioned to you what i want to do is i want to be able to add new orders in very quickly we're receiving orders we don't know exactly where we're going to receive them or we're going to receive them from a warehouse or maybe a supplier so we have those two okay so we've got a supplier here and it's going to be a just a list of suppliers let's go over all the named ranges and that's going to give us a good foundation to move forward on this application so we've got a bunch of them so inside the formulas we're going to go.
Into the name manager we're going to take a look at all of the names and then i'll kind of refresh your memory as we move along in the training but at least you give an idea we have one for the adjustment id remember every time we make an adjustment then we have to secure with an id i also want to know how many were adjusted so we've got an adjusted item quantity when we make an adjustment i'm going to need a named range to adjust and that's going to help us determine the inventory on hand i'll be getting that i'm going to show you how you can automatically calculate the inventory on hand on a per warehouse basis or per location basis using uh both what was.
Purchased and sent to a location and what was sold from a location as well as any adjustments that were made to items within that location okay also we have the item adjusted name what is the name of the item that was adjusted and also the location what location was it adjusted in criteria these are automated based when we create advanced filters and then of course we have a customer id and a customer name those are also using the offset formula they're always going to be dynamic and almost always dynamic named ranges right that helps us because we want as our customers increase.
So does our formula driver id the idea of the formula a driver name also we have that and i
Also want to know the driver picture folder that's gonna be located in the admin remember we've got a named range for a specific cell that is the folder where our drivers are located and then if we take a look here we've got some item data right item data is going to compass all of the items right all of the items right so because i have to call it individual columns so we've got an entire data set there for a name branch for item data item id is specifically for that item id.Item name is going to be of course only for that item name and then the picture folder of course is going to come directly from the admin screen right there under admin picture folder so we also of course have some information for the native timelines here that we're going to be using order id going to come directly from the order order i need to know once the orders are sold whether they're of course dispatched orders or received orders i'm going to need to know that from location where did it come from inside a named range and i also want to know the 2 location right so i want to know where it's gone to so that's going to be important inside a neighborhood so it's going to be a supplier now where did it come was it from or to type that means did it come from a supplier or is it going to a customer from a supplier or to a customer so.
That's the only difference we need to know on that from or to there so now we also have the order items item name these are the individual names remember we've got received orders we've got dispatch orders and each one contains the name this entire list contains the name of those that's very very important because when we have to count them we need to know how many were received in this specific location we're going to count using the name so that name is very important of course we also need to know how many we're going to be counting right when we count we need to know how many got added to a location or deducted location so we're going to need a named range for that quantity so we've got that also i need to know what type of an order it is that's for important because if it's received if that order if those parts are received we know that we want that quantity on hand to increase however if it was dispatched dispatched.
To a specific customer then we know we're going to decrease the materials however if it's coming from one warehouse to another we're going to deduct it from one warehouse and add it to another warehouse so also we want to know the two locations order items two locations we want to know where it's going to is it going to a specific warehouse or is it going to a customer right it can go to two different places to a customer or two a warehouse we've also got some tables that we're going to use in the order items table and the orders we've got two different tables that we're going to be using we're going to be going those are going to help us with the dashboard so we've created some some print areas we'll go over slicer locations i've also got a sort by this sort by it's going.
To be need to we want to be able to sort by then show you this we can sort by any type of field so we can add that that's going to be here inside the inventory management screen also have a supply idea supplier id we want the id for individual suppliers as well as the name for individual same thing for trucks truck id and also a truck name and of course the folder that's associated with those truck pictures right all those truck pictures are going to come in one folder and i also want to know the warehouse names i want a list of warehouse names right that's going to be used and i also want to know with all i need a warehouse names with all and that's going to mean both all the locations and that's because inside our inventory management we could we may want to.
See something from all the locations as opposed to one individual location so our we have a named range that's going to include all the warehouses along with the word all locations or words all locations so we want to get that option too that's going to come in a name all right that's it for the named ranges we'll revisit them as we need them and i'll remind you as we move along inside the training here we are on the received orders okay so this is going to we're going to be able to just quickly search for an order and then load that and we want a few things to happen right first thing what we want to do is we want to make sure that we have a received date a received time a from supplier and i also want to be able to now this from supplier it may remember when we.
Receive orders they might come from a supplier or they might come from another warehouse when we add a new order we don't know we've set the default to warehouse if this is warehouse i need to make sure that this is a drop down list of our warehouse names if we look in the data data validation here we are going to see that we have uh yes we do want to do this merge cells that we want to see the make sure that those warehouse names that named range that we just went over we want that to validate there right so that's gonna be however if we change that to that we're picking it up from a specific supplier we want this text to change and it's simply the from and then whatever is located in i force just a formula and we also want to make sure that this named range also data validation changes to all the suppliers if you take a look now inside the data.
And then the data validation you're going to see that we have now set supplier name so that's going to happen through vba okay we'll take a look at that in just a moment after i give you an overview i also want to be able to select from a list of drivers and i want to have that picture appear automatically right here and i also want to picture a list right if i specific if i check a specific truck i want that truck to appear as i change that so we want a picture of that truck i don't know why you'd be shipping in a tow truck maybe for shipping cars so we want to make sure that we have a truck that's associated with the name so we want those to appear here if we select a supplier want some things to happen i want that address to automatically locate.
If we are going to select a location a warehouse where we're getting it i want the address of that warehouse to automate okay so we've got that going on so we've got a lot of things going on vba if i select a specific item i want something to happen right i want the quantity i want the item cost and the total based on the quantity so if i change that quantity i want a formula to take care of that i want to know the supplier where we're picking it up right maybe we chose a different supplier right maybe this one came from a different supplier so we want to be able to have an order so let's save your truck you send your truck out and it's going to pick up material from three different suppliers and you want that supplier you want to come to one warehouse or you want to go to multiple you now have that option with this so we can pick up this one from it's going to default this one will default to whatever's here but as you move on you can then.
Change it so that way you can pick up parts from all different suppliers and have them delivered all to one location of course you can change the location and have it do so it's really really dynamic this type of an application because it gives us a lot of flexibility we can add in nodes and of course we want a small picture of that item to appear here so we get an idea of what it is all right so that's pretty much going on once i do i want to save that screen i want this fade out message to appear letting the user know that the order's been saved i want that order id to appear here i want the order total to appear here it's simply going to be a sum using the total column all right so that's it the basics for the receiver of course we delete the order and add a new order okay so that's a bit it's a relatively simple screen but what i want to do is i want to get into.
The vba and show you how this amazing application this portion of the amazing application works all right so that's just what we're going to do right now so we're going to go into the developers and you're going to go into visual basic alt f11 is a shortcut that will get you there and we're going to go in we're going to take a look at some of the screen actions those are going to be the change worksheet change on the individual sheet so i've got my vba open and i'm going to look directly on our received order so that's going to be down here called received orders and what i want to do is i want to focus on the worksheet change remember when i make a change to a worksheet we want some things to happen now what do we want to happen well the first thing on.
Change of search order id now there's a few things if i put on here right in e2 and i search for an id if it's if there's an id that's associated with that then i want that id to load up right whatever was loaded those items if there's not maybe it was just associated with a dispatch order i want a little notification saying the entered order is a dispatch order not a received order right because that's a dispatched order so three might be one another dispatch order but one order id one of course is a dispatch order so we can do that right we have an order list here one is received two and.
Three and four five are dispatched six is received eight is received so it gives you an idea of what we're doing now of course if you're going to go into the dispatch orders and you press in two of course that's gonna load because it is a dispatch order so they look kind of the same and that's there's a reason for that okay we want uniformity all right so the first changes what happens when we want to do this on e2 when i make a change i want to know is that a correct order and how do we know that well i've got two columns here that's going to help us out determine that so when i put an id in here what i want to do is i want to search and see if there's a row that's associated with that id and it's going to be called the search id here located in b8 and we're going to place whatever's in e2 we're going to search the order ids we're going to add 3 because we want the.
Row that is associated with that right that means if i'm looking for the first order the first order order number one what row is it found on it's found on row four that's why we add three i want to know the row that's associated if it is not found what's going to happen this is going to be blank right this particular is going to be blank because there's if there's an error it's going to show blank so then if it's blank then i want these to let you know hey this is an incorrect order so that's just what we're going to do on here so when we make the change that's what's going to happen so let's go ahead and take a look at that we're making a change to e2 we also want to make sure that e2 is not empty if those positions are true what i want to check is b8 remember b8 is.
Empty it is an incorrect id right if i enter an incorrect id i want that message box to come up saying please enter a correct order id and that's just what we're going to have here inside this message box please enter and then exit the sub nothing we can do without a correct order id if it is correct what do i want to do i want to take this order id whatever they've entered and i want to put it directly inside b1 i want that order id here up here because i what i want to do is i want to determine what row it is on and it's on row four i'm going to use the exact same formula but in a different spot in a different cell in b2 that exact same formula to determine this because this.
Is the loaded order the order that's been loaded it's currently ordered number one see here this is called order number one it's linked to b1 order number one what row is associated with order number one it's row four i also want to know the next available order if we look in the order list we see we've got up to 8. i want to know the next one we're going to use the max formula for that max order if you've seen my videos you you know this by heart by now because we use this all the time but it's consistent and it's great and this repetition will help you program a lot faster so bear with us there's a lot of new stuff in this if you have not even if you have seen a lot of my videos okay and we always working to optimize the code too so what we're going to do is we're going to use the max for that so that's going to give us the next id that's going to be used for add new orders when i add a new order i want to place that id here and it's going to show that.
New order here again we load it again as you can see this order has not been saved so order row is blank right only on previously saved orders do we actually have an order row so moving on inside our code so we're going to then place that order that order id that they just entered you're going to place them below then we're going to run the macro called order load we're going to get into that macro in just a minute i want to go over some of the other on screen we'll go in order we're going to go over some of the other worksheet changes remember we're going to get to this the record that's the macro that's going to actually load all those order details okay now what we saw is we saw another change right remember when i made a change to here from supplier we saw this.
Drop down list change from suppliers i drop down the suppliers to a drop down list of warehouses right so we know this data validation change so i'm going to show you exactly how we did it of course it's always triggered on change of i4 so when we have a trigger change i4 and we want to make sure that i4 is not empty then what we do now anytime we add validation the first thing we want to do is always delete the validation and not only do i want to delete the validation here i want to delete it here right from location notice our from location comes from different vendors before it's from a warehouse right i want to make sure that we're doing from awareness right notice they're warehouses but if i change this again to suppliers we know it changes here but it's also.
Going to change here right we also want to know we're picking it up from suppliers maybe different suppliers so keep that in mind okay so continuing on what we want to do is we want to delete that validation first so not only in e6 but also in the entire column we want to delete the validations we cannot add any validations unless we first delete any existing ones that might exist once i have that i also want to clear any addresses right if i've got a vendor or warehouse here i don't want to show that supplier i want to clear it out so the user can add a new one right that way it's going to keep this if it's from supplier i want to make sure that if i change it to warehouse i got to clear this out and change this automatically so that i can then put.
Force the user to put in a new warehouse okay so clearing that out is going to help there also what i want to do is i want to know if it's a warehouse if i-4 is a warehouse what am i going to do i want to add the list of warehouses i want to add a list of warehouses to here and i want to add a list of warehouses to here right so we can do that with this line of code here range e6 h12 through h99 we're going to add the validation right we wanted a list type validation we want alert type stop and we'll give it a formula it's going to be based on that named range i showed you called warehouse name that is the name else what do you mean else else it's not aware so that would be.
A supplier a supplier is going to have let's type that in right supplier is going to have a different one now supplier is going to be simply we're going to then add the validation it's going to be exactly the same except the formula here is going to be based on the supplier name that is the validation that we're going to set there that's all we have to do to update the validation the most important thing you have to remember is always always regardless delete any validations that are there already then you're able to add any new ones the next thing i'd like to share with you is we notice that when we're changing the warehouse here we want that address to automate or likewise when we change the supplier we want that supplier address and that's going to be.
Based on the change event located right here in e6 so on change from supplier to warehouse we want to add the name and address okay add the address so it's going to be a change event based on e6 and of course we want to make sure e6 is not empty right if it is a warehouse right if i4 is a warehouse then what we want to do is we want to determine the row of the warehouse now the best way to do that or one way to do that here is going to be using here so i've got here a supplier warehouse row here inside b6 we're going to set the row and this is going to be an if statement because we don't know if it is a supplier or the warehouse it's going to be based on whatever is in i4 we know if i4 is supplier then we're going to need to determine the supplier row and that's going to.
Be based on whatever they've entered in e6 the supplier name we're going to run a match and we're going to add 2 because our first one starts on row three so that's going to get us the row of that supplier however if i4 is not supplier then we know it's a warehouse then we're simply going to use it to match the warehouse e6 is going to be based on a match using the warehouse also adding two because the warehouse warehouses also start on row three and if there's an error it's not found it's going to be showing blank and what that's going to do is going to get us the row so now we know that this supplier row if it here is on row 4 that means this supplier is on row 4. so if we take a look inside the suppliers and we see along row four we see fretters fans here right.
I got a lot of fretters fans in this video so we know it's going to be on row four and so that's how we'd simply find it so now we know the row is and that's going to be regardless of course if it's a supplier or a warehouse so we can then move on inside the code we're going to take put that into a variable called a long variable called wh row now warehouse row and that's only if it's a warehouse then all i want to do is simply extract the warehouse address from the warehouses b and c and d and e right so basically what we're going to be doing inside the warehouse locations extracting.
The address from b and then extracting the city state zip from c d and e and we could add the country i guess if you're global so i want to take this information and put it on the second row and this on the first row so that's what we're going to do here in that first row located right here inside e7 we're going to place that address and then inside e7 also we're going to place that city street and just like that so that's it that's all we have to do city the state and the zip there we're just like that so i guess probably put the comm in the right place but that's okay don't tell anybody all right so continuing so that's all we're going to be doing again the address based on b c d and e is going to go inside e8 that's all we have to do.
Otherwise if it's supplier we're going to do very very similar supplier addresses in this case are going to come from here inside f so the address is going to come from f and the city state are going to come from g h and i and we're going to place those in the same cells if it's supplied e7 will take on the address from column f e8 will take on of course the city state and zip in g h and i from that table from the individual supplier table that's it so that's how we automate either the warehouse address or the supplier address based on the change event on e6 okay what about if we change on receive locations now what i want to do here this is relatively simple on this one right here this is always going to be the received location is going to be definitely a warehouse one of the warehouses so what i want to do is when that's changed i want to make sure that we.
Automate the address and now of course we need the row of course not rows located right here inside b7 b7 is a match also based on the warehouse and that's going to get us the individual row of that warehouse so all we need to do is make sure that b7 if on change of h6 and b7 does not equal empty right we want to make sure that b7 contains an actual value then we're going to take b7 is going to take on that long variable for the row and we're simply going to add in the address just like we did before except this time it's going into a7 and a h7 and h8 that's it that's all we need to do.
Okay great so next up what i want to do is i want to show you on driver change on a driver change when i change that driver i want to make sure that we are going to automatically change that picture very very easily so we can do that just have so that's going to happen on a change event on l4 when we make a change to l4 we want that driver to change so target right and of course i want to make sure that b4 is not empty so i want in the b4 of course is a driver row this is a using a match based on the driver right and this is adding three our drivers will start on row four probably try to make it a little bit more consistent they should all start on row four generally you wanna start on row four so our driver is starting on here so row four okay.
And then we know that our first drive is going to be on row four so we want to make sure that we have that ability to do that inside our drivers and we want to do that so that's going to be based on change event so all we need to do if there's any change to l4 and we want to make sure that b4 the row that's associated with that particular driver is not empty then i'm going to run a macro called received order show driver we'll be getting into that macro shortly as well just like the little one and also the same thing on truck trucks located in q4 however the row that's associated is located right here inside b5 b5 is going to let us know what row associated for that truck as we change it so of course if i change that truck we see that the row automatically.