Collaborate on Billing in Excel for Purchasing with Ease Using airSlate SignNow

Billing in excel for Purchasing

hello and welcome this is Randy with Excel for  freelancers and in this week we're gonna create   an amazing automated purchase order application  in which we're gonna be able to generate parts   needed automatically per vendor it's gonna be  an amazing training we're gonna do it all from   scratch every single formula every format every  line of code is gonna be written right here so   let's get started all right thanks so much for  joining us today I've got an amazing training   where I'm going to be creating an excel automated  purchase order right in front of your eyes in fact   we are gonna do everything as I type so you're  gonna watch me created not only am I gonna create   a purchase order but I'm gonna show you how you  can generate a brand new purchase order with all   the items required based on a minimum quantity so  the purchase orders can be generated automatically   we've got a really lot to cover really cool  training today very unique so I can't wait to   get started before I do that I want to make  sure that you're getting these trainings to   you each and every week I bring you on YouTube so  make sure you do subscribe with the button below   and always click the notification icon belt I'll  be sure that you get those trainings said to you   right away as soon as they're released each and  every Tuesday and if you do like these videos and   you like purchase orders I'm gonna be creating a  comprehensive purchase order with tons and tons of   features in our excel for freelancers mentorship  program where we're going to be creating an   amazing application and I'm going to show you how  you can create your own applications we're gonna   go from concept to cash and everything in between  in this program so if you like to use your Excel   skills and create reoccurring and passive income  this is the program to do it we're going to show   you how to define design develop and deploy your  own Excel application so it's going to be a great   training if you like that have a look I'll include  the links down below or just my Excel mentor com   it's going to be an amazing program so get in  now it's really great I think you'll love the   program also if you do like these applications  they're always free you can download them using   the links in the description below make sure you  do especially on these live trainings every once   in a while there'll be something small or some  small issue that I might forget during the live   training but I'll make sure that any Corrections  are made in the downloaded version it's absolutely   free use the links down below and we'll get that  full version right to you right away if you like   these I've got actually a hundred available in a  100 workbook pack so if you don't want to download   each and every one of them and you want a 100 of  my best workbooks you can get that for just $37   all right let's get started with the training we  got several sheets in this particular application   I just want to go over them with you our purchase  order is going to be designed in this screen right   here all of these things can be done in here  we have a list of purchase orders I've done   it just started off with the table that gonna  move things along a little bit quicker just the   basic things all the components of the purchase  order including purchase order number the date a   status vendor and the delivery address one two  and three we also have a list of vendors I've   just pre-populated this just with some data  just to help move things along that's all as well   as the list of items that's important that we  just won't have some data we're gonna be using   a formula to develop the quantity on hand but  I've got to set a minimum stock level of five   just set some purchased coins just some basic  data and I've also just put in some criterion   results here that's gonna help move things along  as we develop the code also a purchase items I've   also put all the list of the items that we've  purchased will go here and I'll show you explain   the criteria in the filter just setting a few  things up beforehand so we can get these done I   know these trainings tend to be long so I want  to advance it as much as possible II set the   basics and then I have a list of sold items now  if you caught our training in the before a few   weeks ago when we create an invoice an invoice  would create something like a table like this so   I've pre-populated with some items that we have  already sold and they would go here so we've got   an invoice number an invoice date the item that  was sold the quantity sold and the price this is   gonna be important because we need to calculate  how many items we have on hand and basically the   item that we have on hand are going to be all the  purchased items - the all of these sold items on   a per item basis so that's important data that  we need to start off on and so I wanted to get   us started so let's get into this purchase order  screen and it's gonna be a little bit similar than   the invoice so if you haven't caught that we've  got an invoice here I've got some columns just pre   set up just to make things go along quicker now  the first column what I want to do is just as we   always do I'm going to use the reserve those for  the admin so we're gonna color them gray I want   those to be for the admin because we're gonna  put in some formulas and things like that and   we're gonna hide these columns and then when you  create these applications it's always good to use   the first two columns for admin and then you can  just right-click and hide them once you release   your applications and you can also protect them  we're gonna call this automated purchased orders   so I just want to give it a title to this and  we'll extend this this will not be part of the   purchase order this will be part of the top of the  screen and then I'll create the purchase order so   let's create that and the merchant center that I  don't want to create that and then our purchase   order actually will start right here I'll put  that in column E so this will be the title of   the purchase order and then what I want to do is  just put in some formatting here we'll make this   one green a little bit different I'm gonna format  those cells and give it a fill and a fill effects   and then we'll just use a green something very  different something very unique so I'm gonna fade   down and then what I'm gonna do is the second one  on this one I'm just going to probably dis column   just see and perhaps I threw J I'm gonna color  those a little bit less of a color a little bit   lighter of a face so we're gonna give that a fill  effects and we're gonna go into the lighter green   so that's gonna go from the medium green to the  light green and that we're gonna give it a nice   fade effect the rest we can just color green  so then what we can do is we can go down to so   let's say row 31 or something like that I'm gonna  try to keep everything on the screen for you this   time so we don't go off but at least for this fade  and then for this it's all we're gonna do is give   it this green and then that'll give us the rounded  out look that we want just so we can encompass the   purchase order in the screen now it now this  purchaser is not going to be very very long   because I want to try to put everything in the  screen for you so we're gonna have this a larger   font let's make this larger and let's italicize  this and give it a color also I want to give it a   nice color maybe a green color so it stands out  something like this and make it bold so we can   clearly see it okay and I also want to do this  maybe we'll give it a different font so we can   see what we like like perhaps let's go with Adobe  rounded bold that looks good and give this a color   I want to give it a font just so we can know what  we're working on here and uptown says and I also   want to put in some icons so our purchase order is  gonna be basically made up I want a Pio number in   G so we'll do Pio number and then a colon here  and then I also want a Pio date purchase order   date here and then I also want status so status is  important because we have to know if it's open or   closed I'm gonna write justify these and then I'm  gonna left justify this is where it's gonna go in   the date should be formatted as a date so let's  go ahead and put that us on a short date put it   in temporarily a 1 here 1:20 and then the status  we'll just put it open ok so basically at that   point what I want to do is I'm gonna put in the  company name here in a company logo so let's see   we have Freddy's t-shirts incorporated and then  we just put in the street this gonna be the top   of it ok we'll just put in any town California  USA ok so that's the top of our purchase order   next up what I want to do is put a divider in  here and then have it very different so maybe   a color like let's say use this dark green and  then a white font on this so it stands out and   then bold so here I'm gonna put in the vendor and  then the dough let's clip those in capital so it   stands out vendor and then maybe delivery address  we want that we have to know the delivery address okay delivery address and vendor we can italicize  those and next up so the vendor would go here so   I'm gonna put in a vendor name here and then  vendor address one in vendor dress to here   and then skipping a line probably on ten we're  gonna do the same thing so let's just copy and   paste this cuz I want a divider probably on ten  and then what I want to do is I'm gonna put in   the item here description I want the description  of the part the quantity here and the price and   then the total so we're gonna Center that also  okay good so it's gonna be coming along fine and   of course we're gonna get rid of the grid line  soon because if we don't want them to look and   then I'm gonna drop down and probably about say  row twenty five let's bring this up just for a   moment in twenty five let's say right about twenty  five here we're going to put our totals here and   that's gonna go for about three rows so let's  color those white for now and just so we can get   an idea and then I'm gonna put in right here I'm  gonna put in the same color font I think I want   a little bit lighter of a green here but that's  okay so let's put in that color let's drop this   back down again and scroll down and I'm gonna  change this color I like a little bit lighter   here okay so we're gonna put a background color  of this looks better and then I want to put here   let's put in the PIO we'll change that to white  of course POS subtotal okay and here would be   the taxed perhaps let's say 10% just put in any  tax so we can have a tax and here the order total okay great so we have all those lists out of  coal in here so we're consistent and then what   I'm gonna do is gonna make these white bold and  right justify so white bold and right justify   there so now we have this and I'm gonna merge  and center this here because I want a footer   message there's some Merchant Center left and  upper so we can put in like please deliver   between 12 let's say and 5 p.m. just so we can get  a nice footer message the purchase order should   have a footer message alright good it's starting  to come together now let's add a border around   this I'll move it up back and forth so we can see  I'm gonna add a border around this so I'm gonna   format those cells and put a border around here  so go into the border and I'll use a green color   sorry it's off the screen here let's drag it up  here I'll probably use let's say this is the same   dark theme and then give it an outline all the  way around it and then I'm going to add in some   dotted lines here so here format the cells here  and I'll bring this up what I'll do is we'll just   add a little bit of like a lighter dotted line  here just so we can see that's a bit too light   and that way it will give us a nice differentiate  between our order so we can see good alright and   then another dotted line here I'm going to format  this set let's highlight those format the cells   and then we're gonna use the same color border  all the way around and inside and then I'm just   gonna make this a dotted line you can't hard to  see but I think so the left so that basically is   gonna say okay the left one for so that we can  there now it's starting to come together our   purchasers we can go into the view and remove  the guidelines to make it look a little bit   better nice it's starting to come together now our  purchase orders alright let's put in some buttons   and also I'm gonna have this as the item row and  I also want a database row I want to know the row   where it's saved to what is the database row well  that's going to be in the purchase in the sold in   the purchased items here it's gonna be this row  right here 3 4 cuz I need to know where it is so   we're gonna store that right here and the item row  it's gonna be a formula we need to know what row   the item is on like for example this item would be  on 3 or 4 I need to know because I need to pull in   the information I need to pull the description  the quantity and the sales price so the row is   very important so we're gonna put that row with a  formula here let's add in some pictures here that   is going to help us out and we're going to insert  pictures and I've got some pictures saved already   that's gonna help us out and great so we can add  it up I'll drop this down and then we're going to   set the sizes automatically and then readjust  them ingly 0.2 - alright great so now we   have our buttons and what I want to do is I also  want to add in some buttons so let's add in some   shapes we'll make it a square shape and use the  same theme color let's say we have here let's do   point three four and then perhaps maybe one point  four that's good one one point four okay I want to   give it a theme color so we can do that let's drop  grab at this theme color and I'm gonna duplicate   that maybe I think I want about five buttons  so control D control D control D D and D okay   so we've got our five buttons but I want to move  these icons on top right because we just created   so whatever we created last is going to be on top  so I'm gonna take all these icons here format and   I'm gonna bring them to the front that's gonna  place them on top of these which is where I want   so alright great so we have our icon here this is  going to be our automated purchase orders let's   increase this a little bit I just want to make  sure everything's on the screen for it I don't   want to go below this so that's why we're gonna do  it alright we have our let's get out of this the   selection tool and increase this that's gonna be  our purchase order I want an icon for our company   so we're gonna put that here and that looks nice  but I don't I want to change the color of that   I'm gonna look at more to our themes so let's grab  this green here there we go now it's looking nice   and we've got automated purchase orders we can  shrink this down a little bit but before we do   let's start building our buttons now so what a  kind of buttons do I want I'm gonna bring this   back down we'll just that now I want to bring that  back down to 0.34 cuz I increase okay so what kind   of buttons do we want we want to add new that's  for sure so I'm gonna type in to add new P Oh what   else do I want I want to I want to cancel new P Oh  cancel new one we're in the when we're in the new   mode and we don't want to be in the new motor we  want to cancel the new so well show you how that   works I'm gonna generate a Pio and to bring this  down so you can see it I want to generate one what   do you mean generate generate that means if I need  items based on the minimum quantity we're gonna   generate it automatically it's gonna be really  cool so it's gonna create items automatic create   appeal automatically in just one click I want  to print key oh that's important and I also want   to save or update save or update that's important  and what else do I want I want that's it all right   we're good to go so just those five buttons and  let's uh now hold those down and what i'm gonna do   is i want the icon on the left print let's change  this to print print and so we're gonna hold these   holding ctrl how about all the buttons and I'm  gonna format them all together so we're gonna   write justify them here under the right right  justify because I want to put that icon on the   left and I'm gonna we won't line them up just yet  so let's build out our buttons now and I'm gonna   copy this control D cuz that's the background  I'm gonna use I'm going to size this ingly   this is the background I want on it because that's  gonna be concerning our and so that I got the size   right and then we can just ctrl D duplicate this  two three four times add a new P oh I'm gonna use   this same background I can cancel and then we're  gonna do generate print will have its own one so   we don't need that print this can be centered  because it's just a small text alright great so   we want the printer here I'm gonna bring that up  that looks okay for now and what I want to do is   I want to save or update so I'm gonna use this  for the save or update this icon here for the   save update and I'm gonna bring that for actually  I'm going to bring them all forward because I want   them all on top some and hold down the control we  have two of these we don't need that format align   and the format bring to the front and we don't  need two of these don't remove this one okay   so Jen's just going to go for generate purchase  orders I want that to generate it and I want to   cancel new just in case we're getting the cancel  screen so we're gonna put that here and then I   want the Edison to be add news so alright good now  we've got our button so let's group them now we're   gonna hold down the selection group and name it's  always important to group a name format align to   the middle and then I'm gonna group them and then  I'm gonna name it so this is gonna be called Save   button okay enter and then add new same thing  here we're going to let's say align the middle   good that's gonna give it and group it and then  we're gonna call this add new button and cancel   new same thing we can just actually that's kind  of lined up already group it cancel new button   these important we need to show the cancel new  cancel new and the add new are going to be in   the same location so we're gonna focus on that  and I'll show you that this one align the middle   and then group it and then just call this generate  the O button alright and the last one print P Oh   button there we go then we've created all of our  buttons group it print button it's fine okay good   so let's bring these up to the top now get out of  the selection when all out the news I'll have to   save in the update right here that's gonna be the  first add new will be below it then cancel new and   add new are going to be in the same location so  let's there only one of those is going to show   at a single time so not both so we can put those  generate and then print all right I like that I   like the locations of them let's do our selection  two and line them up we're gonna group them I mean   we're going to highlight them all select them all  actually format align the left and then of course   I want to distribute horizontally vertically  distribute vertically perfect okay cancel new   I want these right on top of each other because  they're only going to show at the same time so   we're gonna slide that over put those right on top  of each other just like that cancel new add new   are gonna be on the same and I'm gonna move this  back we're gonna use this second so send to back   we're only gonna show one of those same time all  right it's looking really good now we can get out   of the selection mode and put some borders around  this almost done with our formatting here that's   gonna look really good and we're gonna set the  print area this is gonna be our print area right   here so we can do that now might as well under  the page layout and then print area and then set   the print area that's gonna be our print area  let's take a look in the preview and just see   how that would look all right that looks nice it's  gonna print all in a nice so we're good to go on   that now we can start to add in some named ranges  that's the second point we've got all over buttons   and we also want to add in some information here  so perhaps in venner I want to do vendor row I   need to know the row of the vendor I also need  to know whether it's a the rows of P Oh what   roses that's the database row right the purchase  remember we have purchased items purchase order   list it's gonna be a list so I need to know what  row it's on here and I'll show you the formula   for that in a moment but we need to track that I  need to know if it's a new P o or not and I also   need to know if it's loading P o load we need to  know the difference between when it's loading from   saved when we make a change here when we enter to  hear about the second purchase order to load when   we entered three here on literature so basically  based on the change I want that purchase order to   load alright so next up what I want I also want  to know the next Bo number next P o number so we   need to know all that information let's highlight  that and color that distinctively so that we can   differentiate between that and everything else  great alright so now it's time to add in our   named ranges let's take a look at this what kind  of named ranges do we have well the first thing is   we need a Pio number that's important so let's say  we have POS here I need to named range a dynamic   named range for here so let's create that under  the formulas name manager notice we only have to   print order that we set new let's just call this  P Oh number and though I want to dynamic so what   I'm gonna do is I'm gonna copy this I'm gonna add  offset to it so control C and then I'm gonna click   right here and then put it offset P Oh list I want  to start it on in the header row but we don't want   to include the header row that's just in case  there's no data so it's gonna be comma and then   one wrote down its gonna start com , count a what  on account I want to count all the range from P oh   let's say to a and then perhaps nine nine nine  or something nine nine nine or something like   that something large like that okay and then one  call them down one row down -1 one row down comma   one let's tap out and tap back in and make sure  that encompasses our data with the dancing ants   perfect you see it's just covers that okay good  so we have the P own number what else do we need   I need to know the vendor names obviously we're  gonna so let's click on the vendors and see get   some name ders I'm gonna do the same thing with  a vendor name so formulas name manager and create   a new and we'll call this vendor name and then  I'm gonna do the same thing here I'm gonna copy   this right here just as it is and then I'm gonna  put in offset starting at a 2 and then comma 1   means one row down comma comma count a I want to  count all the text in this range what range let's   say they eight nine nine nine and the minus one  why because we're including the header row but   we don't want that that's just in case there's  no data comma one tab out tab in make sure it   encompasses all the data to check it click okay  great so now we have purchase order and vendor   name what else do we need well I need the items  purchased and the items sold I need that count   number so let's do that right now in the items  purchased we don't have anything but let's just   say we have some quantity one corner right let's  just say we have some data it's encompass out an   add a named range and then we'll delete the data  formulas name manager and let's call this items   purchased new items purchase and then I want the  purchased items basically we're gonna do the same   thing using offset so copy that as we repeat it  you'll learn it very very easy offset and then   again we're going to bring it here one down count  a paste it in and then we will use a larger engine   99 - 1 comma 1 ok first column so look at that  okay now we have items purchase perfect click   ok and now the last one we also need items sold I  want to know all the items sold so that's gonna be   here so again one more formula name manager new  and this time we'll call this items sold items   sold and what's that gonna come again copy this  copy the main thing it makes it a little quicker   add in the offset Oh ffs et all right and then  also one row down really have a few I don't have   committee comma one count comma comma count a  paste it in add in your large range here - 1   comma 1 ok tab it over make sure it's includes  all the data good so now we have this let's take   a look at our named ranges we have items purchased  items sold pio number a print area and vendor name   ok we also need the item names one more let's go  in there i want to know a list of items name and   that would be here item name so we can add that  and that's our last one new and then we'll call   this item name that's fine name as it is again  we're gonna copy this i also want a dynamic named   range offset items and then clear this out comma 1  comma comma count hey you're getting it now hang a   bit nine nine nine alright so this is the last one  minus one comma one okay tap out tab in perfect   okay good so let's go over this we have item names  we have all of our dynamic names for the all the   items that we purchased it even those no data yet  all the items that we've sold the purchase order   numbers we need that and we have the vendor names  great so we've got everything we need we can start   adding in some formulas now so let's go the next  purchase order well we have if assuming let's say   we have the first three used i'll want the next  one to be four so we can use a formula in math   that we always want to wrap that and if equals  if air if there's an air I want the max max of   what the PIO numbers Pio numbers but I don't want  the max that I want to add one because I want the   next one it's a plus one but what if it's an air  well if the tenor I just wanted to revert to the   first one which would be blood okay great so it's  for now let's left justify these drop that down go   to home and left so what if it is in there what if  there aren't up you know then I wanted to revert   to one which is what we want now so deleting those  it's gonna revert back to one perfect that's what   I want I'm going to set this to one it's already  set very good okay so we've got that already set   up let's say I want to know the vendor row and  I want to vend or drop-down list here I want to   create a vendor name so I'm gonna drop down list  as you remember in the formulas we have already   of vendor names called vendor names so if we  edit that just copy and paste it it's a good   way to get it exactly right I'm gonna drop down a  list of vendors right here so we can do that into   the data and data validation on a list adding the  list would be equal to vendor names paste that in   that is a list of all of our vendors that's what  I want when I select a vendor I want the addresses   to show up here for the vendor but I also want  to know the road this offender how do we know   the road we can use matched now let's take a look  I want the row so if we select acne our first a   match is gonna return one but I don't want one I  want three so to do that we just need to add two   and of course we're gonna wrap it in if air just  in case it's nothing there so equals if air match   what are we matching we're gonna look up of course  right here under East seven then we're gonna comma   what are we matching the vendor name is the writ  array we're going to be looking up I want an exact   match so that's zero and then if there's an air  we're going to of course used in double space if   we want nothing all right that's gonna get us one  but we still need to add in two right because I   want the third row so plus two is gonna get us Row  three that's all I want I also want the purchase   order row again same thing I want to know the  purchase order if we have well let's say one here   that means I want to make sure we show row three  so we can do that again here equals if air same   type of formula match what are we matching in this  case we're gonna look up the P oh here we're going   to use the lookup array of P own numbers here  we want an exact match would be zero and then if   there's no match we're just going to or an air you  can use double quotes again adding Row 2 because I   don't want just the match I want to add it I want  to get the 3 so plus 2 is gonna give us 3 new P   O is gonna be a true or false and the Piolo it's  gonna be a true or false also so we can set that   up as well that's it that's all we have we're  just about ready to start our coding let's save   our work great so we've got our automatic purchase  orders here we're looking really good and I'm just   going to start with the code now let's take a look  at everything we have everything just said alright   now we're ready to get into the VBA code I got  a lot to show you it's really really cool so the   first thing I what I want to do is when I make  a change to this I want both the dress to show   up here what I want to want to show the billing  address 1 and the billing address 2 when I make a   change to those vendor names so let's add that in  right now and that's gonna be on the sheet through   our developers Visual Basic you can use alt f11  of course if you don't have the developers tab   open you can locate that into these options  and just click on the customize ribbon here   and you can find it right here so that'll go to  also the shortcut alt f11 alright we've got no   code no modules here we can create that very  soon purchase orders of the sheet we're gonna   be focusing on and what I want to do is I want to  create a track I want to create an event based on   the worksheet and not necessarily change in this  case but when we actually make a change actually   making a change to the worksheet right because  what what is the change and what is the cell that   we're making the change to we're going to check  two things we want to make sure if the user has   made a change to e7 and we want to make sure that  the value of e 7 does not equal empty so we can   do that right now with just a little bit of code  so if not and we can change this that's automated   text based on autohotkey that's a free software  I use so when you see me typing in so fast I use   a software called autohotkey to help me it's a  free software you can google that and there's   another condition range e 7 must not people at  a seven month be blank so as long as those two   conditions are met we want something to happen I  wanted to mention the vendor row as long I need to   know that vendor row and the vendor rows located  in b2 right let's the double check balance bring   this down we don't need that so high vendor rows  located in b2 so then don't row equals dot range   b2 value that's the vendor row now that we have  the vendor assuming that the vendor wrote if if   the vendor row equals zero then exits okay we  can't have that all right next up now that we   assuming that we do have a vendor row we can set  our addresses what do I want to do basically what   I want to do is I want to set e8 and e9 pretty  much equal to whatever's the row here B and C   so we can do that with the following lines of code  range let's go let's look back on here so we know   we're doing range e7 e8 and e9 that's our focus on  e8 dot value equals sheet for y sheet for sheet 4   is our vendors dot range B and the vendor bro dot  value is the address 1 ok great so now all we need   is the address to we can just copy and paste this  and make a few small changes and we're done that's   going to change to e9 this is gonna change to C  so now when we change it assuming that there is   in this course is address too now when we change  it or just double click on it get rid of that dot   we don't need that that's only for wish sheet and  reset the code okay so now when we double click   on it the address is gonna appear here perfect  that's just what I want alright great so now we   have automated now we can put in whatever delivery  address we want but I want the delivery address to   be actually in one cell so let's merge and center  this merge and center and holding down the control   so what I'm going to do is drop this down go into  the home merge the center and then left justify it   now out the delivery address to be spreaders shirt  company one two three four five main street any   town okay so you can pretty much get a whatever  delivery address you want that's gonna also be   saved next up what I want is the item list I  want to drop down list of items remember we   have a data validation remember in the formulas  we have our named range here I should say called   item names that's just what I want to do for here  so let's do that add in the data validation here   data validation clicking on that we're gonna use  a list right we want the item names equals item   underscore name click OK alright so now we have  a list of item reason that's great that's what I   want but I also want to know what row each one is  it on I don't put a formula down here to determine   what rows this for example the perfect t-shirts  when we look in the items we notice that is row 5   again we want to base the name add to use a match  I'll explain that just a second so equals always   if air always want to add that if air wrap it in  match again what do we matching we're gonna match   whatever is in d 11 D 11 and we're gonna match  it with the item name and then we want an exact   match again but we don't want the road we want to  add two just like we do so we want the row number   comma and if it's if it's an air I want to make  sure it's double quotes that's going to give us   the row number we can bring that out either way  we can hide this of course if you want to using   the same font color as the same background and  I'm gonna bring this down and copy this all the   way down right here that's what I want all right  great so it's empty its works perfect anytime we   addict to you it's gonna show the room excellent  okay good that's what I want now we're getting   together we've got the formulas here we're looking  really good next up let's continue with some code   what I want to do I want to be able to save this  so I also when I add this in when I added I want   to add the description the quantity in the price  and that's on change of anything indeed assuming   that there is a value so let's add that into  the code since we're working on the on the   sheet code on the sheet meaning when a user makes  a change on the sheet we want something to happen   assuming that there is a value here what do I  want to do I want to add the description the   quantity and the price and I want to add that  in from right here the description the purchase   quantity in the sales price from our item list so  we can do that with just a little bit of code so   let's bring that through right now and continue  on with our code so let's put this on change of   item but no not a blank about okay that's right  so if not what are we it's d11 what is it we're   gonna make it change any change between D 11 and  D 24 that's what we're gonna focus on now so any   change between D 11 in D 24 so any change there  then that's what I want to focus on and range   b5 is false I also want to make sure it's not  on load dot value why is that because here's   why I want to bring in the description quantity  and price from the items right but what if we're   loading a purchase order in that case of what  I want to bring is I want to bring in the item   the description the quantity and the price or just  the description of corneum price from what it was   purchased from the purchase or remember because  the quantity is going to be different the price   is gonna could be different too so basically  what that means is we were loading a purchase   order something that we saved previously we want  to load it from whatever's been purchased whatever   was saved however it's a new purchase order or  we want to add on to the purchase order then   we want to bring it in from the saved item list  so there's two different ways we load it so we   need to differentiate between those two so the way  to do that is to use whether it's loading or not   and that's located in b5 if it's loading meaning  when we change the purchase order and we load the   previously saved purchase order this is going to  go to true and then it's gonna go back to false so   we want to make sure this is false before we add  in the description quantity in the price from our   item list here so to do that we just run a quick  check and that quick check is to make sure the B   Phi value it's equal to false assuming it's false  we can continue on with the code then what do we   want to do then what I want to do is I want to  make sure that C and values not empty right if   C I can't add the description ing price  if I don't know the row so we want to make sure   that this is not empty so we can do that with the  fall in a line of code if range C and the target   row dot value does not equal empty then I can  do something then what I want to do then I want   to add in the information so we're gonna set the  item row the item we got a dimension the item row   up here let's do that dimension we can do it after  here after this cuz we're not dimension item row   as long it's important the item row of course what  does it equal is equal to C and whatever's in here   C and the target row equals C in the target item  row right we're going to mention this item row 5   before whatever it is I wanted to mention it C  in the target row so now that we have the item   row we can load the details that are necessary  for that what are those details well there but   basically the description the Quantium price so  we can do that with this line of code ok so let's   take a look at this what do we want let's take a  look what I want is e through G equal to let's go   into the items basically B through D B through  D so let's write that right down range on the   purchase order II threw G what is the rows the  target row so let's write that E and the target   dot row and what is it all the way through : G  and the target dot Rho dot value equals what is   it equal sheet v dot range what is it in there B  and the item row reset the item row already and   the G all the way through what is it let's take a  look at that just so we know we're saying all the   way through deep okay so and : d and the item  row dot value okay that would be what was it   description item description quantity price okay  so bringing all of that let's test that out it's   very easy to test out just to double check it all  we need to do is double click on here and alright   our quantity and shirt brought in let's bring this  to the center now it looks a little bit better and   we got to give it our total we've got to make sure  that's in so what's our total it's gonna be equal   make sure that if I want to make sure that we at  least have a price there because we can't have a   price or quantity we need both if there's no price  than equals blank then of course blank otherwise   multiply this times this quantity right the price  times the quantity perfect okay 336 and let's   format that I'll bring it down I'm gonna copy that  formula bring it all the way down here and then   bring that formula perfect gives its blank unless  we select I don't let's double click here perfect   okay now just let's format these format all of  these as currency so drop that down and we're   gonna form at those as currency alright great so  now we've got that everything's formatted just   nice perfect our purchase orders coming together  very very well so now we understand let's just try   this on another item make sure it works okay  it's working really great that's just the way   I like good now we're ready to run to create a  macro that's gonna actually save it okay so let's   do that right now let's create a macro that's  gonna actually save this we're gonna save to the   database save the work that we've been doing right  and we're gonna go in back into the VBA and open   this up and we're done with this let's clean this  up a little bit for now we're gonna we're gonna   to add one more macro on this in a little bit  later on when we do macros so let's just write   that write some ideas on change of Pio load P  oh okay so that's gonna be here later so when   basically but we haven't written that macro that  when we change this we want to load the previous   work order so when we change this we want to load  the previous purchase order so that's important   we'll get to that in a moment but let's get let's  start off with the least saving because we can't   load unless we actually save something first so  create a module let's bring this back up expand   this a little bit we're gonna write some more code  now and we're gonna create a macro called insert   module and then I'll just create a little module  we'll call this P oh macros in the properties here   we're gonna give it a name call it P oh macros and  the first one we're gonna write is we're going to   call this save and update we're gonna use the same  macro so sub and then P Oh save update what do I   want for this log in a dimension a lot of rows  I want to make sure that we dimension let's move   this over a little bit dimension the PIO row I  need to know the rope as long we need to know   that I also need to know the PIO item rope I'm  gonna make sure we're gonna subtract the item row   as long and I also need to know the last item row  if we run through the items last item row as long   and of course the item row item row we're gonna  be going through the items because I need to say   going through all the items because it's gonna  be different right we're going to be we're going   to be doing gonna be saving this information  here and this information to our P o list here   vendor delivery address I'm gonna save all that  and then our items themselves are items all this   information including the item of the description  of chronic price that's going to be saved in our   purchase order items here all of that here so we  need that so I'm gonna have to go through those so   to do that we need to track each of those rows  so we dimension them all as long with sheet 1   that's the sheet we're focusing on what do we  want to do first of all we need to determine   if new or existing vo that's the first thing  we want to do so we can use that through b4 b4   is gonna track how whether it's new or existing  using kiss so we can determine that if it's true   if dot range b4 dot value equals true then it's a  new else existing pio and if so that's our that's   our if and end if so we're going to do just a  few things based on whether it's new or whether   its existing okay so if it's new what do I want  to do I want to set the P o ro what is it going   to be equal to the first available row I need to  know the where's the purchase order row it's gonna   be in this case for but I'm gonna remove this so  in this case it's gonna be three so we want to   know the first available row so we can do that  with this appeal row equals of course it's our   P o list sheet 2 dot range a let's just use larger  nine nine nine nine and Excel up dot Rho plus one   right I don't want the last row of value on with  the first available row which is the last row of   the value plus one first available okay so that's  if it's right but also what I want to do I want   to add the P own number because it's a brand new  one so the top range where's our purchase order   numbers going remember our next available purchase  order is located right here so in that case I want   to take this I don't want to put it right here  just to make sure it's correct so H two we're   gonna add this on new but I want to make sure to  add it here as well so H one is going to be equal   to whatever is in b6 so we can write that line of  code range h1 dot H 2 actually I think it is value   equals be $6 and be sick so we're gonna write that  down as the next purchase order number next vo   number okay so we've got that set up what else do  we want to do that's it that's all I really wanted   to do if it's a new one there's other things that  we can do we can add in the purchase order number   on a so we probably should do that too one more  thing so sheet 2 dot range a and what is it the P   over oh now that we've defined the Piero dot value  equals I want to put also that in H 2 dot range   H 2 so I want to put the purchase order number  but only for new one dog value P Oh number what   is that well when that is is I want only four new  ones because it's already existing I want to take   that purchase or now I'm gonna put it right here  right here here put that in column a that's only   four new ones because the existing ones it's  not going to change so the date theoretically   could change and everything else could change so  just at least put the purchase order number okay   so that's all we're gonna do if you're saying but  what if it's existing well the P over oh if it's   existing let's look at it in B 3 equals dot range  B 3 dot value that's the pur purchase so now we've   differentiated between new and existing but we've  got the P over ov regardless the PIO row is going   to be whether it's new existing so the rest of the  code is going to be running regardless of its new   or existing so I want to update sheet - that's  our P O list sheet - dot range what I want to   do will be is the first column that we have a net  and the PIO rope dot value equals what it's going   to equal the date so that's some dot range dates  located in h3 that would be the PIO date correct   let's just double check that make sure that we're  on okay so we want to add the PIO date I also want   to add which is an h3 I also want to add the  status and I want to add the vendor and then   I'm going to add the delivery address so we want  to add all that information in bringing all that   in right here again pío date status vendor and  then delivery address one through three we can   do that with the following lines of code we can  at least just copy that's gonna be a little bit   easier a little bit faster so we're gonna go b c d  e f and g so let's let's add that in c d e f and g   okay so now we've got that in so let's just add  in the detail so the status we need to know the   PIO status the vendor also the delivery address  one and we need to know the delivery just two   and three two and three okay so now that we've  defined that we just need to set what it where   they're coming from the status is located in h  for the vendor is located in even we've defined   that already and also the delivery just F is in F  seven eight and nine I believe let's take a look   at that f7 f8 and f9 add in that information here  so good now we're getting it f7 f8 and f9 okay so   we've got all that information and now we've saved  all of it so what else do I need to do now I need   to add in the road remember we have all of our  we have all this information now filled out but   I need to add in the purchase order items need  to add them here the PIO the item description   quantity price route so I need to add all this  information right here so we can do that with   those following lines of code let's just write it  up in a comment here so we're gonna add or update   kiyo items because this could be an update too  so we have to make sure we differentiate between   whether it's a new item or an existing item so the  first thing we want to do is determine the last   item row because we're gonna be looping through  the item so I need to know the last item row of   course it's going to be anywhere from be 24 on up  so let's get that last item for now for instance   going to say the last item rope equals dot range D  24 dot and XLF dot and X Allah dot row okay that's   gonna be us our last item row next up what I want  to do I want to check if the last just run a check   item Row is less than 11 just in case then go  to no items okay and then we'll put down here no   items just so we can skip everything else in case  there's no items okay but assuming that there is   that we can continue on with our code now we can  run a loop for item row it's gonna start at 11   all its equals 11 - last item row so we're gonna  start a loop next item bro close our loop always   good to close and open and close at the same time  if we're gonna run an if statement I need to know   if it's an existing or new the way we tell is  the database row if it doesn't have a database   row what is the database row that's located here  would be this row graph 3 4 5 whatever it is so   if it doesn't have a row that means it's a new  item we need to create a new row if it does we   can use the existing row so we can just use an  if-then statement to determine that if dot range   I'm eyes the column that we just focused on and  what and the item row dot value equals doesn't art   just not equal to kiss does not equal empty then  it's an existing row else it isn't new row and ok   so now we can write some code based on whether  it's a new row or an existing Road so the PIO   item row equals in this case if it's existing we  know the row it's going to be in I it's going to   be right here so we know we know it we know it's  gonna be right here if it's existing what if it's   new or just put existent just in case what if it  is new well then the pl rose something different   what is it going to be the first available row  first available row on our what what sheet that's   going to be cheap three right here purchased item  just gonna be here our first available row based   on whatever is a is so we need to find the first  available and add that in here so we can do that   with just a little bit of code so we can use to  end up X up so in this case P Oh item row equals   sheet 3 dot range a little shoes a lot call 999  dot and X a lot dot Rho plus one that's the first   available item throw what else do I want to do  if it's a new one if it's a new one I also want   to add on a little bit more information something  that I wouldn't do if it's an existing item what I   want to do just one time not every time in this  case I want to add in the purchase order number   item description quantity price looks good all  change but the PIO row and the row these three   items one two three I only want to do when we  enter the new row so that's only for one one   time new row so we can do that right here so the  first thing I want to do is add in the PIO number   so sheet 3 dot range a and the PIO item row dot  value equals what equals a Pio number so I wanna   make sure to put in that where is the Pio number  that is located in h2 got range h2 that's the P   own number that's important Pio number okay what  else don't want to add in I also want to add in   those two other things that I mentioned to you  so we can copy this and then add some additional   information here using a different column and then  the next column would be G what's in G Y G again   let's just show that to you real quick G of course  is going to be our row and then F would submit a   purchase order so we'll add in both F and G here  F and gpro and then row our P O row what is that   it's going to be this our row 11 12 13 that's our  pierrot what is another thing that I want to add I   want to know the row I want to know the row here  so we can just use a formula for that so f and G   are the two things that we need to enter so let's  do that so F F is equal to what F is equal to the   item row because I'm going to know that the add a  row and then G is the other thing that we need to   add in G and that's going to be just a formula  that I mentioned to you before so we can keep   track of the database show so that's going to be  rotation marks equals row that's gonna keep track   of our row and I'll show you why shortly that  would be the database row and this is going to   be the papito row Pio row we need to keep track  of both of those alright great so we've done that   now the rest of the things that we're gonna be  even gonna be doing regardless of it's a new   item or an existing item so we can add in all the  additional information and what is that basically   what I want to do I want to basically say item  description quantity and price these for B through   e B through e equals equals in this case D through  G so that's all I just want to write one line of   code that says all of that so we can do that right  here so cheap three dot range B and Pio item row   and colon all the way through what through e again  and the PIO item wrote and Pio item row dot value   equals what from our P o dot range again like I  mentioned to you d through G D and this time it's   going to be the item row item rope item row and :  G and the item row so that's gonna basically take   all the information from our purchase order and  bring it over into that would be what is it the   item description quantity okay great let's just  take a look at that that looks right okay so we've   got that we've that's it that is all we need to  do to save it and then we just need to update the   button sets because we no longer a new purchase  order so we need to make sure the update let's   just finish basically two buttons also I want  to set V for default dot range b4 it's no longer   a new purchase order it's not an existing so V  for value equals false existing purchase order   and what I want to just update the button set so  what button sets do I want to do let's just look   those over real quick we're gonna go in and view  our shapes so we have the add new add new I want   to make sure that is displayed and the cancelin  do cancel newest so cancel new button should be   hidden and we want to make sure we display the add  new button so those are the two buttons that we're   going to be focused on so let's just write some  code for that dot shapes cancel new button dot   visible equals and there so false and then dot  shapes add new button that visible equals come   in so true okay there we go that's it that's  all we have to do and with we'll run it we'll   obviously we're gonna save all the work we did and  then we're gonna run our code test it out and fix   any bugs that we might have so that's going to  be for saving you let's just purchase I don't   want to clear this data out here cuz that's just  temporary purchase order okay so let's take this   say that's the button the macro we just created we  only have one macro pio save update okay so save   our work again and then let's just run the code  check for any hairs variable not defined Pete   Oh ro that should be Pete Oh remember not Pio row  so let's fix that okay Pete Oh ro just get rid of   that I'll fix that okay so we don't need that not  powr Oh okay so now that we fix that run the code   okay no errors perfect alright now we've saved it  let's take a look at our Pio list our information   is here just the way we want it that's covered  what about our purchased items our two items are   here the quantity the rose here the pol excellent  now we're in business so we've now saved our /   sort of good so what do I want to do next well I  want to make sure that we're gonna load it so when   I put in one here or whatever pert server I want  to load all the information so that's the broker   but we're gonna write okay great actually we need  to put the database row in here too I want to make   sure we do that let's just add that in real quick  I'm gonna put that in because that's going to be   helpful when we load it and that's only for new so  all we need to do in this case is do dot range I   and what in the PIO item row dot value equals was  it equal and the item row dot value equals what's   like an equal it's going to be go the PIO item  row okay great so we got that so now we've got all   that set up basically what that is I want row what  is in this case let's take a look at that in this   case it's Row 3 and Row 4 so we want to make sure  those 3 and 4 put in but that's through VBA so   good and why is that well let's just take a look  at that alright good so let's say we add a row on   to this let's say we add a new item on to this  and we want to save it we want that row now to   appear here so now that we have another one code  it works perfect so now we have in our purchase   orders we have one more purchase order number run  everything is set on our purchase order okay good   but what I want to do now is what I want to load  it and the best way to do it to load it is I want   to take when we load it I'm gonna take whatever  information is here I want to bring it back into   this purchase order and then what I want to do is  I want to run an advanced filter based on this and   I want to put in criteria here so that means  let's just say we have tons of personal items   and tons of different purchase order numbers but  I only want to know which items are for purchase   order number one we can use an advanced filter for  that and to do that we need a criteria so what is   that criteria I'm gonna say equals it's always  our purchase or it's always going to be based   on this here so we're gonna say equals H 2 so the  purchase order is gonna be here so what