Can also enter multiple entries even with just a click of a button we can automatically enter unlimited types of entries for the future and of course we've got a dynamic admin screen where we can enter our own income expense accounts along with initial balances we can even set some monthly and overall goals so it's going to be great and then this dashboard is going to tie it all together with our net worth planner we're going to have income here spending and of course we will also have a great timeline where we can click and automatically adjust.
The graphs and charts here we're going to show you that we're going to create this really cool menu and i'm going to show you a whole lot more so i hope you'll stick with us it's going to be a great training i do bring these to you each and every week and i hope you do appreciate them i put a lot of time and effort in because i want to make you not only great with excel but successful with excel and that is my goal here all you need to do of course is just click on the subscription below in youtube and of course don't forget to set the notification icon bell that'll ensure that you.
Get these trainings and alerts sent to you every single tuesday when i create these it's absolutely free if you want to download this you can using the links down in the description however if you do want to support the channel there are so many ways to do that first of which is by grabbing my 250 template pack that's 250 of my best templates and it's got one low price in fact i'm going to put in a mini accounting application for no additional cost and i'll have that for those who view who want to purchase the full 250 with pdf another great way is our patreon platform.
Each and every week i take these templates then i take on your suggestions your ideas your comments and i add on to that i create an additional training and an updated workbook and make that available to you based on what you want so whether it's a feature you want you want to fix something or maybe you want me to focus on something also available on our patreon platform is unreleased features and trainings we've got pdf code books downloadable early bird discounts we've got also got supporting trading resources like the icons that are used in this pictures of course so.
You can quickly assemble your own workbooks and of course full video downloads that way you can watch these trainings anywhere anytime even without internet full video downloads and of course members only discount that's only available on our patreon platform i'll include the links down below and i'd appreciate your support for just a few dollars a month all right thanks let's get started on this so basically we're going to break this up into three major components and the first of which is to be able to enter data we want to quickly and easily enter data whether we're entering expenses income or payments we want to be able to quickly add an entry save an entry and have that.
Present that in a way but before we can do that we need to do that we'll be doing that with some pivots here we've got some calculations we'll be focused on the net worth how do we calculate that to get the total uh available using all those transactions what's the current balance of each individual account what are the total expenses and the total income based on the data entered so working with that data is the second component and then lastly we're going to focus on displaying that data we want to display it in a very user-friendly way so users can quickly see where they are in their financial plans how far they've reached have they reached their goals yet if not how far their way and what is their breakdown right and we wanted to be able to present it in a very easy and familiar manner just in this three tab so they can see a lot of data in a very.
Short period of time and it's easy to consume and they understand everything as far as their monthly income and expenses and then of course their net worth at a glance they can do that so those are the three areas we're going to be focused on and the first of which is the financial entry so how do we create this financial entry we got also got a menu here we can click from the dashboard the entries and the admin that's a very simple one line macro and i'll be showing you how to do that one single line of code to do this so we're going to be going into that so let's go over this entry how do we arrive that we've got entries by type right we can have different types of entries this is our entry type we've got four different types of entry income expense payments and transfers.
Right so what i want to be able to do is i want to be able to simply click an expense or simply click an income i want to be able to locate those very very easily what income types are they now those income types all originate from the admin screen so inside the admin screen we have four different income types right we have paycheck bonus workbook and you can add anything you want and of course we've got some additional expenses we can add that i've also got some accounts right what kind of bank or asset accounts bank or asset accounts or something that you have right something that.
You you know is an asset something and it could be anything it could be tangible like cash right or an account or it could be intangible such as like maybe a home equity where it's equity you have built in your home or it could be stocks that you have that have a somewhat intrinsic value although maybe not a cash value at the moment but there is value to it an ira maybe you can't touch it yet but it does have built-in equity and it is considered an asset right also maybe retirement so we can keep track of all those assets and of course we also want to know the liabilities what are the credit cards what do we owe right what type of money do we owe is it a car loan do we owe a friend a loan do we owe credit cards right what is our liabilities currently money we want.
To always make sure that our assets of course are greater than our liabilities and basically our net worth is simply our assets minus our liabilities and that's going to get it as we can see here in the dashboard our total assets minus our total liabilities is our net worth so we're going to be able to put that together that's really a basic i also want to be able to create i want to know what i want to reach a certain level of income per month let's say maybe i want to set my income goals to maybe 7 500 we should probably call these white because they're user entered so i'm going to go ahead and give these a color white to make sure that we denote that that is available for the user to make those changes a little bit clearer there so what is our goals for our income what do we want to reduce expenses maybe our expenses are 3 000 or 3 500 we're going to try to.
Reduce them down to around 2 500. this is going to help us know how close we are to our goals i also
Want to know we want to increase our assets maybe to 500 000 and we want to decrease our liabilities what we owe other people or other credit cards or other entities we want to re reduce them down to certain levels so that means in our dashboard when we see here we want to make sure that we're we're currently our assets are 328 000. we want to increase them to 500 000 we need 171 000 more to do it our current liabilities are 72 000 we want to reduce it to 50 000 so we've.Got 22 000 more and of course our goals that combined right we know that we've currently got 256 000 in assets we want to increase that to 450 and therefore we've got 193 left to do that so we got an eye on that so we want to make sure that's why so in our admin screen we can create these goals and our admin's relatively simple that's it each of our accounts whether it's asset accounts or whether our liabilities account we have initial balance right if you're starting this today right what is the balance on your credit cards currently if you put in all the future transactions when you know what the current balance is just like with the checking account if you're going to be adding income to your checking account and paying bills what is that initial checking.
Account balance so you can put that in here and that's automatically going to get calculated based on the additional entries that you put in what is the current balance of that it's going to start out with that initial balance so that's the basics that covers the basics there and so once we have these financial entries once we've entered them into our database here here's all of our financial entries here what we want to do is we want to be able to quickly locate a specific entry so the best way to do that is to simply use a filter an advanced filter so first thing you may want to do is you want to separate it by income so if we want to look up only expenses and i only want to look up grocery expenses and then maybe i say okay this is a lot of grocery.
Expenses i've had in last year maybe i want to just cut it to the last six months so i can just take a look at this six month of groceries and we see that it starts at in june 4th and goes all the way to the december 24th so we can quickly locate a previously entered expense item just by using these filters and then of course we can select one when we select one it is going to load that entry if we want to make if our budget amount is 100 but we actually went a little bit over we can make that adjustment here and then save that entry if i decide that i want to create a brand new expense i can click new entry and maybe i want to just put in a reference or maybe you can leave this bank it's not so it's not so important so let's just say we do one two three and then we want to enter.
An expense type right maybe we want something let's say fitness right we're gonna pay for the gym uh we already have that let's choose something we don't have gifts and donations maybe we make a charity donation every month and we're gonna make it from our let's say our checking account automatic and we've budgeted a hundred dollars and then we also set the actual amount as 100 right and now we can just put gift to charity right now let's say we do this every month right i don't want to create this same transaction every month i want to do it in the future so i'm going to put create gifts every one month starting on saturday let's say make this the 20 22 the current year and i want to create a total of 12 entries so now we can see this button says create 12 entries this.
Is going to create one and it's going to start on january 16th so when i click that button it's automatically going to create those 12 entries just like that and then they're automatically created now when we click on 12 gifts haven't added and see that notice it was real quick i can select here and we can see that they have all been added now if we change this filter down to the first of january you're going to see that all 12 have been added if i select on one we can then make it edit to that one so it's very very flexible right we can just save and update so now we see we've we've made a change to this one where our actual amount was 120 and the difference is 20 we've budgeted 100 our action amounts 120. so we can quickly go in and make changes to any previously created transaction this allows us to create and fill the entry database very quickly.
On all of our regular types and we can of course put it whether it's one time or recurring this is kind of helpful more of a note it's not a critical important but i had an extra thing so we can let us know what kind of type is it is it a frequency is it a reoccurring type or is it a one time that might help us in the future so i've added that in so we can quickly create one or more entries we can also look it up and very quickly edit one just for the few clicks so let's go ahead and see exactly how we did that of course every time we save it it's going to get saved inside this database each entry comes with a unique id a type what is it an income expense transfer or is it a payment what is the frequency type is there a check or bill number we can update that an entry.
Date the date that it was entered an income or expense type what type of that and what account was associated with that now if it's a transfer it could be two different accounts keep that in mind and also if it's a payment it could be two different accounts too if you're paying a credit card bill from your checking account you're going to have from the checking account here and then you're going to have the credit card here okay what is the budgeted amount what is the actual amount and then some notes so we're going to tie all that into a single database here called financial entries okay so how are we going to do that well let's get into vba and start out with the left missed an icon here i think it's just in back let's go ahead and bring that to the front right picture format and we'll bring that to the front here bring to the front okay.
There we go that looks better all right so we've got that here and what i want to do is i want to go over we'll start out with this filter here right when we make a change to any type when we want all types i want to make sure that all those types are listed here right so we can use an advanced filter to do that of course we're going to be filtering out this database so the first thing we want to do is filter this data based on the entry type and we need some criteria for that and i've got some criteria right here notice it says entry type we're filtering it by entry type now if i want to filter it if i look in here and i want to filter by only income accounts i want to make sure that that criteria inside there is only income if we take a look here we see this only income so we know that this particular is tied with a formula to that cell if the if it.
The financial entries f4 which is the cell that we just changed equals all types then just show does
Not equal empty otherwise show whatever's in there and this way it's just going to show it's going to list all types if it's if the user has selected all types otherwise it's going to show whatever it is so if i have income and then i want the results to come here so that i know that only income types these income types these are the results so this is where we're going to get our results right here entry type of income and then all the associated the unique income types here if i select all types we're going to show all types and we want the income so we know we've got them here all right so we can see that now it is does not equal empty and it's going to load all that all right so how.Do we do that of course that's in a macro right you can go into the developers and go into the visual basic and we're going to go right into their alt f11 shortcut and we're going to focus on the entry macros we've got three different modules one with some admin macros one with the dashboard macros and one with entry magnets entry is the one we're going to be focusing on first and the macro that does that is called entry types right the macro that does that filtering is called load entry types now if we notice right that macro when does that macro run that macro runs when we make any type of a change to f4 f4 so that way when we pull up this sheet this entry sheet.
This is our entry sheet when we make a worksheet change to f4 that's when we're going to have it so if we take a look right here inside the code on show entry name change run macro to populate list if on if not intersection target range f4 is nothing then run this macro right if we want to locate that macro right click go to the definition right so we just click definition and that's going to go right to this macro which is located in our entry macros first thing what i want to do is i want to make sure we clear out any of the initial data right i'm going to take all this data here i want to clear it out because i'm going to be bringing new data in here so e6 all the way.
Through f and down i want to clear that out so that's just what we're going to do now there's also a row that's associated with that when i make a selection of a row i want to make sure that the row that i've selected also gets cleared out that row that's been selected is located right here inside a let's unhide some admin columns i'm going to right click and unhide it and we're going to see that the selected row is located right here in b6 b6 is where so notice as i select a row b6 changes we'll get into the selection change event in just a moment on another macro but we want to make sure that when i clear this out when i change this i want to make sure that it gets cleared out because we don't know what the user like so i also want b6 and b7 now b7 is the.
Specific entry that's been selected so it's b7 so not only do i want to clear out b6 which is the row here i also want to clear out b7 so those two cells get cleared out along with all the data so we could do that with just one line of code right here we're going to focus on the entry database that's going to work to take our focus because that's going to run our advanced filter we're going to determine the last row of data we're using and excel up that's going to be row 310. once i know the last row i'm going to run an advanced filter and we're going to have specific criteria that criteria is going to be o2 through o3 if you've watched my videos before you've seen me do advanced filters hundreds of times probably.
And they're really powerful so you know the repetition helps so we're going to have of course the results come through q2 through r2 and that's just what we're going to do here first we're going to determine the last row of that database if it's less than three that means we have no that no data right actually we're going to put less than four actually look our first row data's on four so we want to make sure it's less than four so if it's less than four we're going to exit this up then we're going to run that advanced filter we're going to start out with those header rows a3 through f and you certainly want to make sure that your header rows a3 all the way through f is fine because we're only we don't need to know all this data we're not filtering this data at this time so we're only going to focus on this up to the income and expense account here we want to make.
Sure that the criteria when we set that criteria most of the issues entry type this header must be exactly the same and so are these as your original data so make sure when you're adding that criteria or you're adding the results what i just do is i copy this and i just simply paste it up here just to make sure that we have the right exact text right i want to make sure it's exactly right so we're going to run that criteria and we're going to get those results we're going to determine the last row of the results based on column q and that's what we're going to do inside the code.
So our criteria is o2 through o3 our results are q2 through r that's we're going to have our results and then we're going to determine the last results row based on column q right once we get that into variable we want to make sure that we actually have data so if the last results row is less than 3 that means we have no data and we can exit the sub if we do have data what i want to do is i want to take all the results of that data and i just simply want to bring them directly over inside this area right here and we have conditional formatting that will automatically cover that give it that alternating color along with the selected row so this particular line of.
Code is going to bring all the data over bring it from the database here into our entries row starting on row 6 here and the last results row now since this starts on row 6 and we're pulling our data from row 3 we need to compensate for the difference in rows so we're going to need to add 3. that is it for the database then what i want to do is i want to see now let's say i clear this data let's say i rerun this and i want to find out if this has been selected i want to see if there's a particular been selected and i want to select a row so what we want to do in this case.
I want to cl if n10 does not equal empty and 10 is right here let's take a look at this what i want to do is i want to select expenses and i want to select let's say this it should be n9 let's take a look at that and 9 and nine because it was not working but i'm going to show you exactly why i just got that wrong and and nine up here see here and nine so what i want to do when i run this there i want to sit there you see not working i want to select whatever's been notice that fitness is here right if i change this to utilities right and we see that we have utilities i want to look up utilities and i want to find utilities here if it's found i want that row whatever row that is.
Row 10 i want that 10 to be put directly in b10 because why is that because let me just stop the code right here okay and now because i don't want it to run i don't all the way so what happens is it's the code stop but i want to know what row should be selected notice we have utilities loaded here right we have utilities loaded here so i want to select utilities here i want to show that utilities is the one that we're developing these are utilities right.
These are this is utility so i want to make sure that row 10 is selected so what i want to do is i want to grab the value here and i want to look it up here if it's been found i want to take whatever row has been found and i want to place that directly in b6 so as we continue on with the code that's exactly what's going to happen that 10 is going to appear here it's going to trigger that conditional formatting if we want to take a look at that conditional formatting we can notice that that shouldn't happen see when i select it we don't want that so let's fix that issue when i select more than one cell i don't want anything to happen so on entries right let's take a look at the selection change event i'm going to put in a little bit of code right here if target dot count large is greater than one meaning the user selects more than one cell then.
Exit the sub exit sub okay so that's kind of important and then what we want to do because when i was showing you something i want to select more than one i don't want anything to happen right now nothing's going to happen because i've selected more than one cells so i've got a utility selected here i want to make sure that when i run this code i want to make sure that utilities automatically gets selected here continuing on with code that's what's going to happen we can undo that so how are we going to do that well the first thing what i want to do is i want to make sure it's not empty if it's empty like let's say it's a new entry we wouldn't know what to select so that's fine but if it's not empty we need to know what select so what we're going to do is wherever it's found.
We're going to place it in b6 however if it is not found it could create an error bug so what we want to do is we're going to wrap it in on air zoom next and on air go to zero however if it is found we want to take whatever row it's been found and we want to place it in b6 so where are we going to look we're going to look in f6 through f999 what are we going to look for we're going to look for whatever's in n9 we're going to look in the values and whole and we want to extract the row so i want to take that row that it's been found i want to put it in b6 if it's found that's it that's all we have to do so that way notice if we have weekend work here we've selected a specific weekend work.
And then i go ahead and clear that out i want to make sure that it is weekend work that gets selected again very good so i'm glad i got to show you that part now when i make a selection here we want this list to load up i want to know all the entries based on utilities between this date and this date and i want to load them all in here and that comes with a macro so that happens on selection change notice when i make a selection change this we're going to load up all of that items that are fit that description so how do we do that well the first thing that happens is on selection chain so we go back into the entries entry sheet here we're focused on selection change right and we see that we're making a selection change anywhere from e6 all the way through f and then down okay so we're going to focus on that e6 through f and down make a selection we want to.
Make sure that e contains a value right if there's no value in e we don't want anything to happen so assuming that there is a value then what i want to do is i want to know inside t3 into days t3 i want to place whatever's this what's our criteria how we only want to know close so i want to take whatever's in f and the selected row i want to place it inside a criteria right we're going to run another advanced filter and it's going to be called here entry criteria right so what i want.
To do is i only want to know close and i only want to know between these two specific dates then we want to know all of the items all of the entry items that have closed between those two dates and i want those results to appear here once i get those results i want to then creep take those results and bring them directly inside here and so that's exactly what we're going to do so it is that selection change event that happens everything so the first thing what i want to do is take get that criteria inside t3 of our entry database t3 is very important because i need to.
Know what we're going to filter on so t3 is going to take on what it's going to take on whatever is an f in the target row so if i have selected a row i know the target row is 9 i know that f and the target row weekend work is where exactly where i want to place it inside t3 that's where i want to place it okay so moving on we know we've got that so continuing on so what else do i once i've got that criteria placed i also want to trigger conditional formatting right conditional formatting let's just go over that is based on that so i want to take whatever row that's been selected and i want to take that row and i want to put it directly inside b6 inside b6 so b6 is going.
To take on that target row that's going to trigger the conditional formatting if we go in here home go to conditional formatting we're going to see three different rules first which is for that selected row and basically all we're going to do if b6 equals the selected row we're going to color it this dark background with this white bold font i also want to call the alternating row so we can use this formula for our odd rows and we want to make sure that e6 make sure there's no dollar sign before 6 because we wanted every row below that and we want to know the odd rows mod of row 2..
That formula is going to give us that green that little light green formula it's going to apply to e6 to f99 for even rows we're going to go with even a lighter color nearly a white and even rows the only difference in this one is the mod equals zero meaning even rows this equals one meaning address so odd rows are going to get colored this and we also want to make sure that again there's no dollar sign before the six we want to make sure it's not absolute covering every row applying to that's it that's the same conditional formatting we're going to use basically for here and here it's exactly the same the only difference is this is going to be focused on row 7 while this is going to be focused on row 6 okay the selected row notice the selected row will change.
To whatever we want it to when we select it okay so after we've placed that row and the only macro that we're going to run is called load entries that is the next macro we're going to go into so entry we've already covered load entry types now the next macro is called load entries and it's going to be very very similar right the first thing what we're going to do is clear out all the data now we do have some hidden data here i want to know that entry id remember inside our database our database in column a starts with an entry id this is a unique id for every single entry i want to place that but i want it hidden so how can we do that well there's two ways to hide it really one is to change the font color the same as the background color another one is simply to change it to a custom format so if we look in the more number formats.
We've got three semicolons here if i were to clear them out maybe to put or put something general now we can see that those entry ids right so one this is important because when i select on something i want to take whatever entry id is here i want to place it directly inside b3 b3 is where our entry id is that's going to help us determine the row and i'll go over that in a moment so we want to make sure but we don't need to see these entry ids so all we need to do to hide them is just go into the custom format and just change it with custom and just make it three semicolons so one two three.
Semicolons and that will hide them all keep in mind if we are going to use the find to look for something let's say we're looking for something find using xl value let's say remember using when we use a find often we use something like xl values right but when we're looking for a specific number that's hidden we need to use excel formulas that'll find it even if the column is hidden or it's hidden or it's hidden through some formatting okay formulas will help us do that find what when it's hidden values will not alright so that keep that in mind so let's continue on with that macro now so loading the entries the first thing we want to do is clear any data i want to clear one the.
Selected entry row which is in b7 i also want to clear remember starting in g6 all the way through k and down we're going to clear out any data that might be here and that's going to handle it right here entries range b7 through g6 k99 that'll clear that out then again we're going to focus on that entry database we're going to do just like we did determine the last row we're going to get some criteria we know this has already been placed now the dates here for the criteria are linked to the dates here if it's empty here we want it to just show empty show all the dates however if it's date i want to show only this this date greater than or equal to this date and less than or equal.
To this date so we can do that right here with a formula now we want to use the dates in number format that's very important because regardless of the date format when it's in number format it'll work properly so if the financial entries does not equal empty then we're going to put greater than or equal the financial entries i4 that's that from date otherwise we're just going to show doesn't that equal empty and we're going to put it there and the same for the entry date except we're going to focus on k4 if k4 is not empty we're going to show less than or equal to whatever is in k4 otherwise we're going to show it's empty and this is going to provide that necessary criteria for based on the specific income or expense type and the dates associated with that.
Entry so we can do that so our criteria is going to be from t2 to v3 we're going to pull those and we're going to pull those results directly in here all the way down here i want i want the difference between our budget amount our action amount a formula is going to take care of that this formula making sure that we have values both in z3 and a3 as long as they're both available that i want to subtract them so if there's any difference there's no differences in these i'm going to subtract out that difference and show it because i want that difference to show up here inside it but that difference is not part of our database so we want to use a formula to help.
Us to do just that okay so that's basically what we're going to be doing and so again we're going to i want to clear the prior results i want to clear this x through a b and because this formula wouldn't necessarily be cleared out automatically by vba so we want to make sure to do it starting in x3 all the way down clearing out any data that might have been there before determining the last row of our original data if it's less than four then we're going to exit the sub we're going to run our advanced filter just like this should not be jf our advanced filter should go all the way to the last and we're going to call that j okay so it should be j although it still works and then we want our results to come t2 through v3 t2 through v3 these were this is where the criteria is going to come t2 through v3 our results are going to come in through x through a a x2 through a2 and.
That's just what we have here x2 through a a the last results row is going to be based on column x if it's less than three we're going to exit the sub that means there's no results then what we're going to be doing is i want to copy down that formula that formula is always going to stay in ab1 so all we need to do is take the formula in ab1 and copy it down from ab3 all the way to the last row we don't really want to keep formulas in cells unless we're actually using them right so we certainly don't want formulas all the way down here that'll slow up our workbook we don't want that so we can use vba to bring down those formulas having some kind of calculation on demand or formulas on demand only when we need them that keeps our workbook light and fast so.
Once we have bringing over those formulas remember formula to formula we're going to bring in all the results taking these results from x3 all the way through a b and bringing them directly inside here all starting in g all the way through k and that's what we do here so g6 through k and the last resolution again compensating for the row difference by adding three going to bring it all the way through x3 through a b and the last result so this is going to bring over our entry details and again just what i want to do before if i run this filter let's say i run this filter let's say i'm running this filter notice i just ran the filter remember when i run this filter we're clearing out that selector i'm going to run it halfway to right here so when i run this.