Sure that you're getting these trainings to you each and every week I bring you on YouTube so make sure you do subscribe with the button below and always click the notification icon belt I'll be sure that you get those trainings said to you right away as soon as they're released each and every Tuesday and if you do like these videos and you like purchase orders I'm gonna be creating a comprehensive purchase order with tons and tons of features in our excel for freelancers mentorship program where.
We're going to be creating an amazing application and I'm going to show you how you can create your own applications we're gonna go from concept to cash and everything in between in this program so if you like to use your Excel skills and create reoccurring and passive income this is the program to do it we're going to show you how to define design develop and deploy your own Excel application so it's going to be a great training if you like that have a look I'll include the links down below or just my Excel mentor com it's going to be an amazing program so get in now.
It's really great I think you'll love the program also if you do like these applications they're always free you can download them using the links in the description below make sure you do especially on these live trainings every once in a while there'll be something small or some small issue that I might forget during the live training but I'll make sure that any Corrections are made in the downloaded version it's absolutely free use the links down below and we'll get that full version right to you right away if you like these I've got actually a hundred available in a 100 workbook pack so if you don't want.
To download each and every one of them and you want a 100 of my best workbooks you can get that for just $37 all right let's get started with the training we got several sheets in this particular application I just want to go over them with you our purchase order is going to be designed in this screen right here all of these things can be done in here we have a list of purchase orders I've done it just started off with the table that gonna move things along a little bit quicker just the basic things all the components of the purchase order including purchase order number the date.
A status vendor and the delivery address one two and three we also have a list of vendors I've just pre-populated this just with some fake data just to help move things along that's all as well as the list of items that's important that we just won't have some data we're gonna be using a formula to develop the quantity on hand but I've got to set a minimum stock level of five just set some purchased coins just some basic data and I've also just put in some criterion results here that's gonna help move things along as we develop the code.
Also a purchase items I've also put all the list of the items that we've purchased will go here and I'll show you explain the criteria in the filter just setting a few things up beforehand so we can get these done I know these trainings tend to be long so I want to advance it as much as possible II set the basics and then I have a list of sold items now if you caught our training in the before a few weeks ago when we create an invoice an invoice would create something like a table like this so I've pre-populated with some items that we have already sold and they.
Would go here so we've got an invoice number an invoice date the item that was sold the quantity sold and the price this is gonna be important because we need to calculate how many items we have on hand and basically the item that we have on hand are going to be all the purchased items - the all of these sold items on a per item basis so that's important data that we need to start off on and so I wanted to get us started so let's get into this purchase order screen and it's gonna be a little bit similar than the invoice so if you haven't caught that we've got an invoice.
Here I've got some columns just pre set up just to make things go along quicker now the first column what I want to do is just as we always do I'm going to use the reserve those for the admin so we're gonna color them gray I want those to be for the admin because we're gonna put in some formulas and things like that and we're gonna hide these columns and then when you create these applications it's always good to use the first two columns for admin and then you can just right-click and hide them once you release your applications and you can also protect them we're gonna call this.
Automated purchased orders so I just want to give it a title to this and we'll extend this this will not be part of the purchase order this will be part of the top of the screen and then I'll create the purchase order so let's create that and the merchant center that I don't want to create that and then our purchase order actually will start right here I'll put that in column E so this will be the title of the purchase order and then what I want to do is just put in some formatting here we'll make this one green a little bit different I'm.
Gonna format those cells and give it a fill and a fill effects and then we'll just use a green something very different something very unique so I'm gonna fade down and then what I'm gonna do is the second one on this one I'm just going to probably dis column just see and perhaps I threw J I'm gonna color those a little bit less of a color a little bit lighter of a face so we're gonna give that a fill effects and we're gonna go into the lighter green so that's gonna go from the medium green to the light green and that we're gonna give it a nice fade effect the rest we can just color green so then what we can.
Do is we can go down to so let's say row 31 or something like that I'm gonna try to keep everything on the screen for you
This time so we don't go off but at least for this fade and then for this it's all we're gonna do is give it this green and then that'll give us the rounded out look that we want just so we can encompass the purchase order in the screen now it now this purchaser is not going to be very very long because I want to try to put everything in the screen for you so we're gonna have this a larger font.Let's make this larger and let's italicize this and give it a color also I want to give it a nice color maybe a green color so it stands out something like this and make it bold so we can clearly see it okay and I also want to do this maybe we'll give it a different font so we can see what we like like perhaps let's go with Adobe rounded bold that looks good and give this a color I want to give it a font just so we can know what we're working on here and uptown says and I also want to put in.
Some icons so our purchase order is gonna be basically made up I want a Pio number in G so we'll do Pio number and then a colon here and then I also want a Pio date purchase order date here and then I also want status so status is important because we have to know if it's open or closed I'm gonna write justify these and then I'm gonna left justify this is where it's gonna go in the date should be formatted as a date so let's go ahead and put that us on a short date put it in temporarily a 1 here 1:20 and then the status we'll just put.
It open ok so basically at that point what I want to do is I'm gonna put in the company name here in a company logo so let's see we have Freddy's t-shirts incorporated and then we just put in the street this gonna be the top of it ok we'll just put in any town California USA ok so that's the top of our purchase order next up what I want to do is put a divider in here and then have it very different so maybe a color like let's.
Say use this dark green and then a white font on this so it stands out and then bold so here I'm gonna put in the vendor and then the dough let's clip those in capital so it stands out vendor and then maybe delivery address we want that we have to know the delivery address okay delivery address and vendor we can italicize those and next up so the vendor would go here so I'm gonna put in a vendor name here and then vendor address one in vendor dress to here and.
Then skipping a line probably on ten we're gonna do the same thing so let's just copy and paste this cuz I want a divider probably on ten and then what I want to do is I'm gonna put in the item here description I want the description of the part the quantity here and the price and then the total so we're gonna Center that also okay good so it's gonna be coming along fine and of course we're gonna get rid of the grid line soon because if we don't want them to look and then I'm gonna drop down and.
Probably about say row twenty five let's bring this up just for a moment in twenty five let's say right about twenty five here we're going to put our totals here and that's gonna go for about three rows so let's color those white for now and just so we can get an idea and then I'm gonna put in right here I'm gonna put in the same color font I think I want a little bit lighter of a green here but that's okay so let's put in that color let's drop this back down again and scroll down and I'm gonna change this color I like a little bit lighter here okay.
So we're gonna put a background color of this looks better and then I want to put here let's put in the PIO we'll change that to white of course POS subtotal okay and here would be the taxed perhaps let's say 10% just put in any tax so we can have a tax and here the order total okay great so we have all those lists out of coal in here so we're consistent and then what I'm gonna do is gonna make these white bold and right justify so white bold and right justify there so.
Now we have this and I'm gonna merge and center this here because I want a footer message there's some Merchant Center left and upper so we can put in like please deliver between 12 let's say and 5 p.m. just so we can get a nice footer message the purchase order should have a footer message alright good it's starting to come together now let's add a border around this I'll move it up back and forth so.
We can see I'm gonna add a border around this so I'm gonna format those cells and put a border around here so go into the
Border and I'll use a green color sorry it's off the screen here let's drag it up here I'll probably use let's say this is the same dark theme and then give it an outline all the way around it and then I'm going to add in some dotted lines here so here format the cells here and I'll bring this up what I'll do is we'll just add a little bit of like a lighter dotted line here just so we can see that's a bit too light and that way it will give us a nice differentiate.Between our order so we can see good alright and then another dotted line here I'm going to format this set let's highlight those format the cells and then we're gonna use the same color border all the way around and inside and then I'm just gonna make this a dotted line you can't hard to see but I think so the left so that basically is gonna say okay the left one for so that we can there now it's starting to come together our purchasers we can go into the view and remove the guidelines to make it.
Look a little bit better nice it's starting to come together now our purchase orders alright let's put in some buttons and also I'm gonna have this as the item row and I also want a database row I want to know the row where it's saved to what is the database row well that's going to be in the purchase in the sold in the purchased items here it's gonna be this row right here 3 4 cuz I need to know where it is so we're gonna store that right here and the item row it's gonna be a formula we.
Need to know what row the item is on like for example this item would be on 3 or 4 I need to know because I need to pull in the information I need to pull the description the quantity and the sales price so the row is very important so we're gonna put that row with a formula here let's add in some pictures here that is going to help us out and we're going to insert pictures and I've got some pictures saved already that's gonna help us out and great so we can add it up I'll drop this down and then we're going to set the sizes automatically and then.
Readjust them accordingly 0.2 - alright great so now we have our buttons and what I want to do is I also want to add in some buttons so let's add in some shapes we'll make it a square shape and use the same theme color let's say we have here let's do point three four and then perhaps maybe one point four that's good one one point four okay I want to give it a theme color so we can do that let's drop grab at this.
Theme color and I'm gonna duplicate that maybe I think I want about five buttons so control D control D control D D and D okay so we've got our five buttons but I want to move these icons on top right because we just created so whatever we created last is going to be on top so I'm gonna take all these icons here format and I'm gonna bring them to the front that's gonna place them on top of these which is where I want so alright great so we have our icon here this is going to be our automated purchase orders let's increase this a little bit I just want to make sure everything's on.
The screen for it I don't want to go below this so that's why we're gonna do it alright we have our let's get out of this the selection tool and increase this that's gonna be our purchase order I want an icon for our company so we're gonna put that here and that looks nice but I don't I want to change the color of that I'm gonna look at more to our themes so let's grab this green here there we go now it's looking nice and we've got automated purchase orders we can shrink this down a little bit but before we do let's start building our.
Buttons now so what a kind of buttons do I want I'm gonna bring this back down we'll just that now I want to bring that back down to 0.34 cuz I increase okay so what kind of buttons do we want we want to add new that's for sure so I'm gonna type in to add new P Oh what else do I want I want to I want to cancel new P Oh cancel new one we're in the when we're in the new mode and we don't want to be in the new motor we want to cancel the new so.
Well show you how that works I'm gonna generate a Pio and to bring this down so you can see it I want to generate one what do you mean generate generate that means if I need items based on the minimum quantity we're gonna generate it automatically it's gonna be really cool so it's gonna create items automatic create appeal automatically in just one click I want to print key oh that's important and I also want to save or update save or update that's important and what else do I want I want that's it.
All right we're good to go so just those five buttons and let's uh now hold those down and what i'm gonna do is i want the icon on the left print let's change this to print print and so we're gonna hold these holding ctrl how about all the buttons and I'm gonna format them all together so we're gonna write justify them here under the right right justify because I want to put that icon on the left and I'm gonna we won't line them up just yet so let's build out our buttons now and I'm gonna copy this control D cuz that's the background I'm gonna use I'm going to size this.
Accordingly this is the background I want on it because that's gonna be concerning our and so that I got the size right and then we can just ctrl D duplicate this two three four times add a new P oh I'm gonna use this same background I can cancel and then we're gonna do generate print will have its own one so we don't need that print this can be centered because it's just a small text alright great so we want the printer here I'm gonna bring that up that looks okay for now and what I want.
To do is I want to save or update so I'm gonna use this for the save or update this icon here for the save update and I'm gonna bring that for actually I'm going to bring them all forward because I want them all on top some and hold down the control we have two of these we don't need that format align and the format bring to the front and we don't need two of these don't remove this one okay so Jen's just going to go for generate purchase orders I want that to generate it and I want to cancel new just in case we're getting the cancel screen so we're gonna put.
That here and then I want the Edison to be add news so alright good now we've got our button so let's group them now we're gonna hold down the selection group and name it's always important to group a name format align to the middle and then I'm gonna group them and then I'm gonna name it so this is gonna be called Save button okay enter and then add new same thing here we're going to let's say align the middle good that's gonna give it and group it and then we're gonna call this add new button and.
Cancel new same thing we can just actually that's kind of lined up already group it cancel new button these important we need to show the cancel new cancel new and the add new are going to be in the same location so we're gonna focus on that and I'll show you that this one align the middle and then group it and then just call this generate the O button alright and the last one print P Oh button there we go then we've created all of our buttons group it.
Print button it's fine okay good so let's bring these up to the top now get out of the selection when all out the news I'll have to save in the update right here that's gonna be the first add new will be below it then cancel new and add new are going to be in the same location so let's there only one of those is going to show at a single time so not both so we can put those generate and then print all right I like that I like the locations of them let's do our selection two and line them up we're gonna group them I mean we're.
Going to highlight them all select them all actually format align the left and then of course I want to distribute horizontally vertically distribute vertically perfect okay cancel new I want these right on top of each other because they're only going to show at the same time so we're gonna slide that over put those right on top of each other just like that cancel new add new are gonna be on the same and I'm gonna move this back we're gonna use this second so send to back we're only gonna show one of those same time all right it's looking really good now we can get out of the selection mode and.
Put some borders around this almost done with our formatting here that's gonna look really good and we're gonna set the print area this is gonna be our print area right here so we can do that now might as well under the page layout and then print area and then set the print area that's gonna be our print area let's take a look in the preview and just see how that would look all right that looks nice it's gonna print all in a nice so we're good to go on that now we can start to add in some named ranges that's the second point we've got all over buttons and we also want to add in some information here so perhaps in.
Venner I want to do vendor row I need to know the row of the vendor I also need to know whether it's a the rows of P Oh what roses that's the database row right the purchase remember we have purchased items purchase order list it's gonna be a list so I need to know what row it's on here and I'll show you the formula for that in a moment but we need to track that I need to know if it's a new P o or not and I also need to know if it's loading P o load we need to know the difference between when it's loading from saved when we make a change here when we enter to hear about the second.
Purchase order to load when we entered three here on literature so basically based on the change I want that purchase order to load alright so next up what I want I also want to know the next Bo number next P o number so we need to know all that information let's highlight that and color that distinctively so that we can differentiate between that and everything else great alright so now it's time to add in our named ranges let's take a look at this what kind of named ranges do we have well the first thing is we need a Pio number that's important so let's say.
We have POS here I need to named range a dynamic named range for here so let's create that under the formulas name manager notice we only have to print order that we set new let's just call this P Oh number and though I want to dynamic so what I'm gonna do is I'm gonna copy this I'm gonna add offset to it so control C and then I'm gonna click right here and then put it offset P Oh list I want to start it on in the header row but we don't want to include the header row that's just in case.
There's no data so it's gonna be comma and then one wrote down its gonna start com , count a what on account I want to count all the range from P oh let's say to a and then perhaps nine nine nine or something nine nine nine or something like that something large like that okay and then one call them down one row down -1 one row down comma one let's tap out and tap back in and make sure that encompasses our data with the dancing ants perfect you see it's just covers that okay good so we have the P own.
Number what else do we need I need to know the vendor names obviously we're gonna so let's click on the vendors and see get some name ders I'm gonna do the same thing with a vendor name so formulas name manager and create a new and we'll call this vendor name and then I'm gonna do the same thing here I'm gonna copy this right here just as it is and then I'm gonna put in offset starting at a 2 and then comma 1 means one row down comma comma.
Count a I want to count all the text in this range what range let's say they eight nine nine nine and the minus one why because we're including the header row but we don't want that that's just in case there's no data comma one tab out tab in make sure it encompasses all the data to check it click okay great so now we have purchase order and vendor name what else do we need well I need the items purchased and the items sold I need that count number so let's do that right now in the items purchased we don't have anything but let's just say.
We have some quantity one corner right let's just say we have some data it's encompass out an add a named range and then we'll delete the data formulas name manager and let's call this items purchased new items purchase and then I want the purchased items basically we're gonna do the same thing using offset so copy that as we repeat it you'll learn it very very easy offset and then again we're going to bring it here one down.
Count a paste it in and then we will use a larger engine 99 - 1 comma 1 ok first column so look at that okay now we have items purchase perfect click ok and now the last one we also need items sold I want to know all the items sold so that's gonna be here so again one more formula name manager new and this time we'll call this items sold items sold and what's that gonna come again copy this copy the main thing it makes it a little quicker add in the offset Oh ffs.
Et all right and then also one row down really have a few I don't have committee comma one count comma comma count a paste it in add in your large range here - 1 comma 1 ok tab it over make sure it's includes all the data good so now we have this let's take a look at our named ranges we have items purchased items sold pio number a print area and vendor name ok we also need the.
Item names one more let's go in there i want to know a list of items name and that would be here item name so we can add that and that's our last one new and then we'll call this item name that's fine name as it is again we're gonna copy this i also want a dynamic named range offset items and then clear this out comma 1 comma comma count hey you're getting it now hang a bit nine nine nine alright so this is the last one minus one comma one okay tap out tab in.
Perfect okay good so let's go over this we have item names we have all of our dynamic names for the all the items that we purchased it even those no data yet all the items that we've sold the purchase order numbers we need that and we have the vendor names great so we've got everything we need we can start adding in some formulas now so let's go the next purchase order well we have if assuming let's say we have the first three used i'll want the next one to be four so we can use a formula in math that we always want to wrap that and if equals if air if there's an air I want.
The max max of what the PIO numbers Pio numbers but I don't want the max that I want to add one because I want the next one it's a plus one but what if it's an air well if the tenor I just wanted to revert to the first one which would be blood okay great so it's for now let's left justify these drop that down go to home and left so what if it is in there what if there aren't up you know then I wanted to revert to one which is what we want now so deleting those it's gonna revert back to one perfect that's what I want I'm going to set this to one it's.