Discover the Best Invoice Format for Inventory Management

Best invoice format for Inventory

hello this is randy with excel for  freelancers and welcome to the physical   inventory worksheet in this week's training i'm  going to show you how to create this incredible   inventory worksheet complete with barcodes  pictures navigating between previously saved   adjustments i'm gonna be able to filter based  on any type of heading including this and we're   gonna also be able to sort based on any type  of field in just one field it's giving me   incredible training it's all gonna be from  scratch so i cannot wait let's get started all right thanks so much for joining us this  week i've got a really fantastic training   counting inventory is a critical part  of any business you've got to know how   accurate your inventory is adjusting that  inventory is really really important so we're   going to show you how to create this inventory  automated worksheet so it's going to be easy we're   going to be able to print it we're going to be  able to adjust it we're going to be able to save   those adjustments and look those adjustments up  i'm going to show you a really cool filter tool   we're going to be able to clear the filter  add new adjustments printing them and a whole   lot more almost also how to insert these  barcodes pictures so i hope you'll stick with   us we're going to do it all from scratch so i'm  going to show you every step every line of code   every format and every formula so i hope you stick  with us on this training i do really appreciate   you sticking with us we are here every single  tuesday now this workbook is absolutely free   all you need to do is click the links down below  using your email or your facebook messenger and   we're gonna get that sent over right to you i do  appreciate these one way to support us is to do it   with our patreon platform in fact just last week  i created an update to a previous training where   we had this local business directory now for our  patreon members we got to now review so we added   this training last week but we added this really  cool reviews feature so we can automatically load   look up all the reviews on a per business and  create this really cool pop-up so if you like   this additional feature that is one of the many  features every week on our patreon platform   i create these applications and then update them  on our patreon based on your suggestions if you   want me to add a feature or you want to focus on  an area or maybe you want me to fix something i do   that directly inside our patreon and just like  that if you want this barcode these pictures   or any of the icons that come with these training  that's also available in our patreon along with   a ton of other features such as downloadable  workbooks we have downloadable video training   videos for offline viewing we also have pdf code  books and a ton of other features so i hope you'll   check us out there i'll include the patreon  link down below i hope you'll see us there all   right great so let's get started on this training  this is a sample this is what we're going to be   creating this is what we're going to be doing just  so you know i'm going to put this away and we're   going to start out with a blank sheet so we'll go  ahead and save it this is the sample i'll close   this up we're going to focus on this sheet here  this is where we're starting out so we know now i   did add a little bit of data in here so let's just  show what we have this is where we're going to put   our physical inventory worksheet of course we have  some data and that data is going to be items right   so our item list includes item name type and all  the parts purchase sales description cost and so   on and so forth quantity and stock and we have an  associated picture now that picture is based on   a folder right i've got that stored in one of my  folders and so as you can see inside this folder   is all of our pictures so i want to make sure that  we're going to need this path of this picture so   we know where to look those pictures up the names  of these pictures are associated exactly with the   names inside the picture right so that when you  get this file if you decide to join our patreon   platform you'll get these pictures and you can  test it out so it's a great way to test it out   all right we also have the adjustments what  are the prior adjustments right we've made an   adjustment we've adjusted it what is the date that  we made that adjustment on and what was the total   amount so we've got to keep that we also want to  know what items were adjusted inside that so the   individual items on the adjustment we've got to  keep track of those so each adjustment comes with   an id so this was all part of adjustment id one  these were part of adjustment id two and these   were part of three so we need to keep track  of each one i need to know the adjustment date   what is the id of that what is the actual quantity  what it was changed to right and i also want to   know what rows associated that's the database row  we're going to get into this a little bit later on   okay so let's start creating this from scratch  now and what i'm going to do is i'm going to   take those first two columns and just going  to color them gray because i want to make sure   that those are used for admin purposes only so  we'll color those gray and the first five rows   we'll give those a little bit of a different color  because i want to let's drop this down we're going   to be doing some formatting work i want to put  some formulas and some codes in there so we want   to differentiate that we're going to use this as  our header row i'm going to give this a little   bit of a darker color because i want to put some  headers in here so i'm going to have our headers   run across row 6 here put the title up here it's  going to be called the physical inventory let's   put the title up here i've already done a little  bit of formatting as you can see inventory and   then worksheet okay that's what we're going to  go to give it name and then what we'll do is   we'll put some buttons and an icon here i do want  to give it a nice background shape and then what   i want to put is some headers in here so here  i'm going to have the item id then i want to   have the picture here so it's going to be hidden  i just want that picture name here i'll skip there   and then i want to put in the item name here  the part number here i'll put in whatever   category here here we have and then what i want  to do is i want to put in the sales description   this long one sales description we can put in  that just we can we have enough room to spell   it all out that's fine then we'll put the item  cost here next up in column i want to put the   quantity in stock on how many are actually  in our current stock so we can compare that   then i want to put the barcode so we can scan  those in using a scanner if necessary the picture   will go in here in column l we are going to put  the actual quantity okay so that's going to be the   adjusted then in m what i'm going to do is i want  to know the value adjusted so i'm going to put   a formula in there we're going to call that the  value adjusted right so we know the value between   what is currently there in our actual amount i  want to put the item database row item database   row that's the database where it's located in the  item there and then if it's been saved already   inside our adjustment item i want to put that row  in there adjusted item database row i'm going to   put that in column o okay so what i want here  is the item database row that's going to be the   row that's located here it's actual row the other  one is going to be the adjusted row here so i want   to know what row it's in here great so that's  basically what i want our for our headers to be   and we'll give that a little bit of a color here  and so i'll just only these parts so i'm going   to right click and then format those cells then  we'll just use a theme here in this one maybe   we'll use this one right here this darker theme  here and then just we have it actually we'll go   with a fill here and then what we'll do is we'll  do a fill effects and we'll do a two color fill   effects here so we'll go from this one to the  light one or the light one to the darker one   that should be fine so we can see that because i'm  gonna have a top header too so we're gonna do that   and then that top header i'm gonna put i'm also  gonna call that we're gonna call that the physical   inventory worksheet so that we can see that on  the printout so i'll go with this a little bit   darker that darker theme so we're going to use  the same color theme but just a little bit of   the darker one here so that it blends in properly  click ok all right so what i want here is the name   probably right around somewhere around here we're  going to call this the same thing we do a physical   inventory worksheet and now what i have they'll  just put some borders around all of it so it's   nice and clear and then we'll clear some borders  out because i don't want them all format those   cells and then i'm going to do some borders we'll  do this brown color let's bring this up so you can   see it i'll give it this keep it the same theme  which is this brown color here and then i'll just   color it that thin border all the way around in  the inside and i wanted to want to remove a few of   these borders here these borders we don't want to  see here so we'll keep those up i do i don't want   to merge and center these so we'll just remove the  borders from here that should be sufficient not   there and that should be sufficient there okay  so we're good to go we've got our little color   i also want to give this a nice background so i  want to you go into the page layout and give it   a background so when i select background here i've  got a white paper background here that's kind of a   cool so it gives it this background so now it's  kind of a nice and i also want to remove those   grid lines so back in to the view here and we  want to move the grid line so we get a nice look   we're going to put some button sets up here i'll  put an icon here so we're going to let's go ahead   and add the buttons now and add the icons after  because we want to make sure the buttons are below   the icon so we'll add some shapes here i'll use  a rounded uh rectangle corner here and then i'll   give it a height of about point three let's  say point three one that should be sufficient   the width is gonna and we also want the font in  uh white so we're gonna call this clear filter and   then what we'll do is i'll just place this  directly in the middle here and then on the right   and then we have space for icon so that's pretty  good and then what i'm going to do is i'm going to   use ctrl d i'm going to duplicate that and we're  going to call this the next one we're going to   call this new adjustment so this one's going to  be our new adjustment we're going to use that   one to create new adjustments you saw it work in  the sample so new adjustment and we'll set set the   width so we have space for the icon and i'm also  going to duplicate this i also need to we need to   print it out right so we're going to use control  d print and then worksheet so we've got to print   it out again one more duplicate i want to now save  it so we need to add one for save called the save   adjustment and then also what i want to have is  i want to have some navigation some arrows so   again we're going to insert a shape instead we're  going to use this arrow here we're just going to   create an arrow but i don't want i want to save  room for some text so i'm just going to use that   up like that and something like that would  look a little bit good but not quite that much   and then a little bit down like that so what i  want to do in this case we can zoom in if you want   to set the size right i want to call this previous  so we'll do pr previous and then i'm going to make   it a little longer so it fits all the text in here  not previous previous so now that we have that   button and then i want to do also i'm going to  center this here just like we did here just like   we did with the others and that looks pretty good  we can reduce it a little bit and then the next   one's obviously going to be called next so now i  don't want it if you see i don't want i want less   spacing around it so what i'm going to do is i'm  just going to right click i'm going to format that   shape before i create the next one and then we're  going to go to the text box here and we're going   to reduce the left point zero three point zero  three that's to be sufficient enough so that way   we don't have so much spacing around the button  there okay once we're set up with that then we're   good to go with that again we wanna make sure it's  centered i like that there centered in the button   so i want to do now a next button so the best way  to do that this is really our next button so i'm   going to duplicate that and then what we want to  do is we want to call this one here let's move   those out of here i want to switch those right  so our previous here is going to be aligned what   we're going to do is we're going to rotate this  we're going to flip it horizontal so our previous   looks great now all we need to do is set our  next move it over here and set our next one here   change this text to the next so now we've got our  navigation buttons here so we're going to change   this to next okay so all we want to do is line  it up so i'm going to grab my selection tool here   i'm going to line all these up here select  them and then we're just going to make sure   that they're all in the same line okay that looks  good there i like that we'll undo the selection   tool here and then just move these a little bit  closer there so we're going to be able to navigate   between previously saved ones using the next and  previous so we'll save an adjustment we'll print   the worksheet here new new adjustment and we're  going to clear the filter okay so now we're ready   to add some icons here so i've got some saved up  so all we're going to do is insert pictures here   and then i'm just going to select holding down the  control all these icons that i'll be using inside   here and then just click insert okay obviously  there need to be size so we're just going to set   0.2 going to be the default size except for that  larger icon that we're going to use for the shape   so i'm just going to drag them over here that  sheet icon obviously i want that bigger so that   one's going to be bigger here that looks fine that  looks sufficient for us and now i'm going to move   these buttons up but let's put these icons here so  i'm going to have this clear filter button you can   barely see them because it's white on the right  i'm going to have this one let's take a look at   that that's our print so i'm going to bring it  over here this one's for our new one i know it's   white you can barely see it this one i'm going to  use that for the clear filter so that one right   we're going to clear the filter so it's going to  use that recycle bin along with the filter so i'm   going to cut that in half the size and then we're  going to drag it down here so right so that's the   clear filter we're using two icons for that and  then another one we have here inside here we have   the save adjustment so let's pull that up zoom  in here's that check box here almost can't see   it there all right we're good to go on that so  i like that so now what we're going to do is   we're going to group these buttons and then we're  going to size them ingly so we can zoom out   and now what i'm going to do is i'm just  going to hold down this and then i'm going to   center that and then i'm going to group them i'm  going to do the same thing for each button here   making sure that they're both centered and then  both grouped and then we're going to do the same   thing for this center it and then group that okay  so now that we have all of our buttons i'm going   to keep the clear filter a little bit over to  the left but i want to space these ingly   although they already are i'm going to hold down  the control and i'm going to go into format and   i'm going to do line and then what i'm going  to do is i'm going to distribute horizontally   it's going to include the spacing there okay i  like that everything is looking good we've got   everything grouped here we've got to group this  one too so i'm going to hold down the control   making sure to select each shape inside there  and then we're going to use we're just going to   be able to group that okay so now we've created  our buttons and what i want to do is i want to   have some filters up here as you saw i want to  have this is going to be called filter by right   not goodbye filter okay so we already have a  drop down list assigned to that as you can see   but i'll go ahead and walk you through that then  we'll also i want to have this one called sort by   then i also want to know the total adjusted so i  want to know that so the total adjusted value so   i'm going to put it here total adjusted value  then i'm going to right justify as it already   is so that total total adjusted value is going  to go directly inside here all right so that's   going to be sufficient so this one so let's say  sort by this one and our filter by should be   colored in white those are going to be user  entered so we're going to close those white   then i'm going to format those using our same  border that we've been using consistently and then   i'm going to use a dotted line on the left side  and i'm going to use the solid on the top right   and bottom okay it's already on the bottom okay  i like that that's going to give it now ing   to this we just want to use the filter by and this  and not this one the filter by the sort by and the   total adjusted value here i also want to give  those format those cells so we're going to use   those borders we're just going to go all the way  around here and then use the dotted on the right   side so we're consistent and then we're going  to click ok all right so i like that this total   adjusted value right here i want to format that  cells i want to put the border on top but i want   to i don't want to put that one in in white that's  going to be a formula so i want to make sure that   that doesn't go white because the user will  not be entering anything in this adjusted value   okay i like that there these are not part of  the header so we can i'm going to drop this down   here so i don't need that so we don't need the  header to extend beyond that it's not necessary   so all right we're good to there let's save our  work and now what we're going to do is we're going   to just continue we can bring these button sets up  a little bit so that they're balanced but i think   we're good to go on the original design i do want  to add some conditional formatting in here as we   add data in here we want to make sure to do that  so i'll do the unselection just bring these up   a little bit here and then we can all right that  look good right there all right so what i want to   do now is i want to make sure that we're going  to add in data but let's add some information   inside here okay so how we going to do that well  what i want to know is i want to know what sort   column this is going to help us when we know  which corner we'll be using this for this so   i'm going to put a sort column based on that sort  and the second one i want to know if there's been   changes this we may or may not use but i want  to know and i'll explain why and it's up to you   how you want to do it i'll explain some  differences whether we want to know whether   the user has made changes or not so it could be  important we'll go over some ideas together so   i also want to know the adjusted id if it's been  saved before it's going to contain an id 1 2 or 3.   so that id is going to go directly inside b3  also i want to know if it's been saved before   i want to know what row adjusted id row i'm going  to put that here and i also want to know the next   id number so i'm going to get borders around these  and then we're going to we're going to go through   just a few formulas and some named ranges  so if you go into the formulas name manager   and you see i've got i've created a few just  to help us move along i've got an adjustment id   now if we look at that it's an offset formula  based on the adjustments id one two three right so   that's an offset so they're tied so as the  adjustment ids increase so does this formula   if we tab out we can see the dancing ants are on  one two and three there i have the same thing for   item id i wanna know know created all the item ids  and i also have an entire one for the item data   we're going to be applying that item data and i'll  show you so this named range item data encompasses   all 16 columns of all the item data and as many  rows as we have so it's an offset formula using   a dynamic number of rows using count a and all 16  columns so this particular named range encompasses   all data okay i don't do that too often because  we then have to set the columns but sometimes it's   important and i'll explain why okay so we're going  to the data as we enter the data the data is going   to come in here in other words as we list all  the items it's all going to come here item id and   picture are not important as far as the picture  name or the item id so those could be hidden right   so the item name this is important the part number  everything else here is important and so what   i'd like to do is i also want to have a picture  folder right if we're going to be adding pictures   in here remember those pictures we need to set  a folder where are those pictures going to go   so i'm going to put that directly inside let's  say oh i'm going to put in the picture folder   and then i want to have that picture folder here  that label here so i'm just going to copy the   formats here i'm going to use the same formats  here paste special and then paste those formats   not the validation but just the formats so we  have that here so what i want to do is i want to   put that file now you can add a browse button you  could browse for the folder but it's relatively   the same thing as doing this so all i want to do  is take that full file path i'm going to copy that   and then i'm going to paste it directly inside  p4 okay so now we have our our full file path   directly located in p4 so once we have that  we're going to be able to map those pictures   to that specific folder all right so then we  can get a full file path using the picture   name now that picture name is going to  be here that combined with this folder   will provide us the picture and then we can place  that picture directly inside here but i also want   to add some conditional formatting as i add name  whether it's based on here as i add item names   i want conditional formatting here based on that  so how we going to do that well the first thing   is i want to place all of those let's say all  the way through m and down i'm going to add some   conditional formatting so i'm going to go into  the home and then conditional formatting i'm   going to add a new rule it's going to based on  a few conditions so it's going to based on what   conditions well it's going to be based on two  conditions i want to know is there a value in   column d and is it an odd or even row because i  want to color them ingly so it's going to   be based on and equals and and what is the first  one is there a value in d and the row but it's not   only row 7 it's every single row that we're  applying so i'm going to remove the dollar   sign the absolute there and so i want to make sure  that that is not equal to empty and i also want to   make sure that it is an even row so in this one i  want even rows so that's mod formula we're going   to use that the 0 indicates even rows so what do  i want to color that well i want to color that   let's say that white color i'm going to give it a  white color so i'm going to format that i'm going   to give it a fill of white and then i want to give  it a light border something relatively light but   you know enough so i'm going to use our same color  here probably let's say a little bit lighter color   here and then just give it that light dotted line  all the way around so that's going to give it the   border now what i'm going to do is i'm going to  copy this and i'm going to ctrl c and click ok   i certainly want to apply that to all of them  so when we manage rules we need to do more rows   so i'll do 999 i want to add one more row i want  to add one more conditional format because we've   only worked on the even rows but what about the  odd rows so we're going to go into new rule and   this is why copying that formula is so easy i'm  going to paste it in right here but this time   we want odd row so i'm going to do that 1 here  and now i'm going to give it a format i'm going   to give it a fill i'm going to use our consistent  color here something like that and then that's it   i also want to give it the same consistent borders  that we did with the other one so believe it was   this color we used here and i want to go all the  way around just like we did here and click ok   and click apply okay and click ok all right so  now we see we've got so now as we add items you   see our conditional formatting applies here let's  double check this this didn't apply so notice that   it didn't apply so we just make sure we have to  update the range the apply to range for the full   compass so managing those rules we need to make  sure that we're going to copy this applies to   to the entire range too click apply okay good so  now we've got our conditional formatting i like   that so as we add items our grid will grow out  and it looks kind of nice so the macro is going   to take care of that it's going to take care  of adding items from here into our inventory   worksheet based on maybe a specified filter  so now let's take a look at this i've got some   there's no specific range tied this but what i  want there's no named range here notice i'm just   clicking here but what i do is i want to set a  named range right what do i want to do i want to   basically filter by any of these headers right  pretty much any of these headers here i want to   filter by so if i take this right and let's just  say we call it headers right or we can call it   if we go into the formula and name manager we  see we've got one called headers already here   i've created it it's based on that range just to  make things easier so all i need to do is go into   here into the data validation here and then just  make sure that etc i had a few different ones this   we're just going to call it equals headers if you  want to find it you can use f3 to find it now when   we do that you see we have that here so we can  filter now what if we want to sort by any of those   well sorting by i'm just going to copy and paste  the and paste it all there so now we can also sort   by headers right we can sort by item name we can  sort by part of course of course we need to to to   do that and we need to add some code to make that  happen but that's you get the idea okay great i   also want to know the value the adjusted value  we get to a formula in here i'm also going to   be bringing down formulas here and here and i'll  show you how that's going to work but what we want   to do now is we want to get that data into that so  how are we going to do that we're going to do that   with a macro we only have a few formulas let's add  in some in the admin columns here so what i want   to know here is i will put in let's say we have an  adjustment id we have our adjustments here one two   and three so i want to know if it's three i want  to know that that's on row four so if i put in the   id here i want to know what row it's on and i want  to place that row directly inside b4 so we can use   a match formula so we're going to do equals and  then if air we don't want to show anything and   match what do i want to match i want to look up  whatever is here and i want to look it up what   is the range i'm going to look up and of course  that's going to be our adjustment id here i want   an exact match but i want the row number so those  row numbers they start on three so i'm going to   add 2 to that if it's an error i just want to show  blank so that means adjustment id row 3 is on row   5. double check that here and we see that row 3 is  on 5. that's what i want i also want to know the   next available id we know it's for because we just  looked at that but how do we know in a formula   well we can use equals if air again we use the  max formula we're going to max i want to know the   maximum adjustments id but not just the maximum i  want to know the next one so i'm going to add plus   one however if there's an error well why would  there be an error well there'd be an error if   there's no data and if that's the case i just want  to show one okay so the next available is four   okay perfect so we have that and then what i want  to know is the sort column right what i want to do   is maybe we'll get into this in a little bit as  soon as i show that we don't need that quite now   but i'll get into this formula but let's just get  these items loaded in here saving our work here   and then we'll go into the item so basically what  i want to do is i want a macro that's going to   basically load these items in but it's got to be  based on some filters right we have filters and   sorts here and we're going to use some criteria  here so i want to base it on certain criteria   right i want to filter it maybe we're going to  filter by some certain let's extend this around   let's put this one here i need to know what we're  going to be filtering it by so let's add this   it's going to be white so the idea is this if i  want the user to enter some sort of a filter we   want to make sure that they can enter some text  so if they're going to filter let's say they want   to filter by item name and then they put the item  name in here this is going to be that filter just   as you saw in the sample so that's what filter by  and then whatever however if it's like item cost   maybe they're going to do something like greater  greater than 30 right so it's going to be kind of   different so the idea is a little bit different  based on the filter if it's an item name a part   number a category or a description those are all  those first four are all text text based types of   filters and that means if the item name contains  this if the part contains or the category contains   some text then i wanted to show it however item  cost and quality and stock are both numerical   types of filters so they need to be treated a  little bit differently so what do i want here the   first thing this is our criteria item cost right  this is our what we're going to have our criteria   there's our criteria header and our criteria our  actual criteria so i need to make sure that when   we run an advanced filter our header is this e4  and our the results our filter is going to be here   so inside our items i need to do that right here  so what i need to know inside s2 our criteria is   basically whatever is in e4 right whatever's  in the inventory worksheet e4 therefore if i   change it here and i change that to item cost to  quantity in stock and i go back to items we'll   see that that's automatically changed then we  have the results now we need to differentiate   is this greater than 30 but what if it's a text  based filter what if i'm filtering let's say i   want to know the item name and i want to know  any item name that contains the word bath right   in that case what i want to do is i want to put  those wild cards both before and after the text   so they're treated differently that filter is  treated differently based on the type of that   so if we know that right and we know which four  are text and we know which two are numerical then   we can make decisions based on that and we can do  that just right here if first of all if inventory   f4 equals empty then show does not equal why would  f4 be empty well f4 is right here if the user has   not entered anything there then i want to make  sure to have something like does not equal empty   so that all we need to do is just enter less than  or greater than and that's going to contain so   every field that doesn't contain empty will show  up here so we want to make sure that also however   if what if it's going to be quantity in stock  what if we want to deal with those two numericals   those are quantity in stock or item cost in that  case if e4 equals quantity in stock or it equals   item cost then what i want to show then i want  to show exactly what and i also want to make sure   that inventory worksheet here and so it's an and  so it can be or quantity in stock or item cost but   and it's also separated by and here so and we  want to make sure that f4 does not equal empty   right we want to make sure that we're actually  adding a filter in that case what do i want to   put i want to put whatever's in f4 whatever is  in f4 meaning if let's go back to where we were   item cost greater than 30. so i want to know  whatever whatever's in f4 we are going to place   directly in here exactly as it is however if it's  a text base we need to put the asterisk before   and after it right just like we had here so that's  what we don't want to so we can't expect the user   to put in the asterisk here so we need to do  it through a formula and we're going to do that   through here so now otherwise here's our otherwise  right here else here's if it here's if it's true   and here if it's if it's false if it's false then  what do i want to do that means it's not quantity   in stock and it's not item cost not either one of  those then it's a text base so then i want to add   an asterisk before then i want to enter whatever  is in f4 and then i want to add an asterisk after   both of those instances so that's for text space  so we have a single filter handling both text   based and numerical type filters when we run the  advanced filter this will be our criteria it's a   dynamic header dynamic contents our results will  come in here we then want to take these results   and we want to bring them directly inside here  starting all the way on column a and bring it   all the way down here up to quantity in stock the  barcode the picture our actual quantity will be   entered by the user and our adjustment will be a  formula so i'm going to need a formula here and   i'm going to need a formula here a formula i'll  show you explain why the formula we need for a bar   card and we're going to need a font on that okay  so how do we do that well first let's go ahead and   get that data in here and we're going to write a  macro to do just that so we can pop this up here   we're essentially done most of the formatting and  inside the developer using alt f11 is a shortcut   there will get us into that and i want to write a  few macros now i've got some information here just   to help us the macros we're going to be writing  but i've got some guidance that's going to help   we're going to start off with load items and then  we're going to go to add new after that but i do   have some dimension variables already picture  folder as string picture path a string we're   going to need those for picture for last row the  item row last results row adjustment database row   adjustment row item database row adjusted id is  long and the sort column is long and then the   picture shape as a shape great so let's focus on  this second macro here and that's going to be the   inventory adjust and load item so this is where  we want to load all the items based on a filter   and then we're going to sort it ingly right  we want to sort it we're going to also have to add   in the sort so let's go ahead and do it we're  focusing primarily in the beginning with the   inventory worksheet so with inventory worksheet  just to make sure you have the sheet right put   the dot if the intellisense shows up that means  we have the correct sheet name okay the first   thing what i want to do is i want to know if the  user has made a changes and here's here's where   we could run into issues and something that i'd  like to recognize let's say the user is making   some changes on here they're putting the actual  quantity right as opposed to the quantity in stock   and then they click new adjustment if they click  new adjustment everything's going to erase right   it's going to clear out because they're new  but what if what if they clicked it by accident   i kind of want to give them a way out saying  hey you've made changes here do you want to   save those changes can you imagine they have  500 items they've made a change to all 500 or   and then they accidentally kicked the wrong button  everything they just did is gone right so we have   to understand how a user could work what kind of  mistakes they could make and they might click a   button by accident we want to give them a way out  so to do that or when they're loading a you know   when they're loading new adjustments this is the  macro that we're going to tie to that so we want   to anticipate what mistakes they could make and  then help them on that so to do that we'll just   give them an out so what i want to do is when a  user makes a change here we're going to set this   to true changes that means they've made changes  right now let's say it's false as soon as they   make changes to anything there but those changes  only come on a load when those changes are made   right i want to make sure the user knows hey b 2  is true you've made changes are you sure you want   to erase all those changes so that's the first  thing we're going to do inside this macro check   to see if b2 is true so back inside the vba code  we're going to do just that so if dot range b2 dot   value equals true then we're going to let the user  know with a message box changes have been made to   this adjustment are you sure you want to clear  these changes without saving because that's very   important right put a question mark there and of  course it's going to be a vb yes or no question   and then we'll give it a title do changes not  saved then we sell equals vb if it equals what vb   no let's say then we want to say unsafe changes  right we want to make sure i think i had to have   to add a parenthesis right here notice that  that didn't come up right sometimes you're   doing intel sets and you make a mistake i do all  the time so it's just the equals notice nothing   came up so i know something's wrong with my  code so that even before i get to the end   i know that i've missed the beginning of  the parentheses here so when i put that   in right and i know now when i put in equals  you see how it comes up you see the difference   so now i know that i've you know messed something  up and you can go back that's a good way to avoid   then right exit the sub right if they don't want  to save those changes are you sure you want to   clear these changes if they say no i'm not sure i  want to clear these changes then exit the sub so   we're exiting out okay all right great so let's  fix that then exit sub we need to we need to go   um back to the beginning make sure we add a if  there if right because if then statement okay   great so then we're going to exit the sub that's  just in case there's any unsaved changes okay now   we can continue with that the first thing what we  want to do is you notice that there was pictures   inside that so we want to make sure that we've  cleared all the pictures and cleared all the   data if there's any existing pictures i also  want to turn off screen application making   that updating default so application screen  updating equals false so we're going to turn   that off and make sure before the end of the  macro we always want to turn that on so we'll   just application and we also want to make sure  there's no exit sub screen updating equals true so   what we don't want to do is exit the sub anywhere  inside here without actually turning on this so we   got to be careful of that okay all right so let's  continue on so once we turn that i want to clear   all the existing data so to do that we're going  to do dot range and then we're going to do   uh clear which data so let's do this which data  i want to clear a7 we're going to start out the   top row all the way through o and then a large row  o and then we'll just go 999 and that'll be fine   okay so we've cleared that and not value but  dot clear contents okay so we're clearing out   all the content so we don't want to use clear  that would clear the formats out clear all   existing data once we have that now what i want  to do is i want to remove the existing pictures   and i want to give them a specific name once  i assign those pictures specific name i can   remove them based on that specific name now  you'll see picture shape i've got that as a   shape so we can do a use a for each loop four  each pick shape in right dot shapes right so   for every next picture shape close our loop  okay so inside that loop what do we want i   want to check for a specific type of shape  i would not every shape if using in string   in this case picture we'll call picture shape dot  name we're looking for a specific name and what am   i look i'm going to call it item pick okay if it's  greater than zero meaning it meaning it does that   is inside the name then i want to delete the  shape then pick shape dot delete right delete   picture shape okay so that's all that's going to  delete all the picture shapes let's just fix that   up don't forget the then don't forget the then is  greater than zero then okay now we got it correct   correct okay so now that we have that what i want  to do is i also want to set a start column now   what is that start column going to be let's add  that in well first of all we haven't done it yet   but i'm going to show you exactly how that is  okay so we're going to set the source excuse me   sort column the sort column i don't need to what  column equals dot range just set it to b1 and then   i'm going to add that formula in the b1 that's  going to be our set to sort on sort set sort call   okay so the idea is this once i have the data  inside here inside our results i then want to sort   it based on those results so we're going to run  our advanced filter we're going to get all those   results and then i want to sort those results now  you notice that our first possible sort is item   name right the first possible one we sort right  the first one is item name item name is right here   i want to sort based on that so if they choose  item name that's going to be our first one right   our first one so remember we have a name branch  called headers so if i use so let's say equals   match will wrapped around if air which i always  like we're going to run a match right and now   i want to match i'm looking up what they've  chosen here i'm going to base it on headers   okay and i also want an exact match if it's  an error just show blank so right if they show   name if the part number is going to be 2 category  is going to be 3. okay however i don't really want   the 1. what do i want i want to know what column  we're sorting by for example item name what column   is this if i look down here equals column i know  this is column 30 so we're going to sort by column   30. how do we get column 30 from item name or how  do we get column 31 from part number well all i   need to do is add 29 to this so if i go here and  i hit plus 29 i will always know what column we're   sorting on if we're sorting by any name it's going  to be so column 30 and if it's going to be part   number it's going to be 31. great so now i know  what column that way when we get our results here   we have already filtered them i want to know  what column to sort it's going to be one of   these columns right here how do we know which one  so now we've got the column and i know the row   so we're going to set that into a variable just  like we did here so b1 is our sort column number   so we've got that we've put that into a variable  inside our code so we've done that already   now continuing on with the code sort column once  we have that that's it for our worksheet for a   while we're going to work with the data so right  i want to work with the data so we've covered   everything for our inventory worksheet for a  bit now what we're going to do is we're going   to focus on the data so that's our item data so  with items just to check item data that's what   we're going to do first of all i want to know  what the last row is the last row of the data   is going to be based on and excel up okay so our  last row of data i used auto hotkey to make that   really fast all the hotkeys are free software you  can get it okay so what if our last row is less   than 3 then we're going to exit the sub there it  is again be careful of this exit the sub we can't   so let's say we we want to we don't want to exit  the sub we got to make this true so you can go to   something like this then go to something like no  data right then what you do is you put no data up   here so everything is skipped and we can skip to  that so be careful right we cannot exit the sub   without app without updating this now we could say  then and then put this in here then put copy this   and put it here and then use an end if but this  is a little bit quicker just easier okay great   so we have that that means there's no data and  we'll do that with the results too all right so   we've got our last row now we're ready to run our  advanced filter so our advanced filter is going   to be all right right here so once we have our  advancer where are we going to run this data let's   take a look at the data and we're going to bring  this down and take a look directly at that data   inside this sheet we're going to start it out on  now notice we have a blank column here why do we   have a blank column here because our results also  contain a blank column column c so i want to bring   all the data over into one shot so if i've got  a blank column here item id picture blank column   i want our results to match that item id picture  blank column if our results contain a blank column   so must our original data our original data must  contain a blank column so how do we do that we're   going to run our blank our original data all the  way to q because it contains a blank so it's going   to go all the way from a2 through q in the last  row so let's write that in inside our code here   so we can update that so all the way from a2  through q is going to in our last row once we   have that what about our criteria our criteria is  going to be s2 i use autohotkey to make that quick   through s3 that's our criteria what about our  results our results are going to come directly in   all from a a through a j a a three a a a two all  the way through a j two so we can update that   inside the code right here so a a2 through aj2  all right so now what i want to do is i want to   determine the last results row of our results so  how do we get that so last results row is going   to be equal to not a a oh yeah it is a that will  work a a okay that's our last results row what   if it's less than three if our last results row  is less than three then we know that we have to   exit the sub again not exit the sub but go to node  data so we can copy this and go to node data right   if there's no results if it's less than three that  means we have no results so we cannot continue on   okay so now that we know we've got results what  i'm ready to do is bring over those results but   before we do that we certainly need to run that  sort right we have to sort data based on whatever   the user has decided okay so now we're going to  run our sort so i'm going to put a comment here   run sort okay so with dot sort we're going to  run that sort sort and everything we're going to   do is inside here now so with the sort what do  i want to do the first thing what i want to do   is i want to clear that sort so sort fields let's  bring this up here so you can see it a little bit   so sort fields dot clear so clearing that clearing  out any previous sorts the next thing what i want   to do is i want to make sure that we're going  to add a key so dot sort fields then dot add   we're going to add a key so what is that key that  key is going to be equal to focus on items right   items but we can't use range because both the  row and the column are dynamic so in this case   we'll say dot cells and what is that row we know  the row already it's going to be that first row of   data that first row of data is right here 3 right  here on 3. so we know if we know the first row of   data is on 3 then we can continue on so 3 is that  first row data what column are we sorting this   is where that variable comes in handy that column  that we're sorting is the sort column the one that   we've divided up here okay so we have that now  what are we going to sort on we're sorting on what   values so we're going to sort on equals excel  sort on values and then what i want to do i want   to set the order so what is that order going to  be that order is going to be based on extending   xl ascending great and then i also want to undo  the data option data option is going to be normal   right so equals excel normal sorting on that data  and that's it so it's going to sort based on user   entry okay so whatever this sort once we said  that we want to set the range so the set the range   is going to be equal to the items we need to call  out that sheet because we're inside to sort with   dot range here we can use range because we know  it's going to be aa3 and then all the way to the   end our last one a let's take a look at this so  we know the last one here a j so aj is our last   row i'll bring this up here all the way to the top  okay so now we've got both our sort range so a j   and what what is the last row and the last results  are a last result row okay so great so now we've   got our range that we've set and now we're just  simply ready to apply it so dot apply is going to   be our range so now we can apply it so that's the  end of our sort so that'll automatically sort it   once it's sorted we are then ready to bring in  our data so let's do that right now so we're   going to bring in the data so we're going to focus  again on our invoice worksheet invoice worksheet   then what range do we have we're going to start  of course in range a 7 that's our first row a7   and where's it gonna go all the way through  j and the last results row plus four and   last result row plus four why are we adding  four because our results here start on row   three here right and our results start on row  seven so we need to compensate for that so we   do that dot value is equal to based on our result  data so our result data is going to be dot range   aa3 same as our range a3 all the way through a j  in the last results row and the last result row   this is going to bring over our data okay bring  over data but not everything we still need to   bring over a little bit more over data so what  else do we need to bring i need to bring over   the item database row so bring over item data but  also what else so that's going to bring it all the   way over from a through j but we still have one  monocum right so here's our information it's gonna   bring it all the way up from a all the way through  j so the barcodes here are a through i actually   sorry a through i here so once we have that in  stock i also want to know the barcode is going   to be generated the picture the actual quantity  the value adjustment that's all we don't need that   but i do want to bring in this item database  around column n so let's do that so it's going   to go to i quantity and stock is i so that's  the one the last one we're going to bring   barcode will be generated using vba so no  problem on that okay so how are we going   to do that well we can just update that with  one more item so this is going to be i not j   alright and it's going to come all the way making  sure that we have that here and through i and our   last column is j so that row aj is going to be  brought directly over inside here n so let's   do that with one line of code so all we need just  to bring on we can just copy the existing one and   make the other adjustments so let's just copy this  so we can do a little bit less writing paste it in   here and update the arrow so right this is going  to come directly from this it should be i here and   this should be j and this should be j okay so so j  aj through aj in the last row so we have that will   bring it over and it's going to go directly into n  column and here's where it's coming from not a and   n okay so we have that there so now we're going  to do is all just one that column this shouldn't   be a i this is oh correct a i it should be just  i not a i so a7 through i in the last row that   looks correct all the way through aa through  ai and then n7 gonna bring j so that's going   to bring in all of our data bring the last one is  going to be the database database rows bring over   item database row okay so that's it let's saving  our work okay we're going to run this as a test   and see how it goes but we also want to do let's  bring over let's see how that goes check for any   issues on that and we'll just update that excel  sort number not excel number excel sort that'll   be fine and then just continue on and okay let's  take a look inside our original and see how we are   okay let's take a look here we've got our ids here  we've got based on our results based on the bath   right we had a filter set up so it's only those  rules part number assembly bathroom okay so let's   get that barcode in there now to do the barcode  what we really want to do is use a barcode font   now i've provided one already in fact we're going  to use what's called free three of nine now if you   want to use that i've got this barcode font called  free through night and i'm gonna supply it inside   if you don't have it i'll it's free of course you  can download from the internet or i also have it   available on patreon so i'll make sure to include  that free three of nine and if i use that as a   font here it works great but what i want to do is  i want to make it based on that part number that   barcode is going to be based on that barcode now  using free 3 of 9 to use it properly we need to   add an asterisk before and after it so for example  equals right and we do uh asterisk let's put in   quotations marks asterisk and then what we want  is another end quote and we want the part number   then and and we want another asterisk in there so  it's got to be separated by asterisks both before   and after and that's going to give us our correct  barcode this is correctly related but what i don't   want to do is i don't want to have this formula i  like to be able to clear it out so i want to put   this formula somewhere where i can bring it down  so that's what i'm going to do i'm just going to   copy this formula here and i'm going to paste that  directly up here paste it directly inside here   and we're going to show now let me clear that i've  got a format here but i don't i'm going to put   general because i want to show it to you i already  had it there we go i'll show you how to hide that   okay i also want to formulate here i want to know  the value right we're going to put a picture here   if for example if the actual value let's say i've  got one here or let's say i have two here but   our quantity stock shows zero i want to make sure  that we reduce our value by how much how what's   the value adjustment so it's going to be basically  a formula to do that right it's our total   equals so it's basically this equals this  minus this so our our current quantity   minus our quantity in stock multiplied it by  the price right by how what is the price here   so the price is this cost here so that's going  to give us our actual adjustment right where   if we show we have four in stock but where actual  quantity is only two that's a loss of 798 dollars   so what i want to do is i want to take this and i  want to place it inside here place it inside here   basically all the cells but again i don't want to  place it i want to be able to clear out the data   so how do i do that well what again i want to do  is i'm going to place it directly up here and then   i'm going to use vba to bring it all the way down  so i'm going to copy this here and i'm just going   to place that directly inside here and again what  i want to do is i want to show this so here's what   it should like so how do we hide this now we've  used conditional formatting to hide in the past   but what i want to do is i want to hide it so i'm  going to hold down the control and i'm going to   give it a custom format just like you saw a minute  ago but you didn't know what it was and we're   going to go custom and then what i'm going to do  is i'm going to add a specific format and which   is going to be three semicolons one two three  semicolons enter and that's going to automatically   hide it very cool huh i haven't used that before  three semicolons will automatically hide it right   conditional formatting is another way so now  it's hidden now we've got those formulas hidden   of course you if you're you would protect these  cells and so now all we need to do is use vba to   bring down these formulas and it'll populate both  the adjustment and this that way as soon as they   add in an actual quantity it&