Create a Simple Bill Format in Excel for Sales with Ease

Simple bill format in excel for Sales

hello this is Randy with Excel for freelancers  and welcome to the point-of-sale application   in this exclusive training I'm gonna show  you how we create an amazing point-of-sale   application we're gonna start from  the beginning from in a blank sheet   so every step every line of code and every  field and format I'm gonna show you how you   can create an amazing application right  before your eyes so let's get started all right thanks so much for joining us today  I've got a really fantastic training for you   today something I've never created a point-of-sale  application I'm gonna walk you through it I hope   you'll stay it's gonna be a little bit of  a training not tons of code more formatting   we've got a lot to cover today I'm really excited  about this before we get started I want to make   sure you know that you can get this application  download it using the links in the description   below absolutely free in fact this one's gonna  be a zip I'm gonna include some pictures for you   maybe some bar code fonts the application so you  won't want to miss it make sure you download it   and also especially with these live trainings  I tend to make maybe there's a few updates or   a few issues worth the live so you always want to  download my version and compare your version with   my version I always appreciate when you try to  program these yourself and I think it's really   fantastic so you always want to compare those if  you have not subscribed yet please do so using   the button down below and make sure you click the  notifications belt I create these trainings each   and every Tuesday absolutely free for you and  I love to see what you can create so I always   appreciate that if you do like these trainings and  you love Excel you want to turn your passion into   profits I have just the course for you in fact  our Excel for freelancers mentorship program   is gonna teach you just how to do that turn your  passion into profits in fact in this program we're   gonna go from concept to cash and everything in  between in weekly videos while I create an amazing   accounting right along with you and I hope you'll  create your own applications right along these are   weekly subscription-based it's an amazing course  and I hope you'll jump on the bandwagon tons and   tons of students are loving it and learning how  to create and sell their own applications alright   great glad we got that out of the way and also if  you do like these applications and you want over   100 of them I do have a 100 workbook pack so I'll  keep that in mind I'll put the links down low if   you want to get 100 of them it's just $37 okay  thanks great let's get started as you can see   I've got a blank sheet here nothing on here I've  got sheet number one called POS that's gonna be   our point-of-sale application I've got another  sheet just with a few item names and these are   linked to pictures I've got some pictures for each  of these it's just simply item ID item name item   description price and an image this image is  linked to a folder and inside that folder I've   got some images here as you can see right here  I've just got some pictures and they're basically   they're linked actually on my desktop I've got  a folder of the same pictures on my desktop I'm   include these pictures so you can play around  with it as well so you can work you don't have   to create your own pictures that way you can  try out and add features and customize yourself   so I'll include these in this little table so you  have everything you need all you'll want to do in   your own applications is make sure that you update  these links with your actual filings right these   are to mine so that's not gonna help you these  image links so I want to make sure you get yours   and then of course the sheet 3 is gonna be where  we're gonna put our sales I want to track all the   sales that we've done previously that's important  as we create this so XI 3 is gonna handle this so   again this is gonna be our main application where  we're going to in turn print receipts items here   and then sales here so let's get started as always  what I like to do is I like to save the first two   columns for admin and we hide those columns so  let's color those a little bit differently and   while we go into the home I'm just gonna call it  those gray I always want to hide those columns and   keep those for admin so that's a great way to do  that next up I want to give it a name let's just   call it something like point of sale and that  way we'll we can color the columns let's say   all the way through oh and then we're gonna save  some room for receipt so we're gonna format these   cells give it a fade so maybe a fill fate I'll use  something a little bit different than I usually do   if you're familiar with this bed it will just take  a minute and watch some times repetition helps so   even seeing it over and over again helps and then  I'm gonna code the one below a little bit less   of a fade and then we'll give it a fill effects a  little bit lighter of the turquoise color so we'll   go to here and then I want that fading into the  background the main background color so let's call   that I'm also going to clear out some of those  rows with some for the receipt so not everything's   going to be colors on with the background or the  receipt and the background is let's say K through   N K through and I want this to be our receipt so  I'm gonna actually make that white and so color   that white because I want our receipt to print  out and then we can extend this con so basically   the idea is to have our receipt here and our form  here and then this column will just be for space   just so that you can extend this off a little bit  so that way your screen is all the same color so   we'll give it a font a larger font and I'll merge  and center it maybe to J and so we have a little   bit of a larger font and we'll increase the font  maybe to about 32 or something like that it's   something nice and big oh that's too much okay all  right that should be good let's go with 32 I like   that a little bit better and we can then add some  icons in here and add some shapes what I want to   do also is I want to add an item name let's say  in three item item name I want to put that in   there cuz that's important and then I'll put the  description down here and then I want to picture   also so I won't put in that picture so we also  have I also want to know the subtotal of that so   let's put that sub total here cuz I want to keep  track of it and then the tax I want to put that   there total I want to keep that pay type I want  to know what type they're paying with and payment   and then change also I want to keep track of the  change as well okay actually I want them all in   the same column so just copy those over and paste  them here I want them all in the same column then   I'm I'm going to then have a space for each one of  these so we'll block those out and then keep that   in mind all right so that's basically what I want  I want to keep track of that so all right I'll   put a border around that I'm gonna format those  cells and put a border let's say the same darker   turquoise color and we'll use a border inside and  out border and I'll use this dotted line for the   middle okay so we've got that we got a total and  I'll write justify this so that we can see that   and then I want to add codes I want consistency so  let's add colons on these here pay type and then   payment we want to keep track of the payment and  then we want to keep track of the change really   there's only the pay type and the payment are  going to be user entered the rest are gonna be   calculated so let's put these in the background  as white okay I want that probably put the item   description right here so let's put a box around  that format the cells again we're gonna use the   same border here same color consistency all the  way around and then I'll put a dotted line here at   horizontally because I want that description to go  let's put it solid light actually and I'll put the   dotted line here so we want the item name I want  to give it a little bit larger space for that so   I'm gonna merge and center it and left justify it  I want the item name here in the item description   here so I'm gonna merge the center that too and  let's give it a little bit more space for that and   right justified okay so we've got the item name  we're gonna put the calculated information here   and then I also want the course the price of the  information let's go put that in the sick price I   want to show the fresh ball I'll be able to change  that price as well so we're gonna put that as   white and I also want the quantity we also want to  change the quantity okay key here so both of these   are gonna have the same formats both of those are  gonna be editable color those white there and we   also want to make sure that they're both right  justified and then in this case we can keep those   let's say right justified as well all right so  we're gonna format those cells pretty much using   the same borders that we have the same border  colors that we do throughout the application   and we'll use the same border and then I want the  dotted line in the middle vertically okay so it's   starting to come together now let's save our work  and I also want some to scan the item of course   we're gonna be scanning atom that is important so  we're gonna put that here let's just put scan item   here cuz I want to put a little bit large in case  it's a in case it's a larger number and then left   justified that's put in a colon keep it consistent  see we like consistent here and then right justify   that and do the same thing here so let's make this  white and again put the same borders around that   formatting the cells with the border I thanks for  your patience on these while I format these but   you seem to really likely to watch me create these  so based on your views I'm gonna try to do a lot   more than that alright so we have our basic I'm  gonna put the picture here I want that a little   small picture here and I'm gonna put a button set  here and then I want to put a button set here as   well the receipt information will go here and  this will be just a little spacer alright let's   add some buttons in here so I'm gonna insert some  pictures here and see what we got I've got some   saved already so I'm just gonna insert those  they're gonna be bigger siliceous bring those   down to points there's something manageable so  that we can see those and then we'll size them   ingly those are the ones they're gonna go  on the button so we'll have to bring those to   the top but we have a this is an icon here that  I'm going to use for the entire point-of-sale   application so I'm gonna drag this up here make  it a little bit bigger so it's obvious and then   I'm going to added more of a theme color which  would be this so get a little bit more theme   color we can also add a little bit of a shadow  on that so we can click pictures and just add a   little bit of a shadow so it kind of looks nice  make this bold and we can also add a little bit   darker color on to this okay so now it's kind of  a nice we get to see that so we have the item name   but let's add in some buttons here because I want  to make sure that we have the button so the user   can actually use a touchscreen which is kind of  a nice so we're gonna insert shape or just can   use a square shape in here okay let's give it a  size try a little bit bigger point six and maybe   a width of point seven okay so that's kind of  a nice size for that we have a nice big button   there so that's going to work and I want to give  it something closer to the theme maybe this okay   that looks that looks nice you want to actually  give it a font larger font so I want to put it   make this button number one and then I want to  Center that of course and then we'll duplicate   it but it's obviously it's gonna be a lot bigger  than that so how big should it be probably about   let's see thirty-two maybe let's click the button  and click 32 here okay that's given a nice button   there we go that looks good nice and big and then  we're ready to duplicate that let's duplicate it   control D and then oh this is gonna be for two  and then we can duplicate that again and we'll   click on there duplicate that this will be for  our number three nice and then we want also of   course number four so we'll duplicate that control  D because I want one for each one and let me just   go ahead and do these I'll pause the video while  I create each one you get the point so I'm gonna   make this a little quicker okay now I've created  all twelve buttons here so let's now that I've   created all twelve buds I'm gonna name them and  very important to name them at least yous gonna   be four letters at least minimum four letters and  a number so if I were to if I were to add let's   say I wanted to do but1 that wouldn't work why  wouldn't that work because that's a cell so if   I hit enter it's gonna go right to a cell see that  but1 so we want to make sure that we have at least   four letter so let's do that b ut n one button  one okay so that's gonna work just fine and I'm   gonna do the same thing with each of the buttons  there's no need to watch me name each one of them   so I'm gonna pause this video and just name them  all button button - so hold on a second while I do   that okay I've named all the buds at least one  through nine this is gonna be zero this is not   gonna be zero I want this to be clear I want to  be able to clear bug so let's type that in clear   and then we're gonna have to obviously reduce  the font size on this one so let's go ahead and   reduce it down to where it'll fit in the cell  here let's drop this down here and reduce that   down scroll up probably okay that one looks good  we can go a little bit higher and then I'm gonna   underline it control you alright great so we've  got that we've got our basic format buttons there   now so let's line them up all we need to do is use  our selection here and then I'm gonna line them up   like this format and then we're gonna align to  the top and then I'm gonna probably bring them   a little bit closer in now I've got a lot of  to cover here so I want to bring them closer in   here and then also importantly we're gonna group  everything together once we're set up so there we   go that's look a little bit tighter here so we can  I want a lot of space I want to add buttons here   too so we're gonna keep that a little bit closer  together okay good so now we've got everything   nice and close together and I'm gonna line the  rest of these up and there's no need for you to   watch me do that because it's kind of boring so  I'll hold on a second I'll pause it while I line   everything up nicely and then we'll create some  more buttons alright now that I've got everything   lined up I want to got a few more things you  don't want to name this button obviously clear   this one's gonna be different now the different  purpose clear button and I also want to add this   one it's gonna be a little bit different it's  gonna be called decimal button cuz it's gonna   have a little bit different macro alright okay  excellent we've got a few more buttons to create   I want to create some different payment types so  let's do that let's create some buttons I'm gonna   just duplicate one of these buttons using ctrl D  and I'm gonna shrink this down the width because   I want to fit more in here okay maybe 0.5 because  I want to put three buttons in here the same size   and there's gonna be no text on this what I'm  gonna do is I want to put a payment to type in   here so I'm gonna do I want actually three payment  types I'm gonna duplicate this three times total   of three buttons and move them over here because  I want all three payment types and then all right   we can line those up and distribute them just by  selecting them control and then formatting make   sure we align the top here and then we want  to distribute them horizontally that looks   nice all right and then I also want to make sure  the bottoms are the same as these I want to make   sure those are all consistent so let's line up the  bottoms or the tops either one would be fine okay   so we've got those now let's add some payment  text and the first one I'm gonna do is cast so   I'm gonna put that icon right here now let's bring  all let's bring all those to the top first so what   we're gonna do is we're gonna select on all of  them because we created the buttons after spring   forward bring to the front okay so now they'll be  on top of the buttons we're just kind of handy so   cash we have cash we have a credit card here I  want to bring my credit card as a payment option   and then the check option also so we've got those  three options here we can line those up nicely so   that everything's group properly format and align  the middle right here okay so everything's lined   up nicely and distributed align I also want to  distribute them horizontally so everything's   centered and we can group those very soon but I  want to create a payment button also on this so   let's also duplicate this one I want a payment  button and that payment button is going to be   larger but I want the same height as this so  let's align the top this is gonna this is how   you create very buttons put really quickly and  easily okay and what do we want to give the name   I want to call this payment but we're gonna have  to reduce the font so payment and then we're gonna   reduce the font so that it fits and I want right  justified because I'm gonna add an icon on to that   so let's click the button itself and then lower  the font here and then I want to increase the   font obviously it's gonna be a larger button and  I'll give this the width maybe around one point   that should be fine width that should be okay  as it is and then we'll move that over here and   then I'll line the left here because I want to  make sure everything is lined up align left and   then click on here and then we're gonna align the  right to this button that way everything lines up   betterment create three more two more buttons  aligned to the right okay good so now we've   got everything centered and let's add an icon for  this so we can put in an icon there can probably   increase this font we created that button after  salutes again moved to the top bring to the front   all right so let's that's it looks like a good  I'm going to create two more buttons ctrl D and   then I want to bring these two also because we're  gonna add an icon to bring those to the front as   well alright and this one I'm gonna be next NEX T  because I want to go to the next record so we'll   use that and I also want to print record we'll  increase the font here just temporarily bring   this all the way down here and then I want this  to be our print button so we'll capitalize and   then we're gonna increase the fonts on both of  these buttons so holding down the shift I want   to increase those just pretty much larger that's  pretty good something nice and large okay good   now we're getting said we got our button set and  what we want to do is we want to group these start   grouping these let'em so I'm going to hold down  the control and group those and then I'm gonna   do the same thing here and I'm gonna group those  this is our shortcut for grouping and I'm gonna   move this one a little bit to the left and group  that because I want those always grouped I also   want to group these button sets so I'm gonna do my  selection tool and I'm gonna highlight everything   because I don't want it and I'm gonna group it and  importantly right after we grew up what we want   to go to the properties and we want to make sure  that we don't size it we just spent a lot of time   making sure everything set up but we want to click  move but don't size ourselves in fact that's gonna   go through all of our buttons once we get sick  because when we change these columns I don't want   these buttons sets to move but I want to make  sure that everything is set up right so we're   gonna bring down and make sure those bottoms are  lined up align the bottom okay that looks pretty   good and we can bring the font so now we've got  our next button we can group that and we've got   our print button here will also want to line that  up okay good that we can bring this up a little   bit more looking good now we've got our POS coming  together very very well I'm gonna group all of the   buttons line them up in the middle so everything  looks great and group that and make sure we also   group this one line them up everything's going  to get lined up and then group it and make sure   of course after we group everything everything's  not grouped we also want to make sure that we are   moving but not sizing that's very very important  so we're going to properties move but don't sighs   okay so we've got this named and set up our point  of sales coming together we're gonna put our   receipt information here and looking very good we  can align those alright let's get into the naming   right now so we can name everything properly  so let's put this as the next button and heat   n EXT button and this will be our print button  everything should be pretty clear make sure you   always name it so you know it that way you can see  it on the shapes print button and there's specific   these are gonna have specific names and I'm gonna  show you why because it's gonna be a lot easier   to create macro so I can use the same macro for  each one if I use specific names not necessarily   the group but the individual shapes so let's take  a look at this I'm gonna name this let's go into   the selection pane it's much easier to work from  there and you see the selected what I'm gonna call   this is just cash and I'm also gonna sign the  same name generally we don't but in this case   is gonna be very specific cast I'm gonna sign  the same name to that and also I'm gonna do the   same thing with credit card I'm just gonna call  this card and I'm gonna call this card now we're   gonna use the same name because we're gonna turn  this name automatically into the payment type   and we're gonna do the same thing for check check  just so both the picture the icon and the button   are going to have the same name okay great so  we've got payment we've got this everything's   perfect and let's name the payment button and  then we're done that we can start writing some   code but and I also want to payment payment button  okay so we got everything name we're gonna put our   picture here we're gonna put our subtotal here  and put our receipt let's start working on this   receipt I want this gonna be Merchant Center and  I'll call this sales receipt and then let's make   that a little bit bold and bigger of course that  would be good and then I'm gonna put down a also   want to merge and center this and the next  few lines as well because we're gonna put   in some information there that we want merge and  center those and I want to put probably the name   of the store let's call it Fred or rosary's  my favorite name groceries and I also want   to put the address one two three four five Main  Street USA and then probably a phone number here all right so next up I also want to know the  receipt number here so I want to put that in here receipt number is very important because we're  gonna keep track of that and then the next up   I want to know the date also the date of the  receipt and the cashier and we'll just put in   Frieda Frederick's that's Fred's sister Frieda  Frederick a and then the date I'm gonna abuse   a long date here also on emergence enter these  holding down the control merge and center merge   and center and then left justify it and then these  are going to be right justified so we can see our   receipt come to life here great so what's next  well I'm gonna skip a line and then we want to   put the information so here I want the item here  the quantity the price and the total great and I   want to Center those and then put a dotted line  above that and below it so we're gonna form out   those cells format and then I'll put like  a dotted line something like this above and   below so it gets kind of a look of receipt that  looks nice and then our items will go here so   Apple here would go here the quantity one and then  the price would be let's just say $2.99 and then   the total be probably equal to this times this so  just to get an idea of where we're going there or   that okay so let's focus on the formatting now I  want to format this entire one probably as a none   because we want to use generally could be 1.5  and these are gonna be price columns so let's   just format all those down there as a currency or  format price okay and this one looks good the way   it is I like I like the way it's looking now and  so all of our items are gonna go here and then   I'm gonna want probably things like subtotal and  total down here so I would want to put something   like at least temporarily like total here tax  your total would be here paid amount I want to   know the amount they paid and the change so  those are all important I want to know all   of those that information is gonna be below the  sheet now it's gonna be dynamic right so we're   gonna put this in dynamically because we don't  know when it's gonna end we don't know how many   rows so that would look good I want that there  and then also want something like thank you for   your receipt but it's gonna be dynamic so I'm  gonna say something like thank you come again   and then a barcode here let's put those in and  using text because I want them standard around   there so many she click shape and insert insert  a text box here I'm gonna make it about the same   size as the width of this receipt and then I want  to put in a message so something like thank you   come again something like a message and then we'll  Center that no borders I don't want any borders   around that so just wanted it no outline on that  so we'll just click no outline make that bold so   we can see what it would look like and okay good I  like that and I also want a barcode font here and   it's gonna be based on this receipt number so if  I enter receipt number one zero zero zero I want a   barcode based on that so let's enter another text  box in fact let's just duplicate this one using   ctrl D but I'm gonna change the font and this one  it's going to be equal to whatever is going to be   that receipt number here so equal that receipt  number so that's what I want good but now I want   to change the font I want a barcode font on that  let's try code 128 obviously that's too small so   let's increase it a considerable amount and then  increase this all right I like the way that looks   there okay so we have thank you and we have a nice  barcode font here that's a good footer message and   let's put that lower here and then we can raise  it up a little bit nice so we've got those two   and let's make them on top of each other and we're  gonna Center those format align and I'm gonna put   line the middle and I'm gonna group those because  I'm gonna use those basically but they're gonna   be displayed dynamically because we don't know how  big this receipts gonna be so let's group them and   always when we group format the cells right format  the shape in this case and go to properties and   move but don't sighs we don't want that moving we  want ed say we mean we may size it ingly a   little bit anyways ingly just to make sure  and I'm gonna give this a specific name calling   it footer group okay so now we've got a footer  group and now we got this is going to be dynamic   of course and I want to put some conditional  formatting in here as well all right so our   receipts starting to come together I like the way  it looks so far we've got a lot more work to do   what do we want to focus on some information based  on the items what items we've selected I also want   to know when I select an item I want to know what  I've selected so let's add in some additional   information here I want to know when the items  gonna load what I want to do is I want to select   an item and I want that item information to appear  here I want the item name the item description   the price and the quantity to appear here when we  select it when we enter a scan an item I want the   number that we enter just to automatically enter  here and then automatically add it down here and   keep adding and adding and adding so but I want  to be able to edit existing items as well so we   need to know when the item is loading meaning  the information is coming from here and going   into here so that's important as well I also want  to keep track of the totals here so we need yeah   so I want the item load I also want the item row I  need to know what row the item and that's based on   the row here then the database I also want to know  the receipt row or seat row that's the road that   we selected here and I also one more thing I want  to know the next receipt number see team number I   don't want to know that because that's important  I want to understand which is the next one so we   can load up so they're all unique the numbers and  then we'll highlight those give those a distinct   look and feel so that we can see the difference  okay good so we're set with that that's all we're   gonna need as far as that and let's go ahead and  name some information create some named ranges now   for the items I want to create a dynamic named  range for the item ID because we're gonna need   to locate that so that's very important so  let's go into the formulas name manager and   create a new actually let's highlight this let's  make sure we include the header name manager and   then new what I want to do is create an item ID  but it's gonna be dynamic so we're gonna have to   add offset in here let's copy this we're gonna  use it in a moment and then we're gonna do is   I'm gonna type in offset and I want to include  the header but only because if there's no data   I want to make sure that doesn't create an error  so we're gonna include a two but then we're gonna   move one column down and then comma comma count a  I want to know all the text in that in that whole   column of course I want to increase in more than  twelve so we're gonna go let's in nine nine nine   nine just in case we have a lot of data and of  course since we're counting the header we need   to subtract one minus one comma one which means  one column let's tab out and tap back in to make   sure that the dancing ants cover our data which  it does it's perfect so I'm gonna copy that entire   named range in control C and I'm going to click OK  I also want another one for a item name so let's   click new click item name I want to named range  there and then I'm gonna copy this and I'm gonna   paste it now all I need to do is change the column  so I can change a from A to B and then we're set   so I'm gonna put this be just in three different  places B and then B again tap out tap back in make   sure that it's covered okay we're good to go and  I want one more new item description I'm gonna   follow the same procedure in fact we're just gonna  paste it in there and change a to C that's gonna   quickly be able to create three different named  ranges using offset tap out tab in make sure that   it covers our data and click OK alright now I've  quickly created three different I also want to   create a named range for our receipt numbers in  our sales our sales page is gonna have this just   call it sales history whatever we want that's fine  and inside that sales history I'm gonna put some   information I want to know the receipt number so  that's important I also want to know the date that   the receivers made the cashier who rung it up and  then of course the item the quantity price and the   total so I'm gonna want all that information here  it's gonna be just a basic table so we're going to   merge and send to that here and then just give it  a basic color it's not so important the formatting   of this one okay so but what do I want I want to  know all the receipt numbers so I want to make   sure that I can have a dynamic named range for the  receipt number so let's do that let's create one   more dynamic name and formulas name manager new  we'll just call this receipt number and of course   this is gonna also use the offset so again we're  gonna say I'll set using the sales a two through a   and obviously you can much larger number so we're  gonna copy that back that up comma one means one   column we don't want to start at the header comma  comma County I want to count all of them again   paste it in but increase that made a larger case  we have a lot and then again minus one because we   need to exclude the header row me including the  header so there's no air when there's no data   combo 1 and then close parenthesis tap out tap  back in make sure that it covers good so it does   all right so we've got we've got our named ranges  now covered so now we can add in a little bit of a   formula so we understand we see our item numbers 1  0 0 1 1 0 2 ok good let's focus on some of these I   want to know the next receipt number so how do we  find that what we just created an extra seat let's   see if we have any sales we have 1 & 2 but I are  just going to be probably like 1 0 0 so let's put   in some larger ones I want because I want a big  barcode font so 2 1 0 0 and then let's just say   1 0 0 1 so our next I want to know what the next  one would be so we can do that using if air and   Max equals if air if in the air because if it's  an error if there's no data we also need to set   in initial receipt number so we're gonna include  the max and the Max's of the receipt number what   is the maximum of that plus one that's gonna be  our next increment plus one but what if there's   no data if there's an if there's no data at all I  want to start it off and let's just say 1000 okay   let's take a look perfect one zero zero two now  as soon as I delete the data as soon as I delete   these two it's gonna get should go to 1,000 so  let's go back and check perfect that's what I   want 1000 1000 here so we know our next receipt  number I want to know the selected rows the user   makes a selection change I want to know the row  and I want to highlight that row and I'm gonna   make sure there's data so for example if they  click row 10 I want to highlight that I want to   know which row the user has selected so we can  add in some conditional formatting let's click   on the first one and then go way down here just  to there and then what I'm gonna do is add some   conditional formatting so go back into the home  conditional formatting manage rule so I'm gonna   create a brand new rule and use a formula what  is that firming and based on based on of course   B 10 so it's gonna be equals B 6 equals row open  and close parenthesis now I want to give that a   specific format based on our theme so let's go  into the fill fill effects and I'm gonna choose   this darker color here but I want to make sure to  give it a font so I want to contrasting so we're   gonna make it bold and make it white it's gonna  give it a contrasting so that we know what they've   selected apply that make sure the form applies  to click okay all right so when they click here   I want that to be hot and I also want to load the  information that's what's gonna happen here and I   want the item name here to appear here and I want  the item description to appear here all right so   we've got the item name how do we know I want to  know the item based I want to know the item wrote   based on the scanned item information so if they  entry 1 0 0 1 which of course is our first item   Apple I want to make sure and that starts on of  course I want to know Row 3 when they enter 1 0   0 1 I want to know it's Row 3 we have named ranges  but they start at Row 3 so we're gonna need to add   2 let me show you what that like so equals if  they're always wondering if they're cuz I don't   want to make sure we're gonna match is what are  we matching I'm gonna match look up this value one   zero zero one and I'm gonna match it based on the  item ID and I want an exact match so that's going   to be zero in case there's an error I just want  MP to show up but I don't want one right one's   gonna show us our first row I really want the  row so we always have to add two because I want   to know the road not that it's the first item so  adding two is gonna do that so now we get three   four one zero zero one and if we change this to  1 0 0 2 we are going to get 4 which is exactly   what I want alright so we've got that this is  going to be a true or false here because what   I want to know when the item loads so now that  we've got in that's all we need for that I want   to know that item description we can use that  based of course on this name so we can use an   index for that equals index what are we indexing  I'm gonna index the item description which we have   here what are we gonna base it on base it on the  row we know the row is gonna be here we can also   base it on the name the match let's match the  name so that way no matter what we select we   can no match we're gonna look up the name and  we're gonna base it on the item name here item   name and then we want an exact match so that's  gonna get us the rule the row in this case we   don't have to add because we're indexing the same  item column comma 1 is a column and let's just   take a look at that ok granny smith apples now  that creates perfect it looks just right that's   just the way I want it we've got apple and if we  change this to strawberry handpicks from ok great   so we've got the description for strawberries  and based on the name that's what I want why   do I want that because whether they enter this or  whether they select it I want the description to   show up here perfect and the price is going to be  dynamic based on whether they enter here from the   database or based on whether they select here the  price so there's going to be two different prices   the price if they enter here the price is going  to come from right here but if they select on   existing item the price is going to come from here  and that VBA will handle that let's focus on the   subtotal now because we to make sure we get the  totals right in the taxes alright so that's gonna   be helpful the subtotal is basically going to be  a formula based on all what's in this column but   I can't just total what's in this column because  I also have the subtotal of tax so I have to use   the sum if it's got to be based on something some  if there's a value in column K then we can sum it   so let's use some if for that equals some if and  what is it we're gonna sum if and it's gonna be   based on this sum if K we'll use a large number  I want to make sure that that contains a value   before we sum so what is that sum then we're gonna  use quotes does not equal course so that's gonna   mean as long as it's not blank what that means and  what's the sum range I'm gonna sum this right here   okay great so I'm gonna add again that to a large  number 999 cuz in case we have a lot of numbers so   perfect so that means if I add figure here and I  add a some kind of a description here again Apple   it's gonna add in but if I add something here it's  not going to add in right because there's nothing   here so that's exactly what I want adding only in  the numbers if they're values if there's a value   in column cave perfect and the tax is going to be  equal to let's just say this times point 10 let's   say 10% tax and then we can also format these  let's format these dis and disks actually not   the payment but that's okay format this okay and  the payments gonna have to be text because we're   gonna be using that based on a entry so I'll show  you what reason for that is in a moment okay so we   got pay type that's gonna be based on this total  it's going to be equals the sum obviously of the   subtotal and the tax perfect okay great so we've  got that all right that looks good that's just the   way I want it however this information is gonna  be dynamic right I don't want it to be here it   could be here it could be here we don't know where  this information it's gonna be dynamic based on   the last row so one of you is I'm going to copy  that I'm gonna place it somewhere other than   really where I want and then we use VBA to place  it back in so I'm gonna place it right here and   then what I'm going to do is I'm going to based  on the total number of items I'm gonna place it   dynamic in the road below this is also going to be  placed dynamically too so we can place it here so   here basically what all I need to do is say equals  the subtotal equals the total tax and then equals   write the total and then we're just gonna paste  the values is there and the paid amount against   again equals also in fact this one I'm gonna do  through VBA but I'll show you that in right and   then equals tochange okay great then all I need  to do is copy and paste this based on the last   row all right so what's next so next up what I  want to do is I want to start with the VBA we're   almost ready everything's looking really good  we have our screen all the way updated I really   appreciate your patience on this I'll put the  picture in here dynamic let's get into the VBA and   start adding information here so we can get this  working and get you a POS that's going to work   all right into the VBA developers Visual Basic as  you know there's no code let's add a module here   so we're gonna insert a module and we'll just  call this POS macros using the properties and   we'll just call it P Oh POS macros any name we'll  do and the first thing what I really want to do   is what a user makes change to e 10 I want to add  the information all in here and I want to add it   in the first available line here so I want to add  it in make it really easy so let's create a macro   and call this add item so we can get rid of this  and we'll go sub add item all right and I also   want to know the item row that's really important  so we're gonna dimension the item row as long and   I also need to know the available row which is the  first available row so available grow as long as   well okay so the first thing I want to make sure  is that b5 contains a value right we cannot add an   item if we don't know the item row that's really  important right it can't add the details I can't   add the price of quantity and the name if I don't  know the row so if this is a bad information if   it's four five five right there's no item this is  gonna be blank so we need to make sure that item   contains an actual row so let's go ahead and make  sure that we put in the check in VB that's the   first thing we want to do you can reduce this  a little bit so we can see both screens while   we have them up okay so first thing with sheet  one that's what we're focused on if dot range b5   equals empty then exits all right nothing we can  do okay so nothing we can do if there's it's empty   so we can no next up I in case there's any picture  here any previous picture I want to make sure to   delete that so we need to write some code so we  can delete any picture as we're loading new items   if that picture doesn't exist it's gonna create  an error so we want to wrap it an on-air resume   next dot shapes item NIC dot delete right we want  to delete that and then on air I use autohotkey to   automatically type it's a free software autohotkey  that's what you'll see me typing really fast it's   automated okay so next up we're ready to set the  item row we know the item row that's in b5 don't   we right so we can copy this just write equals b5  item bro next up I need to know the available row   where are we gonna put it I need to know the first  available row in this case it's going to be 12 so   we can use that using K and Excel up plus one so  we can write that in the code so the available row   is equal to dot range and then we'll just use K  let's say nine nine nine dot and Excel up dot row   that's gonna give us our first available bro okay  good so now that we have our first available bro   we we can set the receipt row I need to know the  receipt row what is the row in b6 I want to put   that in b6 I want to know what row remember we  added conditional formatting for that right so   if this changes to 11 I want to know what row  that we just added so we keep that in mind I   want to know the wrong put that in b6 so dot  range b6 dot value equals what is it equal it   equals of course the variable we just said sheet  2 D and the item price available row set receipt row great so what do I need to know next next up  I need to know I want to put in the item price and   the item name so let's start with the item name  dot range III I want to add in III III is right   here I want to put in the item name right here  that's gonna automate the description because   it's based on the firm so first thing really just  put in the name based on where it is what is it   it's gonna be whatever is in B and the item bro so  that's pretty easy so let's add that in right now   so got range e let's put that correctly t3 dot  value equals right sheet 2 is where our items   we'll look at a cheat to range B and the item Rome  and the item row dot value okay that's the item   name I also want to end the item price so the  item price is going to go in f6 f6 let's take   a look at that right here I want to put that right  there so that's where the item Row is going to be   taken care of so we can add that in right now dot  range f6 what is that going to be equal to he's   gonna be people to almost the same but of course a  different row equals sheet two and of course this   is going to be D the D price is located in column  D descriptions located in C item price but we   don't need the descriptions automated through our  formula so we don't need to add that and that's   automated I also want to default the item quantity  to one I want to default the quantity to one so   we can set that up dot range in this case it would  be F 8 f8 one setting the default you can set your   standing but I think we want to be fine default  item quantity to 1 all right so now we've got   our default item quantity now we're ready to add  let's just put an add item detail to the receipt   detail to receipt okay great so we're ready dad  so basically what I want to do I'm going to add   the name of the quantity the price and the total  through a formula I'm gonna put in a formula here   so we can do that with the following lines of code  dot range okay of course that's gonna be the item   name K and what K and the an available row and the  available row dot value equals what it's going to   be equalled III dot range III cuz we've already  added the name in e3 because that's gonna be our   item name good to comment everything out what's  next we can copy and paste this and make it a   little bit quicker here next up is the quantity  right so that's located in L that's column L we   need to equals here and we need the equals here  okay so we've got the equals and what where   is our quantity located that is located in f8 so  we're gonna change this to f8 and then put in item   quantity okay so good so now we have that what  are we at what else do we want I want the item   price we got to have the price in there so that's  going to be in M so we can copy that and change   this to M M is gonna be where price is located and  it's coming from f6 right our price is f6 which   is right here we want our price f6 and it's going  to go right here in a column so we can do that so   we're gonna call that item price next up what  do we want well now I want the actual formula   and that's gonna be located in column and n is  gonna be where formula is so we're gonna paste   that in change this right we're going to actually  gonna be quotes keep quotes equals L n quotes and   the available row another and sign and then quote  and then I want to use the time she goes multiple   times which is the asterisk and again and in  this case it's going to be M right we want to   know the price and am in fact we can compete  like those together am and the available row okay so we have L times M right L available  row times n again let's just do that here L   and the available row times m and the available  real perfect that's exactly the formula that I   want to enter so we have that there alright  now that we have the formula let's just put   in total price formula all right so what's the  next step next step I want to check to have the   pictures right we want to add some pictures in  there once we select I don't want that picture   to show up as soon as I select it I want to show  the picture that's whatever that picture is based   on this image here we've got the image here so I'd  like to have a nice picture show up so how do we   do that let's write just a little bit more code  first of all we want to make sure that actually   there we don't have any issues with the filename  in case it's not found I just don't want to load   the picture I don't want it to create in here  so we just have to run a check let's just use   on-air resume next but I'm gonna I'm gonna comment  that out in case there's an air on air resume next   but we're commenting it out we're gonna add it in  later so it's just in case cuz it it's not good in   case there's an error I need to know I know that  these are correct so I don't want any errors to   show up but if there are if I make a mistake in  the code I want I want the air to show up now so   keep that in mind if directory we want to check  for that final path what is the file path sheet 2   right dot range what is the range it's going to be  II and what II and the item row and the item row   that is the file path of the picture dot value  VB directory does not equal empty then right if   it's not empty means the file has been found then  we can continue so good so we have that and if so   now we can add the rest of the code in if there's  a correct picture so what is that well first of   all with right we're focused on the she with dot  pictures pictures dot insert what are we inserting   of course we're inserting this right here this  file name right here gonna insert this that's   what I want to insert so let's copy that paste  that in there not the comma and so then I what   I want to do is once it's insert that will insert  it so what I want to I want to focus on that with   dot shape range because of that shape range I want  to do a few things with it what do I want to do   well the first thing I want to lock the aspect  ratio lock aspect ratio equals NSO true when a   lot I want to make sure that that aspect ratio is  locked I want to set a height for that and then   equals let's just say around 45 maybe something  small like that 45 and then give it a name on   a specific name name equals item pick that way  it's always the same name we could easily delete   it and then end with and then end with perfect  so that's the way we have so now we we have our   shape we've set the information but now I want to  work with it what I want to do with dot shapes I   want to this which shape the one we just add an  item pick with that I want to do something with   it I want to place it in a specific cell and I'm  gonna and I want to make it visible so dot left   what do I want to place it equals sheet one why we  need sheet one now because we're on with dot with   shape so we need to specify sheet one range what  I want to place it on a place that let's take a   look where we want to place that back in here and  we want to place it right about d6 let's put that   d6 inside the code range d6 . left and then the  same thing with dot top top of course it's also   gonna be d6 but top so let's copy that paste it  down here and put the top and I want to make a   visible adopt visible equals and there so true  okay that looks good so now we've got end with   there and now we want the end if so let's write  remember this is the end if that's based on this   is a directory it's a clear link so we have that  perfect and I also want to do a few more things   let's go on air zero right in case there's that  way if there was an issue with the link and let's   comment that out for now but we'll add that back  in a little bit later on just in case there's an   air with the picture so also what I want to do is  I want to clear the item ID I want to get ready   once they've entered an item I want to clear this  out I want to get ready for the next item and I   want to put it in that way they can just scan the  item in and it's automatically ready for the next   item so let's do that let's clear it out top range  e 10 through f10 remember it's a merge cells so we   need to clear up both of the cells or all of the  cells in that dot clear contents and then clear   put in a note clear item ID and I want to make  sure it's ready for the next one so let's select   that dot range eat and remember we only need one  cell in a merged cell there remember we only need   one cell and a merge so when we're selecting it  so I'm gonna just select that and get it ready   for the next either alright so we're done with  that let's run this code and see if we have any   issues let's make sure we have a specific ID and  let's run this code to save it before we run code   in case we make a catastrophic mistake then we can  always just close it out so we're gonna run this   and it looks nice let's take a look at this apple  perfect it put in the price it's got the price   the quantity and it's cleared it out and to put  the picture and just as we wanted okay great so   now we've added the item in and let's just put in  now what I want to do is I want to make this run   automatically when the user makes a change to e  10 so let's add in that code right now so when the   user makes a change we want it that's gonna be a  change event based on our POS sheet and it's gonna   be a worksheet change so worksheet not selection  change but change will add some code in selection   change shortly so I want that change to happen  when we make a selection what kind of selection   do we want I want to write on on change of item if  the row is found if the row is found and add item   to found and add to receipt so what do we do so if  not intersection which one e 10 e 10 if the user   makes a change to e 10 and I want to make sure eat  n is not empty and range e 10 I want to make sure   that in case they cleared out that kind of change  I don't want dot value does not equal empty then   what then I want to run the macro add item well  if if you're not sure what the macro name is use   it in all lower case and as soon as it changed to  upper case you we know that it's the right watch   did it change let's bring this out a little bit so  you see how it changed let me show you that again   I put it in all lower case add items but I'm not  sure if I got the item name right so as soon as   I hit enter it moves to all caps and that means  you know you've got the macro right so add items   so that means as soon as we change it it's gonna  add an item so let's clear that out and now let's   give it a try so as soon as I add an item one zero  zero two it should work perfect banana okay great   but you know what I messed up one thing notice  how I replaced my I wanted all the available   role must be plus one so let's go back there go  back to PS and because that replace the same row   so look at our available row here available row  not the last row the first available row plus   one that's important okay we got that fixed up now  one zero zero one at an one zero zero two perfect   so it seems how it's selecting and now what I  want to do is I wanna select a specific item   I want a few things to happen I want to make sure  that that information loads up here when I select   it so let's get that that's on selection change so  why don't we add that code in right now and that's   gonna be called load n so our add item code is  working just good just the way I want it and let's   continue on with another macro called load item  and what do we want that to happen when we make   a selection of a specific receipt I want to load  that item up because this one's working just fine   now so how we gonna do that well that's based  on the selection change so let's go back into   the worksheet here the worksheet code and focus  on our selection change so what are we gonna do   let's write some let's write a comment here on  selection of receipt item load item details if   not what are we going to focus on K through n  right K through and that's our receipt so let's   look at that K and our first rows k10 through and  let's just large number nine nine nine I also want   to make sure that K actually contains a value  otherwise you can't load anything and range K   and the target row target row dot value does not  equal empty in that case I want to load then then   do something what I want to do then I want to  load all the information in so the first thing   I want to do in b6 I want to put the row remember  our row must go in b6 that's that highlighted row   right here so I want to put that in b6 so let's  do that that's what the first thing we'll do range   remember we don't need dot range because we're on  the sheet itself range b6 dot value equals target   row that's the selected row okay so we've got  that next up now that we have that we're going   to set the item load to true I want to make s