If we double click we're going to be able to simply increase the quantity just by selecting on it we're going to be able to decrease the quantity we are going to be able to clear pay print refund void and click next just by a click of a button we're going to show you how you can create your own in this training i hope you do like these trainings if you do all i ask is that you do click the subscribe and don't forget to click on that notification icon bell that is going to ensure that you get these trainings with you each and every tuesday i create these free of charge if you like this workbench you want this workbook and you want 200 of my best.
Workbook templates i've got an incredible promotion right now just released 200 for only 77 and that's going to include in a complete library workbook so that means a single click on your excel to open the application and a single click to open the youtube complete with a library i hope you'll get it that's going to help us out here keep these trainings for free every week if you like that that's the 200 workbook zip file in fact it's even more than 200 workbooks so i hope you'll pick that up all right this is the sample copy this is what it's going to look like you're going to be able to log in and log out also forgot to show you that so that's going to be incredible we'll have multiple users we're going to do it all from scratch this is the sample i'm going to close this up and we're going to recreate this right now from this sheet now let's get started here now i.
Did put a little bit of data in here and some other sheets that's going to help move things along a little quicker we've got a lot to do in this training as it is all right just go a little over a rundown of what we have in this application all the components of it this of course is going to be our pos sheet that's what you just saw in the sample we're going to be creating it right from this screen from this sheet but we also have an admin screen now admin we're going to be able to set categories set icons here so that way you can change this you can learn how to not only change this but to customize it and create it yourself we've got our main categories here drinks and beverages fruits and all the way across here then we have a set of subcategories so for example.
For drinks we've got soda wine beer and so on and so forth so each category has a list of sub categories gonna help us out a lot to do that we also we also have the tax name so we can customize an individual tax name we've got a tax rate so that we can customize it if we want to set it to 8.5 it's going to automatically be set and we also have a footer message now keep in mind i have some name branch already set this is called tax name notice up here tax name this is called tax rate this is called footer message okay i've also have order statuses so that we can customize if we create an order but it's not paid for it's going to be open if it's paid if we've refunded.
Or if we void it now this way you can customize it yourself i've also got different roles right a role would be for a user for example we might want a certain user to only be able to add sales and do nothing more not void not clear or not do anything we might also want another staff to be able to add sales and purchases right sales and purchase we're going to have both and also we want to make sure we want to have a manager that can do everything add edit void refund sales and purchases so we've got three roles of course you can set up more those are user roles so we've got all that in there and then also in the orders i've just got some data in some order so we're going to have an order id an order time whether type whether it was a purchase or sale a status whether it's.
Paid or open customer vendor if there's any not necessarily we don't necessarily need a customer but we can put one in if we want to we can have a user in fact i'll just copy this put some more data in here so it'll be helpful for filtering a user right whatever the user was just some test so let's update that and we don't need that so and also if they've paid the total price of paid now we're going to come up to this a little bit later on in this we have order items those are the individual items per order so for example if i've got an order id it was a purchase and row three we've got that the sku or upc number the product name the quantity and the amount if.
There was a discount we're going to go over this so you don't need to get everything right away you don't understand exactly what it's for but i just want to know that these are all the individual items on a per order basis so for example all of these orders here were for order number 1000. all of these order items are for order number one zero zero one we also have a list of customers just a basic list of customers we'll see how far we get if we're going to be adding new customers you can always add them to this list and we also have a sorted name of customer sorted name we want that we want one list of customers and then we want a sorted list so that we can sort them alphabetically also on the vendors exactly the same let's think about putting total sales here.
Total purchases we can do that pretty easily if we want to we'll see it depends we've got a lot to do in this training so vendor i've got a vendor id and just some basic vendors just something very basic a list of products i put in i had my my staff helped me with this a lot of products here so we got a lot of products and that's going to help us get create those product categories and those individual items so set up some products here and just some fake prices here and just a list of users a basic list of users user id username and then the role that they'd have so that is also going to help us so that's all we have there that's all i've set up everything else is going to come here on this point of sale so what we're going to do is we're going to create.
Those first two columns as we almost always do if you haven't had these trainings i'm going to i'll leave these for admin so what the idea is to eventually just hide these columns and then nobody will see them these are for admin purposes and we're going to use probably the first let's say 11 for one purpose and i'm just going to give that a different color and a board i'm going to pin this here and then what we're going to do is just going to give it a border and then i'll give it a green color because i want those specifically for the pos we'll give this light green and then i want to do let's say 13 through 17. we're going to this is going to be used for totaling and subtotaling in other words i want my totals and subtotals and and.
Payment information to show up here so i'm going to call call that a little bit different it's going to be linked here right so nobody's going to see this once we hide these columns then we'll do one space or column and then let's put some headers just we always do all the way up let's go all the way to let's just say y here i'm going to format those cells and i'm just going to give it a fill here and i'm going to do a fill effects so we're going to start out at a darker color here then we'll just go to a lighter color medium color here and then give it that fill effect the line below is going to be one less so i'm going to do that right here format those cells i'm going to do fill effects again we're going to go a little bit lighter on this one so i'm going to go this one to the lightest blue and then it is that lightest blue that we're going to use.
Inside the background the background and that's what's going to be our general color so then what i'm going to do is i'm going to go all the way down here just go all the way down enough rows so
That we cover that and i'm just going to give it that background fill which is that basic so we've got that nice fade effect here that looks good okay saving our work more often hopefully we'll remember what i want to do is i'm going to give this a title and so i'm going to use in this case i'm going to we can use whether it's cell based or text based but i'll use in this case i'm going to use a shape-based title which sometimes we do so we're going to call it this is going to be called point of sale and then we'll give it with inventory because i'm going to be tracking.Inventory on this and that means both purchases and sales all right so now let's customize that we'll increase the font here to about 36. i'm going to also use a different font we'll go with one of my favorites just clear we're going to go with arial rounded which is here i'll increase it and then i don't need any border and i don't need any fill so we'll shape fill will go to no fill and the outline will go to no outline and then we'll increase it so we can see it all point to sale with okay inventory all right that looks good it's nice and big i'm just going to.
Center that here make it nice and big and bring it all the way across here okay i like that and we'll give it a little bit of a different color maybe this color okay point of sale with inventory make sure i spelled that right otherwise you're going to be staring at misspelling for the next hour two hours or however long it takes us to do this so what else do we want here i want to i want to know if it's a purchase or sale that's kind of important right so let's fill in some of the information here what we want i want to know what type it is so we're going to put the type in here is it a sale or is it a purchase very important here i also want to know when the order is going to be loaded we may not need that i'll leave this one empty for now i don't think we're going to need order load but we might need this a little bit later on.
Let me also put in the three i want to know the order database row in other words what row order database bro what roads here's our orders here's our database is it row three four five that's going to be based on that order id i also have some name ranges already set up so that's gonna go here here i want the next order id next order id we're gonna go over the name ranges very soon here i'm gonna want the search order row and so when we search for something i'm going to show you what that's like and then we'll we'll build this out a little bit more but let me put in after that the selected row i want to know what row selected let's go ahead and continue with.
The design here so we can know what we're going to do here inside d3 this is emergence center i've already had it set up i want the order results remember i showed you that search form right i want these columns to be hidden unless we're searching for something and that i want those results to come down here so we're just going to call this order results and they're going to come here and then what i want is the ordered on day i want to know when it was ordered that date order on and the next one i want customer or vendor could be a customer or vendor in fact we don't know if it's going to be a customer vendor so what we can do is we can just write in customer vendor we'll even abbreviate that make it customer or vendor then what i want to know is i want to.
Know the order id which is the order id so when i select on one of those orders i want it to to load up here we'll give it some a little bit of a color format those cells maybe just the gray fade out so gray will be our accent color we'll use that so blue is our main color so i'll use gray as our accent color and then again i'm going to use the fill effect here and here and then i'm going to do here the same thing a little bit less formatting those cells and then fill effects here then we'll just go to the light gray here and then the lighter gray here so that's going to give it a nice fade effect for the both the heading and the sub heading here then i'm just going to also add.
Whoop and there we go so we got the so i want those results now we're going to put a button here to close that right if once i'm done with those orders i want these to be hidden so we'll add that in very very soon what else do i want i want a spacer here so this row is going to be a spacer here i want the user to enter the order here and be i3 i want them to enter so we'll do enter let's just put order and they'll put an icon there and then here i want them to search for the customer enter customer or vendor we don't know which one it's going to be could be a vendor could be a customer here what i want to do is i want to have let's take we don't need a data validation.
Here let's clear that out data validation and then clear clear all okay so here what i want is basically like a formula based on whether it is a sale or purchase here i'm going to put the customer vendor notice i have a drop down list here data validation it's kind of i was kind of cheated a little bit right okay but that's okay so basically here in this list i want the customers or the vendors drop down list here so here in this label portion i want to show what is it a customer or is it a vendor so but i'm not sure it's based on whether it's a sale or whether it's a purchase.
If it's a sale it's going to be customers so equals if in this case b1 equals let's get if the word right not iffy it's not iffy it's for sure if b1 equals sale then worse then we're selling it and we want to show the customer right or something we're selling then what i want to appear here is customer and then a colon customer then colon otherwise it's going to be vendor and then colon okay so that's it so basically as soon as this changes to let's say purchase and then it's going to automatically change the vendor that's what i want okay perfect so then what they'll have is a drop down list of either one here and then what i want to have here is this.
To be able to scan in the u the s the sku or the upc or whichever one that code right i want to put
Scan the user will scan it in here sku or the upc it's the same thing upc called different so here they're going to scan for that item here should have cleared out the data validation so i'm going to do it right now so i'm going to do that just to clear it out and then we'll go ahead and add it in data validation and then click ok and then clear all okay basically that's what i'm have here so these three i'm going to make white here so we want to put the white background because they're going to be user entered and then borders all the way around for everything so here we'll go all the borders then so basically they're going to enter the vendor or the customer here based.On whether it's for sale they're going to enter the upc here and then also what i want to have is a user right we're going to have a user name i want that to appear here and then i want the order on date here order on date right here below that we don't have much here we don't need to put much in here i'll put a formula in here but we're good on that so the username will go here so we'll just put in let's say fred fretters you know fred's a great and then that's just temporary fredders fred's got to make his appearance in this video he's made his appearance everywhere okay now let's say the status here i want to know the status is it open or close status and then let's just say it's open that's going to be that's not going to be user generated so that's going to be oh so then i want to put in let's say after that maybe the order id order id and that's going.
To go in p order id so the official order id is going to go right in here status is going to go here i like that okay so i'm going to put borders around there notice it's kind of merged and sit because i just cleared it out from the previous that's why things look good just to make things faster it's already going to be a long video so i like that the order opens so none of these are going to be editable they're editable they will stay in blue just like that here what i want to do is i want to have the quantity on hand and so here is going to start out here's going to be where our table starts out so right under here is where we're going to have i want to have that table so.
Here it's going to be the sku we'll put the sku or id whatever it is ku upc id here then the another header we're going to the product name and then also after that i want to know the quantity i'm going to put in the quantity or order quantity then what we'll do is we'll have the amount so then the amount what is the initial amount i want to be able to put in a discount this time if they want to put a discount on a per item basis that was one of the things that was requested so i want to do that for you and then the total okay so that's pretty good so i got that so all of these are going to get uh just a basic color here this is what's going to show up on their receipt this is the header there's the first part of it so i'm going to format those cells and then we'll just give it a let's say this gray color here we'll give it a light gray color that's.
A good start so it shows out and i like that there and then we just need to give it a border all the way around i'm going to give it a border all the way around it here format those cells and maybe i'll do the border all the way around and then i'll do the fill i'll just do from light to medium i think that should be kind of a nice look for the receipt there so i'm just going to go from light in the two color and then to medium here that's nice okay clicking okay and then bring this up into your screen view and clicking okay so down here is where i'm gonna have all the information here and down here is we're gonna we're gonna have conditional formatting so that as we add rows here we want that conditional formatting and then what i want to do is what you saw was called a floating.
Footer now that footer is going to be based on shapes right so that means that footage is going to include the address the barcode the subtotal everything it's going to be all based on shapes that way we can easily move down that shape as we add or remove items it moves up and moves down automatically very cool so i'm going to show you that today so here is where i want to put let's put in the total let's bring and let's bring this out a little bit here and just like that okay i don't want to have a border between there so i'm going to format that so let's just remove that center border here very good i like that the way it looks good and so here is where i want to put.
Those quantities on hand so i'm going to format this cell and just give it a little bit of a blue border this quantity on hand will not be printed on a receipt so it'll be a little bit different but i do we do want to be able to show when we add an item how what is the stock level of that so we want to know that so that's good so we'll have that stock level every time we add an item so okay just going to put some borders around there that should be fine any border so here's where we're going to have the items and then here all the way over here is where we're going to have our specific shapes right so they're going to start here i've got in fact six shapes here it's going to go all the way over here and it's going to be really cool well let's do that let's.
Let's bring these colors down i want to add a few more columns on this one so we'll just bring it all the way over here that's good so bring it over basically i'm gonna have six different categories six different categories let's build that out now i want those categories to show up based just like these columns are right up here you can have a log out button right up here so these shapes are going to be here so let's add those category shapes in now so i'm going to insert a shape it's going to be just a square shape and i'm going to put it down here now the size of it let's do 0.75 by 0.75 that should be 0.75 and then by 0.75 do that that'll be perfect what i want to do is i want to have the first one show up here right about say here let's give it a look i want to.
Give it a specific look something like this here probably go a little bit darker on the color there but we'll set those colors up inside vba because i'm going to have them switch when we select it's going to go to one color and then it's going to go to other color so i'm going to give this a name because i want this to be called category 1. so i want this so category 1 2 3 4 5 6. so we're going to replace them it's going to also be based on the columns so i wanted to show basically on the left of this column right here and then now it's spaced out according now i've also spaced out these columns a little bit better to help us so i'm going to duplicate that and we're going to create six of them here and this one's gonna be called category two and then the names are very important.
So then i'm gonna do again category three here and then three more times and then ctrl d again making this category four and the placing is not as important right now and then duplicating it one more five and then lastly six so i've got six main categories that's what i want to do with it so six so we've got our categories in here we're gonna put those icons now what do i want the contents of those i've got these icons we're gonna they'll be spaced out a little bit more like here anyway so a little bit so we want the last one here based on this column here to.
The left and so what i'm gonna do is i'm gonna hold down the control here i'm gonna make sure they're lined up vertically and then i'm going to format and then i'm going to align those i'm going to just distribute them horizontally so what do i want i want those to be basically equal to whatever is in these categories here so this one if i link it directly it'll be automatic so why don't we do that based on the admin what is in d4 so all i need to do to link that up is just inside the formula bar here do equals and then go over to admin here and then just change that to d4.
Now i'm going to copy that and i'm going to enter so that's going to add it up automatically there but that's not exactly i want to format that but i want to format them all so i'm going to hold down the control and i'm going to adjust the size and the properties and we're going to go into the text so the first thing what i want to do inside the text box is i really want to reduce those margins down to almost zero so we'll start out with zero because i want to make sure to fit as much text as possible inside i want it to be placed on the bottom i want to put an icon and i want to put it centered like that because i want to put the icon on top of that and i want to do it for all of them all right that looks good i think i'll add a little bit of padding on the bottom notice the bottom there size i'm probably just a tiny bit so the text box on the bottom we could do.
Something like point zero two that'll raise it up just a little bit and we've done it for all of them because they're all selected okay saving our work and now what we're going to do is i've already copied this so now all i need to do this is going to be if that was d4 this is going to be e4 if this is that was e4 this is going to be f4 and so on and so forth this will be g and make sure we have all of ours and then this is going to be h and then lastly i if i remember my alphabet properly it's been a few years but we'll try and then i okay so now we've got them all there so we've got all of them they're linked which i like so if you were to change any one of these it would automatically change of course you'd certainly want to make sure that your products also have the.
Same categories that's very important so we've got our we've got our main categories now and that's i like that the way they look and we're going to change the way they look when we select on them we also want our subcategories to appear here now we're going to have up to 12 subcategories so let's add those in the subcategory is going to look a little bit different what i'm going to do is i'm going to duplicate this one and i'm going to bring it down here and of course it's going to have a different look different feel different size so first thing i want to do is going to keep the same width but i want that height to be about half it or a little bit more so i'm going to do 0.4 on that so then also i want a different look so let's just temporarily give it this look and.
We'll give it the black font here so we don't know this is going to be dynamic what's going to be the text is not going to be equal so it's not going to be equal it's going to be based dynamically on whatever we say this so we'll just keep that there for now and basically so as what we're going to do is when i select drinks and beverages what i want to do inside the products is i want to put whatever category is in here and then what i want to do is i want to have all the sub categories for that or also what we can do is from our admin we've got it here so all i need to do is i know.
That so what i want to do is i want to build all 12 shapes based on whatever category was selected and then i want them to be equal so we can use a formula but we'll use it inside vba because it could be d5 it could have could be d you know e5 and so on and so forth so we'll bring that but certainly we need 12 shapes exactly the same size we'll do six across and then six down so that's what i'm going to do now okay so what i'm going to do is i'll just clear out the text here and then i'll duplicate that one here let's clear that out we don't need the text there we'll be adding it in dynamically i'll duplicate it there and place that right here and then we'll do the same thing again.
Here here and here and then all i need to do is just copy those and we'll make sure that they're lined up properly and spaced out accordingly so we're going to line those up horizontally and then i want to space it out i'm going to be based on the right of this one so we're going to right justify align the right here and i want to align the left one on this one here so that everything's spaced out then what we can do is space them out horizontally so align again distribute horizontally then i've got them there then what i'm going to do is i'm going to hit ctrl d and duplicate that and bring that down right here so the idea is this so as you saw in the sample if you saw the beginning here what we want to do is just bring click on here and then have those 12.
These 12 populate automatically so that's exactly what we're going to do all right so let's undo the selection save our work so far and then when we select on that i want dynamically the shapes that are going to be dynamically the shapes whatever food then what we're going to do is we're going to put in that category we're going to put in that sub category we're going to run an advanced filter so for every drink there's a soda i want that product name and i want that product to be here that upc or sk you'd appear here then what we're going to do is we're.
Going to populate all the shapes with whatever's located in the product name whatever's down there we're going to show up here but now what i want to do is i want to rename these shapes they've all got to be named by subcategories so i'm going to open the selection pane here we're going to start out here i want to rename we'll start out right up here at the top let's go ahead and here that's going to be 12. so what i'm going to do is i'm going to start at the top and call this sub category and then 12. so i'll just copy and paste the text and then we're going to go down and just make these 11 so paste this and then 11 and then we'll do down here and then this will be 10 and we'll go in reverse here and then because these names are going to help us out we're going to need to know these names in order to assign specific text to them we'll have to make sure that.
They are okay now each one of these now have their own independent name and they're all lined up and that's going to be ready for our subcategories okay we can close this out and we can continue building out our screen so we're going to add some borders around here we need the borders around here i want to know the user ordered on here that looks good okay we've got our table now let's add some conditional formatting then what we're going to want to do is we're going to add our footer shapes i want to add a shape to a footer but let's add some conditional formatting here so that we can see different how we can show that up so i want to know if there's based on if there's a value here so let's do that right now so inside the conditional formatting new rule and in.
This case it's kind of a little bit different what i want to do is i want to color this row white so if so that means give the user the ability to enter the next row normally we only color the rows that have valid but this time i want to call it the also i want to consider the row below even if it's empty so that means we're going to start up with the row six so it's saying if six and we're going to apply to seven and i'll explain that in just a moment but first of what i'm going to do is go to conditional formatting and new rule and i'm going to be using two different rules because i want alternating rows but it's going to be based on two conditions so we're going to use and equals.
And and what is the first condition the first condition is going to be yes i know it's weird i six is that going to be equal value or not so we're going to i6 and of course i want to apply to every single row so going to make sure that that is not absolute does not equal empty then also the second condition has to be a mod bro so in this case odd or even rows mod based on so this would be the even rows so the even rows what i want to do is give it a white color and then i'll give it a lighter a lighter maybe a lighter gray color very light gray color so in this case i'm going to format this cell i'm just going to go to a fill of white so that's going to be white now what i'm.
Going to do is i'm going to actually will change this to one i'll take it out for the odd rows but what i'm going to do is i'm going to copy this first then i'm going to change this to the odd rows and then i'm going to click ok now i want to add one more new but let's absolutely apply this to more than just 14 so 99 then click apply then what we're going to do is click a new rule use a formula again paste this in here now we're going to do the even rows i'm going to format i want this gray but i want a little bit lighter gray now we can do fill effects and we can do if we want to use a recent color we can use fill effects something that i've used here this gray here's a recent color so i've got that here and now i can use that our recent colors.
Are always stored there click ok and now gray is going to be there click ok and then i want also to apply to 999 so i'm going to change this here and then apply again and then click ok all right so i like that now i got that one extra row that's exactly the way i wanted to the reason we started on six but we're applying to seven that's why it's one row down i want that extra row so now what i want is i want to put some borders on here so that we can look at that so let's take a look how do we put some borders on well the best way to do it is basically put the borders on now and then use conditional forebank to clear them out so i'll just put some borders on them and format those cells i'm going to put some left and right borders on there just some dark borders.
And then it is conditional formatting that will hide or show them accordingly so what i want them is i want them shown here but i want them hidden everywhere else so how would we do that well again it's going to be based on that one extra row so again conditional formatting manage rules this time a new rule here and it's going to be based on a formula it's going to be based on this again i6 is going to be equal when it's empty right because i want to do that and then i'm going to format those and i want to clear all the borders out here clear them all out so none.
None here clicking okay now make sure we adjust that applies to i need to fix that applies to obviously it's not going to be i6 it's going to be based on i7 and all the way down to 999 and so what that's going to do is when we click apply it's going to actually i got to go ahead and update that and then conditional formatting manage rules but we have to make sure that that row here is not absolute right it's got to be for every single row so click ok and then apply now it's going to be relative so now we have it all those rows are gone except for the ones and that actual row that's what we wanted on perfect like to add one additional conditional formatting for this table and that's going to be based on the selected rows so again i'll highlight.
Them basically and go into the conditional formatting i want a new rule screen based on a row so i'm going to use a formula and what rows where's that row it's going to be located inside here b 6 i want to know the selected row so right now b6 is equal to the row when it's equal to whatever row we selected i want to format that and i'll just give it a light blue effect here something like that maybe just a little bit lighter blue so we'll go into more colors and then just click okay all right i like that i like that that's good enough click ok and so now we just need to update the applies to so we can make sure it applies to everything copy that we want it applying to everything click apply and now so when i select something this row will change so.
It'll go to 8 and then i want that row and maybe what we'll do is we'll bold the font also so so that it stands out a little bit more so all we need to do is go into the format and then go to the font and bold it so we can clearly see that it's been selected so now you see that one is now bold all right good so below that what we want to do is we want to add our floating footer so as we enter items here that floating footer automatically drops down drops down vba is going to help us with that so let's add in that right now but let's add in a few.
More buttons i want to add in the buttons and the shapes here and then what we'll add is the icons in after that because the icons will go on top so let's do that now so add in that floating footer so how are we going to do that well first of all i'm going to insert a shape just a larger shape that encompasses the entire area something like about like this and that shape is going to have a fill of white and it's going to have a shape outline of black and i don't want it that i want a very very basic outline you know something similar to like that which is our i think we can go with that one at half point that might be that should be look that looks good okay the width it will be.