How To Create Your Own Asset Manager With Check-In/Check-Out & Depreciation In Excel [Free Download]

hello this is randy with excel for freelancers and welcome to the asset and tool manager in this week i'm going to show you how every single step how you can create your own asset and tool manager complete with automated filtering we're going to clean with adjustments and repairs even automated depreciation along with check in and check out by staff it's going to be an incredible training i can't wait to share with you so let's get started all right thanks so much for joining me today i've got a really fantastic training a highly requested training the asset and tool manager this application i'm going to walk you step by step through the entire application by the end of this training you're going to know how to create your own asset and tool manager complete with filters check in and check out by staff checking in by location creating automated adjustments depreciation adding your own repairs in saving repairs having that and creating this last 12 months adjustment so we can see each one for each item so we've got a lot to cover today so we're going to get started right away before i do that i want to remind you that we do create these each and every tuesday just for you this application is absolutely free all you need to do is click on the links down in the description using your email or facebook messenger and we're going to get that sent over to you right away i've got something brand new coming up very very soon you can check out the links below and that is our patreon account a highly requested patreon and what that's going to do is give you a lot more benefits things that you've been asking for like how do we get all these icons and pictures from the training or how do we get this in a beautiful pdf all the code very very detailed line by line in a downloadable pdf document i'm gonna have that for you in our patreon account and that's gonna help support us we've got so many benefits that we're gonna be adding to that including a discord we're gonna be able to do live chat and even better than the facebook group i've also got downloadable videos if you want this video and you want to download it watch anytime that's going to be available on our patreon along with a host of benefits you'll be able to request your own training what you want will put that as a high priority once you become a patreon member so that's scary go ahead and click the link down below patreon excel for freelancers you can find this here get signed up we're going to be adding a lot more we're just getting started with that all right let's get started on this training because i got so much to cover this particular training has so many features tons and tons of features tons of code so the best way that i can bring that to you and share everything with you is to basically show you what i have done and that's going to enable me to go line by line every line slowly without having to type it in now i do love to create these for you from scratch many times but when we have so many comprehensive features we've got tabs we've got filters we've got adding new we've got automated transactions we've got so many things going on with this the best way for me to do that is to show you lineball and how i did it so that's just what i'm going to do today for you it's still going to be a long training even when i go to if it would be a six or seven hour training if i were going to do it line by line so we can't do that but oliver i'll go over to everything with you we'll go over an overview of the what does this contain why do we have it why is this important well for any type of company or home you want to track your assets you want to know what kind of assets you have you may have a big company or you just may be running your house you want to know how if you got a car or home you want to track those assets you also want to be able to track the appreciation if the value goes up a certain percentage every year a certain amount every year or you also want to track depreciation something like office equipment or automobiles they depreciate over time unless you have classic cars something like that but generally there's a lot of assets that an asset is something you have you also sometimes if we have assets we may want to rent those out or check those out to people if you've got a company and they're taking the company laptops home you want to know who has those laptops and when they're due back so we want to be able to be or there might be a specific job site or a location where they're bringing those to so we want to track those assets we want to know where they're going we want to know how much they're worth we want to know who has them and we also want some details want to know the condition of it so this application is going to be able to do all of that and a whole lot more so many features so let's get started on this let's go over so we've got an admin screen and basically what we have is we got a location now you noticed here in the asset manager we do have a picture of an item i'm going to show you a brand new way how to do these pictures like we've done before but something with a new twist so we're going to be doing that so we've got pictures both for the item or the acid i should probably call that asset i'm going to go ahead and show you the headings and we want to show the formula bar for the training i was just using that we'll call this asset picture it's more like an asset but it's also an item okay so we've got an asset picture we've also got a staff picture here so we need to store those somewhere we need to be able to pull those out what we don't want to do is inside our database is track the entire folder path notice here inside our picture here all we have is a picture name individual names of those pictures what we don't want is the entire file path here so that way when you get this right and you want to try it out all you need to do is put in your own folder path here or your folder path for the staff picture and the great thing about it with our patreon account now is you're going to be able to download not only the application for free which you can get already but you're going to be able to get all the icons all the pictures everything that went into this training and that's going to be for as low as three dollars a month so i hope you'll get on that all right so we've got vendors a list i've got a vendor list that's going to keep track of under just a vendor list we've got locations i want to keep track of the locations we also have types what type of an asset is it and we also want to know what condition it's in along with the status is it checked out is it available is it broken is it in repair you know what is its status is it available also we have a job site if you're going to be taking these assets to a location you're checking them out to staff and staff or bringing them to a certain job site or area we want to track that and also what i want to do is i want to know repair for if that item needs to be repaired i want to set a repair type is it maintenance is it cleaning is repair i want to associate certain costs with those items and to do that we can have repair or adjustment type and we also have depreciation and appreciation depreciation is basically the deduction of the asset the value of an asset deducted on a year by year so you basically take the current value of an asset and it gets depreciated over time certain assets aren't worth when you buy them they're generally worth less and less unless they appreciate some assets like houses or land which our assets can appreciate over time so keep that in mind we also have repairs maintenance and cleaning you can add them and we also have a search by this is a really great feature because we'll be able to do a search by it's called a dynamic search by so we want to search for an item we can search by item number by notes by asset name so if i want to do an asset name and i want to do something like computer i can search all those assets with the assets name of computer and then just click refresh and it's going to show that we have one asset name called computer and so it's a great way if we want to search for notes or condition we want to know all those that may be for the good condition and we want to refresh that it's going to allow us to do that so it's going to be a dynamic filter that we're going to be able to have here so i'll show you how we're going to do that and also we may want to know a certain status if we want to filter by a certain status we want to know only those that are available or only those that are on a certain location we can do that and refresh that and if we had any data i don't think there's any data on there not on the first floor but we have it available on the we would fix that up on the first floor we do have one all right so we'll fix that up but what we want to do is we want to make sure we show the dynamic filter on that let's see if it works for third floor i think we'll need to update that no not working yet we'll get that working during the training so we'll see what why that is and how that can help and if you want to clear all those filters just click the clear button so what we have is each one of these lists now we have dynamic named ranges that are used in these lists and let's familiarize yourself with some of those dynamic named ranges so we're going to click the name manager here and dynamic meaning basically when we add items they grow so if we take a look at our first one vendors here and we tab over and we see that it's three and we want that on drop down list because we don't want to scroll a bunch of blank empty spaces so what we want is a dynamic drop down list we're going to use the offset if we zoom in here we see that we have the offset and it's going to start out in b7 right the first available one in this case we're first available we're going to skip we don't want to go down any rows or over any columns but we want to also know how many we're going to be counting we're going to use the count a we're going to go all the way to 33 so it's going to leave us 26 or so available and then we want a single column on this that's called offset a dynamic named range and we've done the same thing for locations so for everything here locations conditions we have dynamic named ranges using the offset for each one of these also what we have i've set up one more name branch for us two more one for depreciation we're going to call that depreciation type i'm going to be using that in formula so when we name it like that it's much easier to recognize as opposed to using the whole text depreciation so we can do depreciation and we've done the same thing for appreciation type because i'm going to be using these in formulas this one's called app type so we see that there that is it for the admin screen of course you're welcome to download this and add as many as you like don't forget to subscribe and uh you can get these downloads automatically in your email so that's excel for freelancers.com go ahead and put your name and email and i'll send you these every week automatically you won't even have to download them all right so also inside our access database this is the database of all of our assets and everything that's contained so we have an asset id the name of the asset an item id manufacturer model serial and type condition location of status and then of course that picture that we saw and also we have the checked out by who's it checked out what date was it checked out on and when is it due to be returned is there a location that it was checked out to and also we want to know the original cost of the item and also i want to know if we're going to be depreciating that item or we're going to be appreciating that item we can set that up in here whether it's automated or depreciated so if we have automated added value what we can do is we can say okay i want to this one here let's say appreciation i want to do it every one year let's say it's a home so we can do every one year i want to appreciate that by like let's say two percent so we can do that here if it's not a percent we can do that so what we have to do is just save the asset we can also go in here let's expand that column just a little bit there okay so ten dollars so we can also increase it here let's so two percent we can increase that if we want to increase that we can also see an amount if we notice this we may want to increase it or decrease it by a certain amount every month or you know we may want to depreciate or appreciate every month aprician will be adding to the value while depreciating will be deducting the value of it and basically what we want to do is deduct it completely so we've got a lot to cover here so that's going to be there also we have the total expenses i'm going to make this update i'd like to know instead of total expenses what i'd like to know is the total appreciation or depreciation based on that and then i'd like to know the total repairs and i'm going to walk you through we're gonna do those formulas together they were put out on this repairs or let's just say repair or adjustment right we wanna know the adjustments that were made and we've got a really really cool town feature that's gonna let us know all the repairs and all the adjustments for a specific item here so here we've got a whole history notice that we have a repair here and so we've got a lot of features here that we can do and we'll be able to delete those and add new and save them so we've got that and so within our asset database we've got this so we want to know of course if the audit whether it's going to be depreciated the quantity the frequency whether it's years or month the percentage or amount are we decreasing it are we are we appreciating at a certain percentage are we depreciating a certain amount or are we making an adjustment based on amount so notice this would be a 10 amount while the other is a percentage i want to know the last adjustment date if there's any notes and i want to know the next adjustment data if it's going to be automatically adjusted based on a date and also want to know the current value now the current value is simply the original cost minus or plus you could say plus or minus either way based on the depreciation we're going to use plus and we're going to use negative adjustments so basically it's going to be the original cost along with all the depreciation so subtracting any depreciation adding any appreciation of course deducting any repairs or maintenance made on that if we want to add that in but the value of the current value we're just in this case we're only going to use those appreciation and depreciation so that's the only ones that are going to be associated with the current value because i want to know the current value of it when it comes into costs that will come in separately so then we also want another row okay so i also want to keep the track of those total depreciations and of course any repairs that we might have in there i want to keep track of those as well so the repairs and the depreciation appreciation are all kept in the adjustments and repairs database so they're all kept here and if we do have a repair or we would like to know a vendor on that so we can keep track of that now keep in mind that the vendors here so if we add in let's say i want to add in the date 8 15 and i want to select a specific vendor maybe we had this repaired at the it supply center and we have a specific let's say repair and we want we had a 25 dollars we had a repair and then just say let's say keyboard cleaning and then what we can do is we just save the repair what that's going to do it's going to add it all the way to this list so we now have it this item on this list automatically right here and so it's now tracked okay and it's also tracked here also it's going to be tracked here on our specific adjustments so we know that the last 12 months of all the adjustments and repairs that have been made for this particular part so we have all that and it's based on this database the adjustment and repairs database and lastly all we have is a staff database that contains a staff name email and phone in the picture okay so that's an overview now let's get into some of the things on how we actually did that and we also wanted to go over one more thing and i want to show you exactly how we're going to be able to actually add a new asset right we'll be able to clear the fields and add a new asset clearing everything or what we can do is just cancel new and what that's going to do is just select the first available so we have that when we make a selection on a specific asset it's going to load that information inside that and i'm going to walk you and show you exactly how we did that and we're going to use data mapping for that now data mapping is something that you may have seen before in my videos and it's very very helpful and it cuts down on a lot of code it will notice that this name is based on cell l6 while the manufacturer is based on l7 so what we want to do is we want to map these fields to our database here here's our database so when we look in that specific column asset name we see up here in row one l six we also see the manufacturer here in column d l7 so what we do is we simply map those cells to whichever column and that's going to allow us to automatically bring in them with just a few lines of code so what i'm going to do is i'm going to run a loop from column 2. i'm going to go all the way over to the last mapping right here which in this case is column 24 and i'm going to map those so for whatever is in l11 on this i'm going to bring that into the row here whatever's in the row here i'm going to place it directly in here and that's what we do when we actually save this sheet form to this database we can also use data mapping when we're actually loading we want to bring this information and we want to load this sheet form with all the data all that we need is the specific row that it's located on once we have that row and i know what row i can then run a loop again from column 2 because asset id will be there and we'll go over that from column 2 all the way to 24 bringing in so basically in this case we are going to take whatever's in our column 2 and we're going to place it inside l6 of this sheet here we're going to take whatever is in in this case column d in this case the fourth column as we're looping through two three four and placing that directly in l7 so that's how that's going to work and we'll go over a little bit detail once we get in the code so we're going to be able to load all that information in and each of these fields is already mapped out for you okay great and also what we'll be able to do is if we have a let's go ahead and here this particular and hasn't been checked out yet so what we want to do is i'm going to i still need to clear this idea when we when we select and i'll make sure that this idea is cleared but we want to do is we want to be able to check out by a certain person right so when i click fred fretters right and i want to select maybe a specific job set i know that fred's going to check it out maybe on this date the current date and now it's fred so if i check it out or if i check harold banks and check in or check out let's go check out and we save that it's automatically going to save and i'll fix that picture so that it automatically updates and so each individual what we do is what i basically want to do is when i select that name i want that picture to change so that way i'm going to be updating that and i'm going to show you exactly how we do that so then make sure that picture is updated now each individual staff is right here so let's do that right now let's get that coded up right now and i'll walk you through it that's going to be based on the change of s15 s15 is what we're going to be changing so that's what we want to trigger so let's go in to the vba and fix that up first and get that done and then we're going to walk you through so that's going to be inside the developers if you don't have the developers you can use alt f11 to get you there we're going to go into visual basic and we've got some code here which we're going to be going over and this is going to be based on the asset manager sheet notice i have an asset manager sheet and we're not going to be focused on selection change right now but we want to focus on when we actually make a change it's called worksheet change now i've got some code there already but we're going to focus on we're going to call this on staff change right because that's what we want when we make a staff change we want to update the picture right so if not if we're actually making change but what cell are we making change to remember let's take a quick look back in there we're actually making a change to s15 so that is the change we want to do s15 so we make a change to s15 we also want to make sure that there's something i want to make sure that there's actually a row associated with right if we put in something incorrect there's not going to be any row associated with that we need to make sure that there's a row a specific staff row that's been associated in this case it's going to be row 6. so to do that we need a formula and i can place that formula inside column b and i've done just that this staff row here located in b17 that's going to be based we're going to be using a map so what i'm going to do is i've got a named range called staff name it's associated with all the staff names is the dynamic named range using offset just as we did before that's going to be based on s15 now i want the row of that right so it's on row six so if this is on row six i need to add two and why is that because our first one starts on row three so if we return one if we run a match and i return one i don't want one i want three i want that third row so what we're gonna do is we're gonna add two in this case the fourth item found is mary but i want row six so i need to add two and that's just we do here so we're going to add two so i know that mary right here is located on row six if i know that then i can determine what mary's picture is and mary's picture the name is called mary.jpg and i know that i can use the index so what i'm going to do is i'm going to index column d3 of the staff d is the column is where those pictures are located so i'm going to index that and i'm going to be basing it on b17 now in this case in this case i don't necessarily need the row i want the number so i'm going to subtract 2 in this case and i want an exact match okay so what that's going to do is going to pull up the mirror if i know that picture if i if b18 is not blank i can then go ahead and add that picture in so let's do that right now so as long as i know that mary has a picture we can do that now keep in mind i've got a square here and that's called staff picture what i'm going to do is i'm going to take that square and i'm going to place that fill with a picture basically what we're going to be doing is if i right click and i format the picture and i want to fill that with a picture all i would do is fill it with a picture and then i'm going to select it from the file and we're going to find that file so if i've got some pictures here and i'll just do it so basically we're going to do that inside vba like this so we have a staff picture remember these are all going to be available to you and i want to put mary here and insert that that's basically what i want to do but i want to do it through vba i want v be able to do it so vba is going to fill that so all we need is the file path now to get that file path i need to go into the admin and i need to know what folder those staff is pictures are located and they're located right here inside c4 so if i have that i'm going to pull that out so let's take a look at that so we're going to dimension the picture folder as string okay and now what i want to do is the picture folder where is it located right and equals admin dot range c4 that's the cell that's located dot value picture folder okay so that's where but now what we want to do is we want the full file name we also need to make sure we cannot add a picture unless we know for absolutely sure that inside that sheet that we have a picture here b18 must not be blank right so we can make sure it is and range inside the sheet itself range b 14 dot value does not equal empty then we can move on b18 sorry b18 there we go okay b18 is not equal empty then we know that there's a picture right so then what i want to do is i want to put that picture name so we're going to dimension the picture files as a string also and so the picture file is going to be equal to what's in b18 so that's the picture file now what i want to do is i want to put the path together right so this is the picture file now i want to put the path together so let's do that picture path as string okay here we're gonna now we got the picture palette okay so now let's build that picture path the picture path is equal to the picture folder we need that backslash on that and backslash and the picture file picture path once we have that picture path now what we want to do is we simply want to update the shape accordingly with that picture so what is that shape name and all we need to do is add the fill so here it's called staff picture now it's also part of a group here called general info group that group gets hidden when we move to the tab so we need to make sure that we're addressing that picture inside the group we want to make sure to do that so it's going to be general info group so we're going to start out with shapes general info group inside that group items and then there's a there's a specific item that is called the staff picture right there so that's the one i want to focus on staff picture staff picture and what do i want to do dot fill and what do i want to fill i want to fill it with a user picture what is that user picture it is called picture path now if you want to make sure what we can do is we can add a just to check on that and error checking on that we can do it something like this if directory of picture path vb directory equals empty then exit sub okay there's nothing to do if there's an issue if that if that's incorrect if this picture path is incorrect for any reason then exit the sub so let's save our work and now what we're going to do is when we make a change to s15 we're going to check on that to make sure we'll change the staff change it to a different person and we see that it's now changed so notice that it's now changed automatically great i'm glad we got that and i'm glad you got to see me program it so we can do that because we'll be doing a little bit of that but more going over because it's such a large application next up all right let me familiarize yourself with a few more named ranges that we've created now as you remember inside these adjustments gonna contain really three types of information we've got depreciation appreciation and repairs or maintenance okay so for those two types what i want to do is i want to use the sum i want to sum i want to know how much depreciation for a specific acid notice we have asset ids these asset ideas are tied to these specific assets here you know so it's id so we've got assets and i want to know which depreciation or appreciation or repairs for a specific asset id i want to make sure that we can total that so to do that we're going to use some named ranges so if we take a look inside our formula in name managers we have something called the adjustment we have an adjustment amount here and the adjustment amount is also dynamic named range tied to the specific amount inside the adjustments we also have an adjustment date and that is the date that it occurred on we also have an adjustment asset id here that is the asset id that's tied to that specific adjustment and also we have an adjustment id okay and also we have an adjustment type i want to know the type depreciation appreciation repair and so on and so forth okay so knowing that what i want to do inside our asset manager here on this what i want to do is i want to determine all of the total appreciation and depreciation every time that it's automatic every time we've created it a per for those specific items every time it's been appreciated or every time it's been depreciated that's going to happen automatically and i'm going to show you the macro how to do that but at when the we run the macro to do that what that's going to do is going to enter that item and a line item right here okay so what i want to do is i want to know all of the depreciations for specific assets so in this particular sample we are on office binders so this is asset id 1 this is asset id 2. so this particular asset what i want to know is all that so i want to know the total appreciation and depreciation for this so we can use the sum ifs for that and that's just what we've done here now keep in mind just as a reminder here this named range is called depreciation type this named range is called appreciation type we're going to be using those inside our formula so let's take a look at that formula this is the formula that we're going to be focused on here the basically it's the sumifs so in that case we always want the sum range first that sum range is going to be the adjustment amount that is that dynamic column that i just showed you and our first criteria range is going to be the asset id i need to make sure that the asset id is the same as what's in b3 that was that one i just showed you so we only want to total those items with the asset id and i also want to only total those adjusted types remember we went over this type with the depot with the depreciation type depreciation type so we're going to total all the depreciations now in this case our depreciations are always a negative amount and our appreciations are always a positive amount so this way we can simply just add them together so we're simply adding in this case again we're going to do the same thing the only difference in this sumifs is this one we're going to be focused on the appreciation type so anything that's been appreciated we're going to simply sum it together so what that's going to do is get us the total so in this case the total is a dollar sixty then i've got an original cost that we entered and we know that our current value is simply our original cost plus a dollar sixty which is going to create a result of a 790. so 950 minus 160 is 790. so keep that in mind that these are negative amounts anything in parenthesis is a negative amount that's based on the format so these are all negatives so depreciations get entered as negative appreciation gets entered as positive that's why we can add them together and repairs in this case are positive because they're considered repairs so we will automatically just know that they're repairs and we can enter them based on that so that's fine repair we know is an expense so keep that in mind all right so they're negative right so it's depreciating the value we want to total up all the repairs in that case okay all right so inside here what we want to do is we want to make sure that we are understanding exactly how we handle it now basically what we want to do is we want to know the total repairs and adjustment how are we going to do that well let's go ahead and write that formula in now basically what i want to do is i want to sum up all of the costs that are associated with the repairs or adjustments but not depreciation and not any appreciation so again that would be some equals sum ifs i'm going to base it on this and what is that sum range again it's going to be the adjustment amount and we still want to make sure we're dealing with one specific adjustment asset id and that ass id is located right here inside b3 so that's it we want to know only one and now what i want to do is i want to make sure it is does not equal i want to make sure that we're summing anything that does not equal the depreciation so we're going to put in the criteria here in this case is going to be the again adjustment type here focused on the adjustment type but we want to make sure it does not equal appreciation it does not equal the mirror so here comma it's going to be in parentheses does not equal and the appreciation type the app type we also want to make sure it doesn't equal the depreciation type so again adjustment type here is anything but is not going to be equal to the depreciation type so again in parentheses does not equal and the depreciation type so basically we want to know any transactions that are the deal that's it that's the sum if right there and that's going to get us 35 dollars so we know that we've had 35 dollars in repairs on this particular item and if we take a look inside and we see that we have 35 dollars in repairs for item one it is not counting anything that has to do with so it's anything else because it could it might be repair it might when we select this item for it may not be repair right it may be maintenance it may be cleaning we just want to make sure it's not depreciation or appreciation all right so i'm glad we got to show you that part so that was really important so that's how we finish out the at least this area here inside the general influence so we got the total so we know how many repairs or adjustments there we could just put in probably repairs here a little more clear repairs i'll keep adjustments for these scene so repairs and adjustments we can adjust that here so total repairs are 35 okay so how do we now we got all this information let's left justify that so it's clear and we also want to know the last adjusted date so that's going to be important because i want to know if we need to make another adjustment so how are we going to do that let's go ahead and focus on just some of the basic macros that are going to cover this and that's something like new asset basically we're going to clear all of the cells so that's called an asset we're going to save we want a new asset so let's get into some of the macros that go with that and that's located in our asset macros here if we scroll up we've got some information here we've got ashley we've dimensioned some information here we've got an add asset picture we want to be able to add that picture if we want to add a picture how would we do that well we click add a picture then we just add a picture here just like that and that's going to add a picture it's going to take that picture name it's going to put it in cue 11. okay so that's the first macro that we're going to go over we'll go in order but i want to make sure that we dimension a few things for the macros here because we're going to use them throughout some of these macros the asset row i want to know what row that asset is on and i want to know the asset column right we need to keep track if we're working with a specific asset i want to know what row it's on and i also want to loop through the columns remember we're going to be looping through and that's going to help us save or load the data so we're going to need both of those items there we also need the last row is long and the last results rule we're going to be running an advanced filter and i'll go over that with you and we're going to need those too i also wonder the asset id as a string the picture file the asset picture folder as we saw before the staff picture folder and the picture path as a string i also want to know if found staff row that's going to come in handy if we wanted to search for that and also dimension the asset picture file as a file dialog that is the variable that we're going to use right now inside this macro here asset add picture that's the one i wanted that's the macro that's been tied to this button when we click add a picture that is that dialog that pops up so how do we do that well the first thing what we're going to do is we need to get that picture folder located in c3 where are those asset pictures right here inside added i need to know this folder without that folder where they are located we need and that's located in c3 once we have that folder and we can put it inside a variable and that is going to be called the asset picture folder okay so once we have that we need to make sure that it's not empty if it's empty there's nothing we can do so we might want to let them know that you know first add a folder so let's do that and if we give the user a little message box please add a picture file path inside the admin screen before adding pictures so we need to know where to store those pictures right where are we going to put those pictures i want to put them on a specific folder so it's always tracked and so basically what we want them to do is browse for their picture wherever it may be and then we want to copy that picture into that folder and that's just we're going to do right here so with the asset manager focused primarily on that sheet we're going to set the asset picture file equal to application file dialog file picker here file picker because we're going to actually be picking a file so then with this asset file picker we're going to assign it a title please select an asset picture that title is going to appear right here notice it says up here at the top please select an asset picture that is that title that we have here once we have that we want to filter i want i don't want every type i want to have a specific type so when we run that i want to make sure that it's going to be picture files jpeg png gif we can add a lot more if we wanted so i want to add some filters here these filters are very important so that only selects those pictures those files that we have that so that's how we're going to add that filter add picture files and then what files are typed were you going to use this asterisk right here because that wildcard is going to sell any file name that ends in jpg any file name that ends in png and any file name that ends in gif and then we want a single file allow multi-select in this case no meaning we only want them to select a single picture if show does not equal negative one then go to no selection what does that mean that means if they have selected use the cancel button and not selected anything then we want to go negative one will be the automatic if they've selected something okay the picture path then becomes the selected item that selected item is the entire path of that location wherever it's located it's going to be in this variable that full path of the picture including that name once we have that what i want to do is i want to take that name and i want to extract that name i only want the name of that file which in this case is royalbluecopy.png okay so only that one i want to take that name and i want to place it directly in cue 11. to do that we use the directory command so inside here we see that q11 dot value equals the directory of the picture path that's going to set the name basically what it does it extracts the name extracts file name from file path okay once we have that what i want to do is i want to take that current file in its current path and i want to basically copy it into a new folder into that specific folder what folder would that be well that folder would be this folder right here i want to take it and i want to put it directly in the asset picture that way i know where to find it when i need to show it so we're going to take it directly from there and copy we can do that with a single line so we can do that with this line here file copy let's escape out of there file copy picture path it's current location we're using file copy and that's a very very simple command because all we need to do with file copy is find the original and enter the destination so it's current path and then we enter the destination path and the destination path is basically the asset folder adding on the backslash and then the file name that file name is directory of the picture path that's going to copy picture to new folder okay or just call it application folder application asset picture folder so now that we have that but what i want to do is i want to make sure that this is displayed this picture must be displayed this square what is this square i want to do this square right here this particular square right here that i've called asset picture it's part of this larger group called general info group just as we had with that staff picture notice it's not displaying there because there is no staff assigned to that but this is so i want to make sure that first this is visible this picture and then what i want to do is i want to fill that square with the picture now this is a nice feature if your pictures are generally the same size it's a great way to do it it's just a little bit of code however there are other options to display the picture we can import the picture and display it and size it but this we can do is a little bit less code so i wanted to show you this method basically what we're doing is we're filling this box now you don't necessarily need a border on this box if you want to remove the border you could just say no outline it's going to remove the border you may like that better so but what we're doing is we're basically filling that box and i want to fill that box but the only way to notate this specific box is i have to call it in the group it is within this group the picture asset picture box inside this group so to do that we can do this here shapes generally for group this is the group one of the items inside that group is called the asset picture so it's a group item acid picture visible we're going to make it visible first thing we'll do is make sure it's visible and then the second thing what we're going to do is we're going to do the same we're going to call it up we're giving it a fill we're going to fill it with the user picture and we're going to picture that picture path just as we did before with the staff that is it that's all we have to do to simply add that picture inside the asset picture and it's very very easy just one line of code to do all that all right that's a nice feature okay let's continue on with the macros once we've added a picture we also need to be able to clear the picture so simply clicking a button should clear that picture so how do we do that well that's just one macro it's very small two lines of code is all we need and that's what the next macro that we're going to go over called asset clear again all i want to do in this case we're going to focus we're not with the sheet so we're going to add the sheet in with the single line general info group we're focused on the single item here asset picture and it's inside our group so we're using group items i'm going to make that visible equals false i'm simply going to hide it until we add another picture until the picture changes and then all we want to do is make sure that we're clearing the contents of q11 q11 contains our picture name and we want to make sure that that is cleared out that is it so when i run that macro i click this button it is this button that's been assigned to that macro here clear asset picture and that's all we do is just clear it and then we add another picture then we can do that and just like that okay great i'm glad i got to show that to you next in line is our asset add new now what we want to do in this when we create a new one basically when we cancel new if i click new this button here signed macro is our asset add new that is the macro that's been assigned to this button so when i click new what i want to do is i want to clear out a bunch of fields now i can go in vba and say i want to l6 l7 l8 i want to clear out those contents individually or what i can do is i can assign a named range to all of the fields and that's just what i did i've created a name basically by holding down my control button and then selecting the individual cells and once i did that i then what just created a named range i just went here and i changed the name and i created a name called asset data so when i click on this as the data you see that all the cells are highlighted now when you do this keep that in mind that you're going to have to go in and update it for any cells that are centered any cells that are merged and centered or merged when you click this merge here emerge and center any of those cells you're going to have to update it it will only recognize the first cell the upper left cell so what you're going to have to do is go into the formulas name manager and go into asset data just like we did here in here and what you'll want to do is you'll want to update that so for example those merge cells let's say it was l11 so we'll take a look at l11 and what you're going to have to do is you're going to add this o12 so you want to add the lower right cell to complete that because when you select it it's only going to recognize the upper left cell so for any merged cells you will have to make that update inside this named range just as i have done here and i've just got just a few of them here so we did that already including the jobs areas of merged cell so for those we want to make sure that we're updating including all so if we created based on selecting it's only going to be focused on that first cell for example in this case r16 we want to go into the name manager and update it from r16 through s16 so that's how we do that so now the reason we want to do that is create a name named range for all these cells it's very easy to clear them out and we can do that with just one line of code and that's what we've done here so with the asset manager.range asset data that's the data we're going to clear the contents and this clears all of the data in those cells so it's very easy and then i want to add i want to add one more which is b3 the reason i kept that separate is because that is for the id and that's not part of it so b3 there may be instances where i want to clear out the cells but not the id so b3 the asset id i want to make sure to clear that in the reason is when we load something and we're going to go over this that asset id comes from here where does that asset id come from i was showing it to you a little bit later on so it is this that i also want cleared out b3 okay so because why is that important because when we save it i need to know whether we're saving an existing or a new asset we're going to use the same macro to save weather or update so we need to know the difference and that's what's going to happen inside our next macro so i'll go over that in a minute but the first thing what i want to do is i want to show some shapes and hide some other shapes so one called new asset group now we have some buttons if we look at this we have two button sets here if you take a look at this button set we have two buttons associated with this save asset and cancel new both of these buttons i've grouped and called a name called new asset group both these are called new escrow if i were to cancel the new you'll see another button set this one's called existing asset group so basically what i want to do is i want to hide one button set and i want to show the other one so when it's an existing asset i want to show existing asset group if it's a new one i want to only show the new asset group so when we have a macro that's for the new asset group i want to show new asset group and i want to hide the existing and that's what we've done with these two lines of code the new asset group we're going to display that here the existing asset we're going to hide that here also what i want to do is i want to make sure that we're hiding both of the pictures if we have a new item it's not going to have any checked out and it's not going to have any pictures so we want to make sure to hide those shapes so we can do that just very easily we're hiding two shapes they're part of the general info group and we're going to have the asset picture and the stat picture now you're saying well why are they part of this group why is it so important to make them part of the group the reason is because and i'll show you this background when i switch tabs i want to hide that entire group when i show that i want to show that so this group here this would be this border this text right and some of these buttons i want to make sure that they're part of this picture here this picture those are all part of this group so that when i switch tabs we'll be going over that mac or that switch tab so make sure you stay tuned and watch it all we're going to i want to hide that group and i want to show another group right so i want to show this group so we want to make sure that we hide and show that group accordingly so that's why they're part of the group so we just simply hide both the asset picture and we hide the staff picture okay the macro up next is the save and update it is this macro whether we're saving a new asset or we're updating an existing asset okay there is a required field we need to make sure that everyone does have a name if i try to save an asset that doesn't contain a name we're going to have a message box says please make sure to add a name to your asset before saving so that's going to be an l6 so we need to let the user know if with the asset manager the same sheet if l6 equals empty then we're going to get that message box and exit the sub so we want to make sure that we at least have that name before they can actually save it if it's not contained then we want to make sure to give them a warning and exit out okay so now what we need to do is again here we we need to differentiate because we're saving it now we need to differentiate is it a new asset do we need to if it's a new asset i'm going to need to assign a brand new row at the first available row in this case 33 and i'm going to need to assign a brand new id so to do that but if it's an existing i can use the existing row so how do we know the difference well that's inside our column b we have that information we have that asset id here and we have an asset row associated with that now that asset row that's associated with that we can use that because if we have an existing row like this it's going to let us know what row it's on now how do we find that we're going to use the match i've got a named range called asset id if we go in here we click on asset id we can tab over and we can see that this asset is tied to all these assets another dynamic named range based on that so all we need to know based on that dynamic named range we know if that ass id has been found using the match in b3 we know to assign it a row and it is that row is the first one found plus three the reason is is because i want the row number i want to know what row it's on so if the first one is found it's going to be on 4 that's why we add 3. so 2 is going to be on row 5. so we know that if b4 contains a value we know it's an existing if b4 is empty we know it's a new one okay so we can use that together so that's just what we do here if b4 equals mp then it's a new acid and there's a few things that we need to do for a new asset if it's a new asset i need to do some things only for those new ones first thing is i need to assign an asset id and that's how they would be here i'll make sure that there's 29 on this one so the next one would be 30 okay also what i need to do is and it's all i want to add a row i want to make sure that we add a row here and i want to bring down the current value formula we're going to do that but that formula i don't want to have all formulas i don't want to put formulas in my column if there's no data there i only want to put the formulas there that's going to calculate my current value of that asset i only want to put that if it contains data so what i'm going to do is i'm going to copy the formula that's here we'll be going over that i'm going to bring it down here and then what i want to do is i want to associate a row with this that row is going to be very important and it's this whatever row it's on and we can use a formula called row equals row and what that's going to do is just simply add that row here so these three things this one the row this one the formula and the id all those three things are going to be done only for new assets the last thing that we're going to do is we're also going to take that brand new asset id and put it here but how do we know what the next asset id is well that's going to come right here inside next id and we're going to use the max formula for that and the maximum the asset id plus one and then of course we are going to use if there's an error well why would there be an error there would be an error if for some reason there was no data at all we would need to assign it a unique id for the first time and that would be one so that's why we're going to be doing that if it's one okay so what that's going to do is it knows the next id is 30 and we have also the selector of that we're going to go over there that's based on the row that we select here okay so that's if it's a new one so we're going to do that so the asset row the first thing we're going to do is we're going to get that asset row it's going to be the first available row first inside our asset database first available row okay the next thing what i want to do is i want to take that id whatever's in b5 and this is going to be the asset id and i want to place it directly inside b3 again we're going to take that next one located here in b5 i'm going to place that directly in b if we're on a new asset b5 so the ass id that asset id that next id is also going to be going into column a right here so we're going to do that in the next line of code column a and the asset row is going to equal the b5 value and that is the asset id also that sid is going to be placed in two places next thing i want to bring down that formula remember that formula is located in y1 that's the second thing that i needed to do and that's this formula here that automatically calculates the current value i'm going to bring that down and go with that with you in just a moment i'm going to bring that down and we're going to put place that directly here that's the second thing and the third thing is we're going to place that row so we're going to do that with the last two items so we're doing that right here so z y and the asset row formula is going to equal whatever formula is in y1 now keep in mind that formula is going to be based on this first row so if we take a look at this formula we'll go over it now it is based on row 4. so that way if we bring down formula formula it'll automatically copy down it's like basically if we were using copy and paste i would use row one but if i'm bringing a formulative formula i want the first row that i can bring in the first row that's going to automatically so what is that formula so basically what i want to do is i want to know the current value and all i really need to do to do that is i'm going to take our original cost and i'm going to determine add in all the appreciation and and subtract out all the depreciation so that's just what we do inside this formula here so that formula is as follows we are going to use sum if air first of all i want to add just in case there's an error p4 all right i want to add whatever there plus we're going to use summers so we're starting with p4 which is our original cost sum is we're going to take the amount what we're doing is we're summing those adjusted amounts that's the amounts that's been adjusted located at our adjustments and repair table but what i want to do is i only want to sum those that have the asset id in a4 only that specific asset id located right here in this case one so we only want to know that i also want to know only those the type with the appreciation type here only those i'm adding up all the appreciations and then what i'm going to do is i'm going to add all the depreciation now remember we're adding them because the depreciations are subtracted so the only difference i'm doing here is the depreciation type and that's it so where the adjustment type is the depreciation type again so inside of the i'm looking for a specific asset id i'm looking for depreciation here and when it's found i'm going to use that i'm going to add it all up because the depreciations are going to be subtracted this one should be minus in this case that was the first one it did so depreciations appreciations would be positive and so that's how we do it right so then again it's going to take all these in this case outside it is one we have a depreciation here we have so it's going to add up all based on that asset id and based on whether it's a depreciation or a appreciation all right so that's that formula that's all we do and i just that's going to deduct it so we're going to simply going to add that to our value so that's it so it's adding it to our original cost that's all we need to do all right very good we'll probably you wouldn't have that kind of a depreciation on so that's kind of a little bit of a bit much of a depreciation make me make that .05 that's more like a reasonable depreciation okay so now we have that inside our acid database and at least we have a positive value for that okay so that's going to give us our current value then we bring it down the last thing we bring down is the row okay so the bringing down that formula and that way the reason we added a formula not just the row why don't we just put the asset row right here the reason is if i delete a row if i delete an item those rows are always going to be correct right if i delete it because there's formulas in here inside this row these rows will always be accurate because they're using formulas so that'll update automatically that's why we use row for that sometimes in the past you've seen me use the actual values here but only then we don't delete the rows right we may clear the row but we don't delete the row then it's okay continuing on so that's all all these things are what we're going to be doing if it's a new asset but if it's an existing asset we have a lot less to do all we need to do in this case is take the asset row and just determine whatever's in b4 is going to be our asset row that's it that's all we need to do if it's an existing everything else this little bit of line of code here is whether we're doing an existing or a new asset regardless and again this is why we use data mapping data mapping makes it so simple because it's only three lines of code whereas it would be 22 lines of code if we weren't using this so we're turning three 22 lines of code into three lines of code for this so what we're gonna do is we're gonna run a loop for asset column equals two to twenty four and that's just what we did here starting in two of for those of you that have seen this before the repetition will help but starting in column two because our id's already are two all the way to 24. we don't need to do all the last columns we don't need to do this column we don't need to do this column because this these two columns are already done so i'm going to do it to the last column if we put in here column we see that it is column actually it's the date format so we go change it back to a general and we see it's 24 okay so we know that we're going to go all the way to 24 and basically all we're going to be doing we're saving is whatever's in r16 or l15 or l19 of this sheet we're going to place it inside that row here so that's all we need to do and we do that with this loop fork asset column equals 2 to 24. the asset database because that's what we need to fill cells what is the asset row we've already determined that either here or we've determined it here and the asset column this is the one that's dynamics going to be going from 2 to 24. we're going to take whatever that value is going to be whatever is located in our asset manager sheet and what are we going to get that range that range l5 l2 whatever it is it's going to come from row one of our asset database and the asset column that is data mapping so basically save data to database so so that's all we're doing there so that's it asset load is similar but we just have to make a few updates on this because we have pictures so the next macro is asset load and when does this macro run this macro runs when i select something here when i make a selection change i want whatever asset we're focused on and i want that those details to load inside this here so how are we going to do that well again let's unhide it i actually have saved some information here in column c but it's hidden so what i'm going to do is i'm going to change the font color not to the same as the background color so i'm going to go in and i'm going to update that font to just the blue here and then i'm going to expand this column just a bit so we can see i actually make it a little bit darker because it's kind of hard to see so what i'm going to do make it dark here a little bit so now what we want we have the id here so i want that id to show up here this here is the item id notice item number one so that way when i select something it is this item id that's going to go inside b3 when i select something if i select nothing nothing will happen it is only for those rows that can have something containing an item id so i bring this information over from i will go over that in a minute so this information is going to be brought over from this list here we'll go over that load those results so basically there's a macro that's going to run it's going to create an advanced filter it is that macro it's going to send this information from our database these are results and it's going to bring it directly in here but we're going in order of the macro so that's why i want this load so when i make a selection change on anywhere from d6 all the way through h then i want something to happen so let's go on the sheet and see how that happens that's inside our asset manager sheet and it's based on worksheet selection change so as i spoke before on selection of assets we're making a selection if the user selects the cell anywhere from d6 through h999 and we need to make sure that one other condition is true we want to make sure that they're actually a value in d or c right without that there's nothing we can do so to ensure that we'll do this and condition d and the target row the row that they've selected does not equal empty then what we want to do is we want to do a few things first thing i'm going to do is i'm going to turn off application screen updating that's going to remove some of the unnecessary flashes we just need to make sure that when we use application screen updating before we exit out of the macro we need to make sure to turn it on with application screen updating equals true okay so i want to trigger that conditional formatting i want to know what row we've selected so inside b6 is going to take on that target row b6 is right here so notice that selected row changes and you'll notice i'm using conditional formatting so if we go in here and go into conditional formatting we manage rules we can see that b6 is equal to the row when that happens b6 is equal to the row and make sure that b6 and just apply to your d6 through h99 we are going to give it a white font with a dark blue background simply by changing their bold and also making a bold and giving that white font color and also changing the fill to a gradient fill so my fill effects we see we've got two colors here on the fill effects okay that's going to be that okay so that's how we do it so simply just doing that we also have conditional formatting i wanted to show just two other rules here that's based on the odd or even rows so if we look in here in two conditions one condition is we want to make sure that d6 does not equal empty that's one condition we also want to make sure that the mod row is equal to odd the mod row 2 is equal to 1 and that basically means odd rows odd rows i'm going to be coloring blue and i'm also going to give him that dotted outline so if we look in the border here we see this dotted outline here it is that that's going to be only for those odd rows for the even rows it's going to be somewhat similar except the background in this case is going to be white or just left line in this case it's going to be white so even rows how would even rows in this case even rows are mod equal to zero that's the only difference between those two formulas and that way we get this nice effect and it's only for those cells that contain data so as we add data here you notice that is automatically going to be formatted just by adding the data okay great so i'm glad i got to show that to you so let's continue on inside this macro right here so b6 is going to take on the target rule that will trigger that conditional formatting again all i need to do is i need to place that asset id very important inside b3 and that's going to come from c and the target row so b3 is equal to c and the target rule it's going to take on our asset id next up i want to run the macro that we're going to go over right now called asset load that is the next macro that we're going to go inside here asset load that's the next one we're coming up and again our focus is going to be on the asset manager sheet we want to make sure that b4 contains a value b4 is that row that's calculated for any reason that's blank we can't load it up because we need to extract all the information from that row we need to know what row of the database it's on so that's critical if it is blank we need to let the user know to please select a correct asset from the list on the left okay exit sub nothing we can do if we don't have a current row assuming that we do what we want to do is i want to first of all define some information i want to know the asset picture folder is going to be inside c3 remember in the admin c3 first i want to define that and put that in to a variable i also want to do the same thing for the staff because if pictures are contained i want to make sure to know the folder and have those in variables so also i wanted to define the asset row that's going to be located in b4 and then again we're going to do just like we did before data mapping just like we did here but this time it's going to be reversed in this case we're going to take whatever is inside the database in the asset database on the asset row and the asset column we're going to bring it inside and also this time notice we're going all the way to 23 and not 24. why is that and that is because this one the next adjustment here is located this is called 23. why do i not want to bring this inside because if we look in b15 here inside the asset manager b15 calculates our next and we'll be going over that soon but b15 is a formula right i don't want to bring in the data i don't want to cover up and replace this formula i only want this as a one-way directional i only want this date saved to the database but i don't want it brought back in okay because it's going to be calculated that date's going to be calculated based on the number of factors so i want to make sure that it's only one way that's why we're only going to 23. so in this case from 2 to 223 why are we not starting at 1 because 1 is our id for our asset that's already located in b3 it's already in b3 right we've already placed that here so we don't need to place that again so that's why we can start off at 2. so from 2 to 23 based on whatever the range is located in bro 1 inside our asset database we're going to take that cell that range there and whatever is inside that asset around mastercall we're going to place it directly in that range again three different lines of code replacing up to 21 in this case great so we've covered that that's going to bring all the information but now what i want to do is i want to update the check in checkout button notice that we have the buttons the ability to check out or check in somebody if we want to check it out right we can check it out to a certain person maybe we want to check this tool out to herald i need to know whether we are checking it out or checking in should i display this check in button which is the name of check in button or should i display the check out button which is the name of check out well that's going to be based on whether it's checked that if it's currently checked out then i want to make sure that the status shows checked out here right if it's checked in or whatever so that means if we change the status if it's available and we check it out i want that status to automatically go to checkout so if i save it if i load it if i load this one and i want to make sure that notice this says check in and when i load this one it says check out see the difference there so i want this button to load whether it's been checked in or checked out notice this has been not checked out yet this picture should must go as well so i'll keep that in mind but if it's already been checked out i need to check it in i want to check it in by somebody right so i want this check in button to display if it's already checked out so we can use what's in l10 to figure that out we can do that here and so we can use l10 to determine whether it's checked in or checked out so we're going to do just that inside the code if l10 equals checked out then we know then the check in button equals true we want to show the check in otherwise and hide the checkout button otherwise it's already checked it's in you know checked in already let's just say it's available in that case if it's available then what we want to do is we want to hide the check in button right because they can't check it in and we want to show the checkout button so that's all we're going to do if q11 equals m2 we want to hide we're gonna do the same thing for this making sure that b8 if cue 11 equals empty then what we're going to do is hide the picture or we're going to show the picture if it's not empty so that's all we do with this line of code and what does the q if remember you know q11 contains our picture if for some reason this is empty we don't want to show a picture here so we do just that with here if q11 does not equal empty then show the picture again we're going to determine the picture path is going to be based on the acid picture folder and the backslash and whatever's in q11 that's going to be the picture file path then all we need to do is check to make sure that it is an accurate picture path we can do that with the following if directory picture path vb directory does not equal empty we know it is an accurate path then all we need to do is take the general info group asset picture this asset picture which is part of that general group and make sure that it is visible we want that shape visible then what we want to do is we want to fill that shape with the picture so we can use that fill user picture with the asset picture otherwise else no asset picture in that case simply going to hide it with the group items asset picture visibly goes false hide picture box okay so that's all we need to do now we're gonna do very very similar with the staff picture but that's gonna be based on b18 right so this is what we need to do fix up if it's not we notice we do have a name here there's no name here associated with this right so if we take a look in b18 there's no staff picture associated with this we want to make sure to hide it so we just need to update that and that's very easy so in this case if b18 value does not equal empty then what are we gonna do then else empty picture is empty let's say no staff picture here all right so basically all we're going to be doing here is the same thing we want that staff picture to be so visible and i'll show you why i fixed that issue i'll show you in a little bit later why it wasn't working but now it's working fine now it's going to be hidden at that it's going to be based on another macro so no problem so basically what it was when i displayed this we don't want to show a picture here if there's nothing here so now what we're going to not fix that now so now when we select it notice only when we have b18 when there's a picture here we want it displayed otherwise it is hidden now okay this macro essentially was right though the issue was somewhere else so basically if there's no staff picture with the general for group stat picture we're hiding then all we want to do again we are now saving loading this we know it's an existing asset so because it's an existing asset that new asset group this group right here that's this one right here i want to make sure this new asset group is hidden we only want to show these two buttons for an existing so we only want to do that and i want to make sure that we're showing the existing asset group so we're going to do that the new asset group we're going to hide that and the existing asset group of buttons we're going to show it so that's all we need to do there so now what we want to do is we want to asset adjustment load what is this the adjustment that is this macros right here so and also we're going to get to the tabs right i'm going to get to this really cool tab feature i'm going to show you that but we're going in order i want to cover all of the macros in this module we only have two more to do after this so there's a module here that when i load it when i load it i want to make sure that i know all the adjustments that are associated with this particular item right so i want to know with this asset here i actually need to clear out this line here but i want to make sure that we're clearing it out so how do we do that how do we first thing what i want to do is i want to clear out everything here all the way from y and then what i want to do is i want to run an advanced filter and that advanced filter is going to be based on all of the adjustment repairs notice remember in our adjustments repairs database we have a item and we have an asset id so in this case i only want let's say we've selected one i only want to know those specific adjustments for asset id number one in this case we so if we're doing asset id number one which is the first one here i only want those items there so how do we extract that well that is basically with an advanced filter so we need to run an advanced filter and our criteria needs to be based on that asset id and that is located right here inside this so we have a advanced filter that we're going to run through vba we're going to take that asset idea i can link that i don't need vba to place that acid id because it's located in our asset manager b3 that is where that id is located right there so then what i can do is i can run an advanced filter i want to bring those results and i want to copy those results and i want to bring them over here notice adjustment date vendor type amount description row these are the same exact fields that are located here date vendor type amount depression and also we have the row that's associated with it right here this can be hidden right that is the actual row why is that important because if i want to delete that by using this macro i haven't added a macro then i know what row it's automatically i can just click delete and then of course we would just hide these by changing the font to the same as the background color and they would be hidden nobody would see that so that's all we would have to do there but we can change it back now for our purposes so that we can see it so it's easy to hide just as we did the ids previously so we've got that let's go a little bit darker here so we have those and so what i'm going to do is i'm going to run an advanced filter to do that in an advanced filter the first thing we need to do is determine the last row once we know the last row in this case 16 we'll run an advanced filter we've done it many times before but if you're new to vba our new advanced filter will go over then what i want to do is i want to bring this our criteria is going to be based on m2 through m3 i want those results to come here once i know the results i can bring those results and bring them into the asset manager but the first thing we need to do is clear any data that and that's just what we do inside the macro so inside this macro the first thing we do inside the asset manager from y11 through ad99 is we're going to clear the contents of all of those cells right there so that's it and then once we've cleared it we're going to focus primarily on the adjustment database that is the database that's located here adjustment database right here is what we have this is the database here there's nothing no macros on that sheet but that is the macro here this is this sheet that working on again we're going to determine that last row so we do that here the last row is going to be based on this sheet here a excel end up row once we have that last row we want to make sure that it's not less than three then we're ready to run the macro okay we're in the advanced filter so then the advanced filter we're gonna make sure that we include the headers and we're gonna go all the way to column i and the last row then we're going to run that advanced filter we're going to copy it over and we're going to create that criteria of m2 through m3 we want to copy that over we're going to copy it over from q2 q2 make sure through v2 and we want to make sure that our header the names here the header rows here here and here are always the same they can't if there's any difference we're going to get an error so make sure they're exactly the same it's spelled exactly the same once i have that then i'm going to determine the last row of our results using column q so once we complete it here is our last results row using column q if that last row is less than 3 that means we have no results once we do have results what i want to do is i want to take this data and i want to bring it directly inside here so basically we can do it with a single line of code starting with y 11 all the way through a d and down that's going to be equal to whatever is located right here from q3 but notice this starts on row three and this starts on row 11. so we need to compensate in eight rows for that and that's just what we do inside the code so the asset manager range y11 through ad in the last results row plus eight we're adding eight because we know our asset manager starts on row 11 while our other starts on row three equals dot range because we're already based on the adjustment database here q3 through v and last result bringing over the results okay so that's all we have to do there relatively simple cancel nu now cancel nu that is the but that is this that's been decided if i have a new uh asset and i decide that i don't want to put a new in it i want to cancel new well first of all i can only cancel new if there's actually some data here so all i'm going to do is just select whatever's in d6 and if there is value in d6 we can just put that in there so that's all i do because when i select that it automatically loads whatever's at the top of that so i can do that with cancel new so assuming that d6 is not empty all i need to do is with vba selected because we know that the macro is going to run automatically we select it so that's just what we're going to do here if the mat asset manager d6 doesn't equal empty then select it that's simple and then on the selection it'll load that macro so that's all and now with the deleting if we want to delete an asset deleting is relatively simple all we need to do is determine the row of the database and then delete that row and clear any data inside there so first thing we want to do is make sure we have a row that would be in b4 if there's not a row there if that was empty we'd have to have the user select a correct asset from the list on the left as a row is going to be on b4 and then all we need to do is just delete that the asset database asset row and the colon and the asteroid delete that and also what i want to do is general input group i want to make sure that the asset picture is not visible i want to make sure that the staff picture is not visible and then i want to run the macro that's going to add new and then give the user a message that the asset has been deleted relatively simple fantastic that's all the macros at least for saving you adding and deleting let's get into the macro that's going to switch these tabs now how do we do that well first thing is we have to understand that there's located on several columns right so if we unhide columns we can do that if we zoom out a little bit we can see that we have a first set of columns located here inside l let's click on this add that up inside between actually it's located j through t we have a set of columns that is our general info we have another set of columns from x through a d and those are so all we need to do with the macro is pretty much show one set of columns and hide the other and also update the remember we have to update the button sets so we show or hide the button sets accordingly so that's all we need to do inside that and that happens when we select on it so for example if i select on k4 or i select on y4 we want to show the general info if we select on l4 or we select on z4 we want to show adjustments and repairs so that's all we need to do inside that so let's show up that happens on selection change right when the user makes the selection change so that would be on the sheet the asset manager sheet focused on worksheet selection change and here it is if not intersection j4 x4 l4 z4 then we want something to happen if the user selects on any one of those they've selected on one of the four tabs so first thing we want to do is turn off screen updating and then i want to determine exactly what tab if they have selected on others j4 notice that j is right here so it's actually going to be j4 or y4 then we know it's the general info tab so j4 through x4 actually x sorry x not y because it's x is here notice that they're merged and centered i've got two of them so that's the merge let's zoom that in so you can see it's actually x or not y because there's a there's it starts because i wanted a spacer column here that's why i put that here so it starts in x and then y so keep that in mind same thing here this one starts in j again i wanted a spacer so this one starts in j okay so if it's j or x then we know they've selected on the general input then all we need to do is run a macro called asset tab general info otherwise if they've selected on l4 through or z4 l4 or z4 this being l4 and the other one being z4 this one's g z4 here then we know that we've done that this like let's unhide those again because i ran the macro by selecting on it so easy enough just right click and unhide that if they've collected on z4 then we know it's the adjustments and repairs so then we're going to run that macro accordingly so that would be the macro asset tab reports and then we're going to do is just turn application screen up into so let's take a look at these macros if we do that that's going to be based on the asset sheet macro module here so if we go in here we've dimensioned just a few variables here the last row and the last results are those will come up in other macros first thing what we want to do is with the uh asset manager that's the sheet we're focused on if we know they've selected on the general info tab what i want to do is i want to show that group remember that entire group with all the shapes general info group ms true i want to show that and i want to hide the repair group i've got a group that's called repair and that's just those visuals here so if i select on here we click here we see that's called the repair group i want to hide this group of two buttons because we don't want to show it unless they've selected there only when they're selected here so we want to show those two buttons i want to make sure we show the general info group and hide that so we do that with those two lines of code next what i want to do is i want to determine is there currently is it new now i need to know what button sets to show if it's a new asset right if they select here do i show the new button set or do i show if it's an existing or do i show when they click on the general info tab here let's get rid of that there we don't need that there we can hide these i'll hide these two we don't need those columns so or do i show the new in other words when i click general info do i show the existing asset group or do i show the new asset group and how do we know that how do we know which of those groups to show well we know based on what is located in b4 if this is empty we know that it is a brand new asset so we need to know what mode they were in in order to know what button sets to show because we've hidden them all so we do that here if oh we use b3 b3 or b4 is fine either one is fine b3 is all both of them are empty when them if it's empty then we know they're in the new mode so we need to display the new asset group msoc2 otherwise we're going to either show the new acid group or we're going to show the existing azide group right they're both been hidden they're already hidden so we just want to know which one to show are we showing new or we're showing existing also what i want to do this is the that line of code that i added remember you know was showing the staff picture it was showing that automatically well what happens is when we show that general info group all the shapes show so we want to make sure that we're hiding that shape so we can do if b18 meaning there's no staff picture associated here it's empty then we know to hide this picture hide that and we should actually do the exact same thing for this right we want to make sure that we're actually hiding it if it's new we need to also hide that so we're going to add that in right now the asset picture must also be blank and how do we know that we know if q11 is empty so we can do just that here and all i need to do is just copy this and just change that to q11 because we want to do the both both things so i'll add that row in right here and we're going to be based on q11 so in this case if q11 is empty cue 11 that is our asset picture then shape general group in this case it's asset picture asset picture must be false right we don't want to show that asset picture if they're there so when i go back in here and go back in here that has the pictures now gone if i were to add a picture here now when i go back in here and i go back in it's going to show that and the same thing here if it's been checked out we decide to add a person there we want that to show and then we want to make sure that that shows also which back so it's the same thing for the staff back inside our macro so we want to make sure based on those if q11 and b18 contain values then we show it and otherwise we should probably hide it then so let's add an else and i want to make sure that we're going to add it then shapes false else we want to show them right else let's put the else in else show that right dot mso see true right make another want to display it or hide it based on those so the same thing here the staff picture then also that's going to determine so making sure that we're showing it or hiding it based on that just to be sure that it's spelled else wrong here else then in this case that picture mso see true okay so we're just going to show it or hide it based on those values there also what we want to do is they don't want to hide all the way from j through u entire column hidden false right i want to display those show columns and then hide these columns hide columns okay so we can do entire column hidden equals true so which columns are we hiding we are hiding in this case we are going to hide in the adjustment columns and in this case we're going to be showing the general info columns so we want to do that so that's it so all i want to do it also i want to make sure again we want to make sure we're updating the checkout button as we went over before we want to make sure if it's been checked out then we want to make sure l10 looking in l10 if it's been checked we want to show this otherwise the check in button so since there's nothing here we want to show the checkout we want to give them the ability to show the checkout otherwise if it is an existing one that's already been checked out we want to show the check in button right so when we switch tabs we have to know which button to set in and that's always going to be based on the status l10 we can do that if l10 equals checked out then the check in button we're simply going to show and hide the check out button else it's already checked out then we want to hide the check in button and show the check out button okay just basically showing or hiding those buttons based on the check in around stats and then what we're going to do is we're going to just select l6 we want to select anytime we use the selection change on tab i want to select something else so we're going to do just that when i click on the adjustments i want to select in this case y8 for the date or in this tab i want to select l6 so we can do that here l6 is what the asset tab repair is a little bit simpler because there's less to show or hide the first thing i want to do is that general info group of shapes i want to hide that i also want to hide the existing asset group and the new assay group we're going to hide those button sets regardless because those are only for the general info and also the repair group i want to show that because that's specifically for the repairs adding repairs or adding new ones or saving them in this case i want to do just the opposite with the columns in this case we're going to hide j through u and show x through a i and also the checking out button both of those buttons are going to be hidden both of them and then also what i want to do is i want to run the macro that's going to load it we're going to go over that macro in just a bit but i want to run the macro that is the macro that's automatically going to load this so i want to run and load that we already did we already went over that macro that macro is going to load every single time that we run this macro so that way when we click on adjustments pair we want this to clear we did go over that mac already and that's going to refresh us so we just want to make sure that we refresh that data when we do that great so glad we got over that that's the last thing and last thing is why it's like okay so what about the asset list load the asset list load what is that well that is our list of assets that we're loading here that's going to list is going to load and basically what i want to do is i want to take all of our assets in our database i want to run them through and i did fix that small issue with our criteria here here's our criteria and i'll show you how this works in a minute and i want to get those results as we briefly went over before and i want to bring those results and i want to bring them directly into here so the first thing we want to do is clear out all of the cells starting with c6 all the way down through eight so that's the first thing we do b6 as well c6 through h and b6 as well b6 is our selected row here so we'll want to once we clear out this data we want to also clear out our selected row so that's important b6 here so clearing out b6 as well as everything from c6 through h99 clearing all the contents next primarily we're going to focus with the asset database we're going to get the last row we're going to if the last row is less than 4 then we're going to exit the sub now before we run our advanced filter we need to add some criteria as you notice before now it's where can i fix that issue and we refresh our data now we have that so we've got some criteria that we're going to go through right we want to know that we have some so how do we get this criteria automatically in here well that's going to be through our criteria and we have it linked so if we take a look at here our location is based on what's in the asset manager a4 so if we take a look in here in our location it is h4 h4 so if h4 is not blank then i want to list whatever is in there so we could do that with here if the asset manager h4 is equal to blank then we're going to put blank otherwise we're going to list whatever is located in h4 and we're going to do the same thing with status except that's an f4 if the asset manager f4 here is not blank then we want to place whatever is located there so we do just that right here if the asset manager f4 is equal blank put blank otherwise put whatever here these are the parts of our criteria but now it gets a little more tricky what if i want to have a dynamic what do i mean by dynamic what if i don't know what i want to search by or filter by i want to i have item number i have asset name i have notes or i have condition what if i want to do all of those things how do i know that what if i want to have a specific asset called office and i want to search them so in this case what i want to do is i want to have a dynamic header and the content is dynamic so we can do just that that header now is dynamic notice this is asset name if the asset manager e3 equals blank then we're going to put whatever's in w3 what does that mean well that means just adding just some random here just to keep it from not playing so i just added notes there just to keep it from not being blank so i'm adding w3 if it's blank otherwise we're going to show whatever's in there and i'll show you how we do that so this is going to change so if i change this to asset name it's going to be based on whatever's in e3 so if i'm going to search by let's say notes that i put notes in here if i want to search by a condition it is then can change the header so now it can change so all we need to do is link this so make sure that 100 sure that if you're going to be doing that now to notice those are coming from here item number asset name notes and condition they all come from this list make 100 sure that the names and the spelling in the search by are exactly the same as those headers whether it's the item number right whether it's the location the status the picture whatever it is make sure that the header details are exactly the same because that's our criteria then what we do is we have the dates in this case what i want to do is basically i want to make sure that i got a formula in here to see if it's blank if in this case the reason it's a little bit more complex because i wanted to add the asterisk by so that we can search anything that begins or ends with that so we've done just that if or the asset manager e3 is bike or f3 is blank then it showed nothing what does that mean well what i mean is if this is blank e3 or this is blank then i want to show blank right because we need both of these we need both e3 and we need f3 to show value if they're bla if either one is blank then we're just going to show them blank right so when we need to have in order to filter by that we need to have both the condition and for example let's say in the asset name in office we need to have both both need to contain values for us to actually run our filter by that so to do that we just check that inside that so that's why we use the or here if the or and then otherwise in other words if either one of them are blank we're just going to add blank right no criteria there if and making sure that if it's item number right item number and f3 is not blank then what i'm going to do is search by item number in other words item number is very specific if i want to search for a very specific item number let's say i want to search for this item number here i'm going to copy that and i'm going to go into the asset manager i want to search by item number here and i'm going to paste that item number here that is the only one that i want to search by so that is the exact one that i want to go from so if i'm searching item i want exact it's not a text field it's the number so i want that exact so in that case i don't want any asterisk before or after so i need to treat it a little bit differently and that's just what i've done inside the formula so if it's if it's item number here then i want just place whatever is in f3 here f3 right make sure the f3 is not blank which is that number otherwise we're going to assume that it's text right the other options were text in the case of text what i want to do is i want to put that wild card before and i want to put the wildcard after and whatever they've put in that way it's going to search for anything where it contains what the text for example if i search for office i want to contain that means it only needs to contain the word if i search asset name for office it only needs to contain that office and then refresh it so that way we have three different values that come up because they contain the word office that's why we add that asterisk right before and after so we have the wild card meaning containing okay so then what we would do is once we run the criteria we want those results to come here we're going to determine the last row and bring those results over and that's just what we're going to do inside this macro so let's take a look at that so the last row is the macro because we've done all of our work with the criteria with formulas the macro is super simple the last row we're going to determine that if it's less than 4x at the sub here's our criteria from a2 through ag3 that's our criteria we're going to copy it to ak2 through ap2 that's where our results are going to come then we're going to determine the last row based on what's called ak if it's less than 3 we're going to exit the sub then all we're going to do is bring it over we're compensating for the row remember our row starts on 6 here so we need to compensate because our row starts on three here so we need to add three that's it for the asset load but what about the macro and of course that's the macro that's tied to this refresh button then i've got another macro tied to this clear button it is that macro that we're going to run next so here all we need to do is just clear the contents of all the fields here and then just run that macro once again that same macro once our filters our criteria is cleared it's going to clear it automatically so all we do is clear out what's here here and here these three cells f3 through h3 along with f4 and h4 clearing all that out and then re-running the same macro it's going to automatically do great so that's it for that that's how we get to this list so now we've got coming up next we've got this check in and check out so basically if i if i want to check something out i need to make sure that we have a staff okay make sure that we've got a staff we've added a staff that we're checking it on and then we're just going to click checkout and what i want to do is check it out on the current date and maybe i want to assign a return date too on that so we could do 9 15 and i want that saved in the database so if i save that asset everything's now going to be saved and so there's two macros that are assigned to this check-in and out and those are the two macros that we're going to go over right now so for the asset checkout we're checking out first of all we want to make sure that they have a staff that's located in b17 remember once they select a staff we want to make sure that it's a right correct staff and that's going to be b17 b17 is going to tell us the row of that staff if it's blank if there's an error it's going to be blank so we want to make sure that we actually have a selected staff b17 will do your job b17 is empty then please add a check staff button before uh check out stuff i think it should be check out stuff you know check out staff out of this for checking out this asset so assuming that they have had now we can then then checkout button what we want to do is i want to hide that checkout button they've just checked it out so the next thing would be to show the check in button and i also want to set the status l10 the status here must go to checked out so l10 becomes checked out that's automatically and then what i want to do is i want to set our checkout date for the current date cue 14 is our check out date q14 right here setting that to the current date using date so that's going to set it to the current set checkout date to the current date and then also what i want to do is i want to automatically save and update this to make sure that it is saved so that the user knows that those changes are automatically saved so that's it all right next up is the check in button once they've checked it in i want to set it up so that they've checked in so how do we do that well that's just with a little bit of a macro and you can do two things you can set the due date we have a due date here but you can set the return date or due date here either one you can set it to r14 if we want to do that we'll probably do that so r14 is equal to the date that sets our return date set return date okay so we know it's return and i also want to do one more thing actually i want to make l10 here available right so i want to switch it to this available one so we're going to do just that right now inside the macro asset manager dot range l10 because it just copied above i guess dot value equals again available okay so set to one available so now we know it's available and then save and update okay so when we check in there we check that in this goes to available and we can then get the ability to check it out now it's been checked out and we can check it back in so it's just gonna switch these and continue to save that's it relatively simple for the check-in check out great we've covered two out of the three modules and we're going to cover the last one the last one is the asset adjustment macros that's where we're actually adjusting the macro there's two macros two main macros and it's relatively easy one macro what i want to do here is i want to basically determine what this would let's say you could run this macro every time you open the workbook and i want to determine based on this next date which assets need to be appreciated or depreciated based on our percentage here or based on our amount so we have the dates here notice they're all after so if i set let's go ahead and set them today's the 4th of september so i'll set a few of them to the 4th of september ok so basically i want this to trigger i want to filter i want to know only those assets that need to be adjusted automatically based on this remember we've already based on this so how do we get this so again what i want to do is base it on this but how is this next adjustment calculated well let's it's time to go over that now that we're focused on that so if we take a look at this we've got appreciation every three this could be months or years and we also have an amount or we have a percentage notice it's going to change to percentage so how do we change that so the first thing we notice is this change to percent or this change to amount how did we do that well the first thing is going to be based on a change event that's going to be based on the change event on n20 so let's take a look inside on n20 and see the change event that did that so we're going to go into the asset manager and we're going to take a look inside our worksheet change it's going to be based on i should say n20 here and 20 if that's what i said and 20 right so once the user makes a change in 20 we want to do something relatively simple if the target value equals percent then m 20 m as in mary 20 the number formats can be set based on this percentage else range m20 is equal the dollar amount so that's all we need to do is just change that to the dollar amount changing it so if it's amount or percentage it's going to be changed now this is obviously you need to do 0.02 or something like that so we can change that because otherwise it'd be 20 we can increase that a little bit right there we go so now if it's the amount here so we can change the amount to two change a percent back to percentage you can see it's 200 but then you change it okay so that's a great way to do it automatically changes so i want to know if it's a two percent so what i want to do now is i want to determine the next date and i want to base it on either two either the last adjustment date in this case notice the last adjustment date is nine four i want to do it if i change that to four i want to change it to one four or if i change it to two years i wanna make sure that it is two years from this day so how do we do that well it's based on two things notice we've got in single cell we've got both text and we've got a date so how do we do that well it's going to be based on what's in b15 if b15 is empty just show empty otherwise next adjacent equals the text whatever's in b15 and i'm going to show you what's in b15 in just a moment and then we're going to give it a specific format that's how we can combine a date and a text together and the reason is because i have just a little bit of space here so i can show both so moving on to b15 we have these three cells they're going to help us calculate this first of all i want to know is where are we adding months or are we adding years so we can do that if 0 and 19 equals months then we're going to add months so if i change this to months this is going to go to 2. if i change it back to years this is going to go to 2. so whether we're adding years or months i need to know that for our formula down here so if 019 equals years then we're going to place whatever is the quantity which is in n19 otherwise zero same thing here if it's months we're going to place whatever is in n19 meaning our quantity is located in n19 otherwise zero then we can have a very single and simple formula this so basically if 010 equals data the only difference is i need to determine was there a last adjustment date was our last date and if not then we need to base it on the current date so would be the next adjustment based on so if it's three months from the current date so basically the only difference is i need to determine is there a last adjustment date if there is base the next in this case two years on that last adjustment date otherwise based on the current date so we can do that here so if 010 meaning the last here located the last adjustment date if it's empty then we're going to base it on the current date we're going to base the year using the date formula we need the year what is the year it's based on today's year plus whatever's in b14 if we're adding years then the month is going to be two day plus b13 whatever's in the month so this way if the months are zero nothing's get added plus the day of the current day okay so this is all based on if there is no last adjustment date but what if there is a last adjustment date if there is then what i want to do is i want to base the year on whatever's in o 10 again plus b14 place the month on the month of whatever's in 0 10 plus b13 plus the number of months we're adding and plus whatever's the date so basically we're just setting a date a specific date based on either the current date or the last adjustment date once i have it it is this the next adjustment date automatically that gets saved automatically we bring it over when we save it save it it is this one in b15 that gets saved inside our asset database right here so notice it got saved so now that we notice how to calculate now what we want to do is i want a criteria i want to run an advanced filter i want to run a filter based on all of the asset items but i only want those asset items that are equal or less than the current date so if it's less than the current date then i need to run it so we can do that with a criteria it is this criteria here less than or equal and today so i want to know any next adjustments and i want to run advanced filter and i want those results and i want them to come right here down once i have those results then we can then work with them for each one i want to then add an adjustment and i want to add that adjustment in the next available row right here and that's just what we're going to do inside the macro so let's do that right now inside this here let's go back out of here so now we understand that and we're going to go asset check and make adjustments it is this macro that you may want to run every single time you open the workbook all right the first thing we're going to do is determine the last row and if they're of course just like we always do if there's no data we can't go any further so we can exit this up we're going to run our advanced filter based from a3 all the way through a z i'm running our advanced filter all the way a3 our header row all the way to a all the way to z all the way over here okay once we have that criteria it's going to be all the way here at 2 through a t3 so that's what we add here at and then we want the results to come all the way from av to b2 so results are going to come then of course as we always do we want to check the last row of those results if there is no last result we should get rid of that i did need to do that so if last result is less than three then exit the sub out okay nothing we can do if we don't have any results now i want to actually count how many adjustments we make so i'm going to run that just to make sure of course we can determine the last row but i want to loop through that just in case but we could also determine the last row if it's nine right it starts at three we know that we're going to make seven adjustments but there's a good way to do that we can also run a count set the initial count to zero if the last result row is less than three then no adjustments to be made let the user know just wanted to make sure so we've got a little notation there to let them know no adjustments you may or may not want that it may get annoying if you have every time you open it may not but you know it's checking at least okay but assuming that we do have data we can then move on for the results row is going to equal 3 to the last results row so we're going to loop through all these results starting with 3 all the way down and we're going to use we're going to first get a lot of information from that we've got a bunch of information that we're going to need so all the way in here we got the last row we've got the frequency as a string the adjustment type as a string an automation type as a string i need to know if it's appreciation or depreciation type the quantity three months two years i need to know that quantity also the asset database row what is that that is the row that's going to come over here here's the row remember this is why it's so important to have that row i need to know the original row of data if i know the original row of data i can then extract the information and i can also more importantly make updates right i can make the update to the next adjustment date i can make the update to the last adjustment date because i know the row and i know the original data what columns they're on so that's why we bring the row and we bring it row over in here all right let's put some data in here i'm going to run this macro okay now that we've got two rows of data here we can see that we bring it so those rows that we're going to extract 4 and 15 in this case are going to come from right there so now that we've got some data i want to want to extract some information so the asset database row we're going to pull that directly from be we need to know that row then also i want to know the automation type what is it it's going to come from ax and that's either depreciation or appreciation or something like that so now that we have that and then what we want to we want to make sure that we got values in this case the automation type also the frequency quantity in this case we have three months here normally we wouldn't have this it'd be full with data so normally we wouldn't have that let's update that i do want to update make sure that we have the data i'm just going to bring this down here so we have enough information here and then i'm just gonna bring it i'll bring it all the way down just so we've got a lot of information here and we got some more data to work with here okay that's good i like that and then uh let's run the macro one more time we'll reset that and then run again get a little bit more data in here and make sure that data is valuable okay good we've got some let me update the years on that i want to update i want to get some more data here but i don't want this year 22s obviously after that so we don't need the deers i'll put some more data in here so we can see okay now we'll run the macro and now we'll reset it getting the years right that looks good now let's see we got some actual data here three good rows also with information on the percentage okay so i also want to know the adjustment type whether that's going to be located in bb is it going to be a percentage or is going to be amount and then also i want to know the last adjustment date that's going to be the last adjustment dates located in bc here the last adjustment date here right here in bc once i have that i also want to know the current value what is the current value of the asset located in bd because we're going to have to add to that or deduct from that so we need to know the current value also if the last adjustment date is empty then the last adjustment date is going to set to the current date we're going to set the date the last adjustment date to the current date if it's blank now what we're going to do is we're going to add in now we're going to use we're going to do something very similar to this formula the one this this formula right here but we're going to do it in vba and we're going to use date add it's actually a really great formula so i'm going to show that to you and we're using date add for that because what i want to do is i want to basically determine three months are we adding are we adding one year how many we adding to that next date so but to do that what we need to do is we need to get some information we're going to use this date add and let me go ahead and show you if a z value equals months then i'm going to set the frequency to m otherwise it's year so how do we know if it's year m so why am i setting this to m and why am i setting this to four whys well that's part of the date add and let's take i've just copied it from the microsoft site and i'm just going to paste it in here so we can say the date add function syntax has these names to it interval right we need to know the interval and we need to know the number what is the number and the date right so we need to know that so basically i need to know whether it's months and years the numbers are quantity and we also need to know the date right the date's going to be the current date or the previous date so for settings here inside the settings made this too long why why why if we're doing years so yyyy year this is the interval argument this is what microsoft requires vba requires for the year and for month it's m so there's a few others here let's shrink this down here so we can see it here and so for we have date year of day if we're adding a day we use day and if we're adding a weekday we use w okay so i wanted to show that to you and so that's basically we're going to use the date add and i'll show you how that works so we need to get this m or this yy inside a string variable and that string variable is going to be called frequency it is that frequency variable that we're going to use inside the data formula and here's that formula right here super simple the next adjustment date is going to be equal to date add here's the function the frequency right the first value is the frequency i need to know the frequency then it would be that that yyyy or m then we need that quantity which is the frequency amount and then the date right so that's all so if i run it here right we have some values here we see the next is 12 4. for the first row notice our last adjustment is september 4th we know in this case it's three months so in this case our date add our frequency here is three our frequency our frequent frequency here is m see it's m for that because we do our frequency is three and our date here is nine four so if we use the date added it simply adds the next date meaning december fourth is simply adding three months onto that this frequency if this were yyyy and it would three it would add three years to this date a really really great great function there okay so then we have the percentage so now we need to determine what are we going to do so we have the right date that we are going to make this on but what we don't have is what do we know it's percentage are we adding this or are we using a percentage and multiplying it times the current value so that's going to be based on the adjustment type so if the adjustment type is percent the adjustment amount is equal to the adjustment rate right so we're going to set that adjustment array the adjustment equal to the adjustment rate times the current value we need to make that multiplication the current value we've already determined that right so if the current if we're if we have a 10 percent then we know the current value is 100 we're going to use 10 percent it's going to be 10 that adjustment is going can be 10 else the adjustment or amount in this case what do you mean else that means it would be amount not a percentage else then simply the adjustment amount is equal to the adjustment rate so let me just run and that's it that's all we need to do then all we need to do is if the determined is it depreciation or is it appreciation if it's depreciation should be a negative amount so if the automatic type is depreciation then the adjustment amount is equal to a negative adjustment amount that's all it's already a positive so if it's depreciation all we need to do is make it a negative that's why inside the adjustments you notice that many are negative if they're depreciation but if they're a appreciation it should be positive right so that's how we just make it negative with that line of code that's set negative for depreciation okay great so next thing what i want to do is now i need to update both of the inside here i need to update both inside our original data i need to update two things i need to update the last adjustment date to the current date and i need to update the next adjustment but i need to know set the original data now as long as we have that original row we know that that original data here is located directly inside here this update date right here located in v here and x so those are the two columns that i'm going to be making those updates inside the original data we could do that here v and the asset database row equals date set the current date to the last adjustment date on x is going to be the next adjustment date we've already calculated the next adjustment date here so we have it now we need to do is add it to the that's it that's all we do so now it's getting it ready for the next adjustment okay so now that we've updated the database but we also need to add the information to the adjustment database i want to add that adjustment i want to create a unique adjustment id here i want to add the asset id the asset information the adjustment type the adjustment date the amount description vendor if there is any there won't be any vendor only and then the row now so the first thing we want to do is get the adjustment id and it's a unique id so we've calculated that here automatically just right in here the next adjustment id is based on again using the max using the adjustment id that's a dynamic named range we're adding one if there's no data it'll be just be one so that's it so that's all we need to do to do that so that's going to get us 15. so we just extract from b10 we know the next adjustment id so we can do that here so with the adjustment database the adjustment database row is going to be the first available row which is going to be the last row of data plus one that's going to be the adjustment database row once we know that we can start adding in our information the first thing we want to do is add in that adjustment id and that's going to look come right here in b10 and it's going to go directly inside column a so we're going to we would put that 15 directly in there so b10 we're going to send that information directly to a inside column b we're going to take the asset id that's going to come from av i want to know that asset id inside av it's going to come directly from here inside a v right here asset id i want that asset id next up i want that asset name it's going to come directly from a w and also i want to know the automated adjustment type that's going to be located here in auto tab we've already defined it in a variable and whether that's going to be depreciation or appreciation it's going to go in column d next up i want the adjustment date which is the current date here and that's going to go inside column e it's going to take on the current date also i want to know the adjustment amount going column f and also the auto type again just once again exactly inside the description and we have that twice and then also the row i want to know the row is going to go directly in here great so that's all we have there and notice these these are different these are repairs and i'll show you these in a moment but this is what we're doing here so that's all we need to do and then all we're going to do is increment our adjustment count by one i'm gonna once we loop through every single row of our results here in this case three we're gonna loop through all these we're gonna place those directly in here and then update those dates we're gonna run a count adjustment count equal adjustment count plus one that way when we run this macro we know we have it and i've tied that macro to a specific button here called make adjustments so when i run that macro and that's to remove that we don't need that anymore and run it all the way through we're going to get three adjustments have been made and we take a look inside the adjustment database here we see that we have three brand new once all adjustments all made here based on that so we've got two appreciations and one depreciation all right very good okay so we've shown you that that's it for that one we went through this entire one that's great we got the make adjustments so we can run that macro open the workplace now what we want to do is we want to actually save adjustments so what do you mean by saving adjustments or repairs and in this macro i want to give users the power to actually save their own so inside the adjustment repairs here again we want to give them the ability to save their own adjustments or save their repairs so we can do that in there so if they enter a specific vendor and they want to enter a specific and usually they probably would enter something like repair or maintenance or cleaning service or something like that and then we can just give it some test description i want them to be able to save those repairs just like that so there's a macro that's going to do that so let's go over that macro right now which is called adjustment save so we're going to focus on the asset manager we need to make sure that they filled out some required fields data is required we probably need a vendor we certainly need an amount and we need the type so we're going to check to make sure that those if y8 equals empty or aa equals empty or ab8 equals empty then please make sure to add in the date type and amount these fields are required vendor is not required and exit the sub okay assuming that we have that we can then focus again on the adjustment database well again we're going to get just like we did previously the first available row plus one we're going to add in b10 we're going to add an adjustment id we're going to add in the asset id which is located in b3 right we already know it's always going to be this asset that we're focused on but whatever the asset that we're using and it's going to be based on b3 so that's going to take it on we want the asset name which is located in l6 here l6 is our asset name i want to place that in there i also want to know what's in aa8 right what type is it aa8 is the type here is it a maintenance that's going to be placed we're going to put that directly inside column d also the adjustment date coming from y8 f is going to take on the adjustment amount and that's going to be of course right here the adjustment amount from a b and also we want ac is going to be the description also a vendor right calling in to call h and that's going to come from z8 so the vendor here is going to go directly inside here putting that vendor here great so then let's take a look quickly on here the last thing we want to do is want to set the row and then also what i want to do is i want to actually clear those contents out once we've saved everything we're going to clear everything out and then the repair actually the repair has been saved and then i'm going to reload those adjustments we're going to run that macro again that's going to reload it so it is that same macro that we just went over that's now tied to this button so when i click save repair it is that repair that's going to get added the repair has been saved and we're going to now see that that repair has been here inside there test description okay great so we've got that that macro is there all right adjustment new is simply just clearing out those contents very very simple great there's another one i wanted to show you some information here we also want to know the adjustments for the last 12 months we can put this into graph lastly how do we do that very cool so let's take a look inside that the only thing is by the time you get this i didn't do this one i want to be able to delete one i didn't do that by the time you get this workbook i will make sure that that is active and all we're going to do is determine the row we know the row here right we know the row here all i need to do is just go inside here and delete that row right if i know the row i can delete it so i'll be adding that macro on before you guys get it so not to worry about that one so that is pretty cool all right so lastly before i let you go one more thing what i want to do is i want to be able to graph out as you saw this adjustments 12 months history i want to be able to graph this out in basically this type of a chart so how do we do that well we've got a set of data here and what i want to do is i want to determine the last 12 months starting with the current month which is now september 21. going back 12 months i want to set some dates and i want to have a specific format so this is a date field how do i get the current date and how do i drag this down so that it automatically drags down properly well the first thing what i want to do is use the date i want to know the year of today i wonder the month of today and i want to know not only the month of today but i want to determine the current month and how do i know that the row what is the row so i know that i want to know the current month that is the month of today minus the row plus six what does that mean well if i want the current month right but i don't want to create a formula for everyone if i know if i base it on the road what is the current month well if i want to add the month of today i want to add zero right how can i add zero because i don't i want the current month so the row what current row is on row six if i subtract 6 and i add 6 i'm going to get 0 right but now what if i go to the month before i want the month before so what do i want now in this case i want the month before so i want to subtract i want this to be minus 1. how do i get minus 1 well if the row is 7 plus if i minus 7 and i add 6 i'm going to get minus 1 right minus 7 plus 6 is minus 1 that's what i want i want the month before so if i keep doing that if based on the row in other words this row the row is eight a minus eight plus six is a negative two if i use negative two plus it's going to subtract two months before so i can use the row width that lets us know less so that's what that's going to do is automatically get the last 12 months so if i bring this down to 12 i've got the last and i've given it a specific format this format is a custom format based on the date this is the first day of the month mmm-yyyy that's the custom format i've given this is the first day of the month if i were to change that format to a standard date you would see that it's just a basically 9-1 you see it's just a date but i've given it a format so now what i want to do is i want to know all the adjustments in this month all the adjustments in this month so how do i do that for this specific item only for this asset id here so how do i do that again using some ifs i can do that it's going to be based on of course the date right only within the month so i want to know based on sum ifs i want to know the sum range is going to be the sum the adjustment amount it's going to be based on the adjustment date one of the criteria is going to be based on that date a date's going to be greater than or equal to a u6 right greater than equal to the first of the current month and it also must be less than or equal to the end of the month so it's going to be less than equal the end of the month based on this 0 means no months above we're using eo month which means end of month we don't want to go 80 months ahead or 80 months before so it's going to be zero so that's going to ensure that we have transaction in the dates but i also want to make sure that it's based on only a specific asset id it is that asset id in b3 and i'm making sure that that's absolute because when i bring this formula down i don't want to change any cells i want to maintain at b3 so i do just that then also what i want to do is i want to add an adjustment type right i want to make sure that the adjustment type is based on the appreciation appreciation only those for appreciation here so if i add that so i'm going to add up all the appreciation and what i want to do then is do the same thing but in the next day exactly the same i want to add it to another summit but the only difference is the adjustment type is going to be depreciated so all i'm doing is simply adding up all of the appreciations and all the depreciations based on that specific month for that specific asset item based on that asset id so i can then take this formula and drag it all the way down here getting all the numbers in here once i have all this data i can then just simply insert a basically insert a column jeff basically like a cylinder like this and that's all i did then what i did is i updated it accordingly so i just created something so it is this map here that i've updated here i've given it no background here filter background just i've added i've decided to add it here if we these series labels here given a search table now of course i use an older version of excel because many of you don't have newer versions so that's why i want to use i know there's some nicer graphs and things but this is fine it covers it and i've given it this particular series here we see this this grid line here and this access here are based on the dates so it's relatively simple it's just going to show the adjustment and then i've given it a title here that is it all right so thank you so much for sticking me i think there's a few things that we might have missed but i tried to cover almost everything it's already been incredibly long training i really appreciate you sticking with us if you like these workbooks to help us out you can pick up 200 of them and that's just 77 it's less than 40 sets of workweek and of course that's include a full workbook library single click to open the workbook or single click to view the video amazing training so i really appreciate that tons of great templates in there thank you so much don't forget to click the like button subscribe comment below and we'll see you next week for a brand new training thanks again [Music]

Channel: Excel For Freelancers

Views: 27,379

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Asset Manager, Manage Assets, Excel Asset Manager, Manage Assets In Excel, Tool Check-In Check-Out, Manage Tools In Excel, Excel Tool Manager, Automated Depreciation Excel, Excel Automated Depreciation, Excel Asset Depreciation, Depreciate Assets In Excel, Excel Tool & Asset Managre

Id: mWdQKTmKL_M

Length: 124min 20sec (7460 seconds)

Published: Tue Sep 07 2021