How To Create A Dynamic General Journal & Accounts Register In Excel

hello this is Randy with Excel for freelancers and in this week's training we're gonna be going over the dynamic general journal where users have the ability to enter specific transactions with just a few keystrokes and automatically select specific accounts for balances as well based on dates it's gonna be an amazing training so let's get started alrighty thank you so much for joining me today I really can't wait to get to this so many of you have asked me for this type of training a dynamic general journal account register are the ability you know Excel is a perfect tool for this in fact we're gonna put our own spin on it we want to make it dynamic so it's as opposed to having multiple registers we just want to have one a single register that displays it based on whatever specific types of expense income or chart of accounts or whatever type of transactions you have we want them all displayed and we want to do that based on specific date so if we choose specific dates we wanted to filter based on those dates so it's gonna be a great training in fact we've also got a general journal which is gonna list all of the transactions all of the transactions here and you can scroll up and down based on the dates that you have selected so changing those dates will automatically increase or decrease those transactions based on those dates so we've got a lot on that issue we have also got the ability to enter transactions so when we enter a brand new transaction and we select a date for example we can select a debit account so for example if we want to do cash on hand in fact if we've paid for something we can do that or we've got received money from a specific person we can do that simply by entering the amount and then selecting the other account so if a customer has paid us from accounts receivable or from an invoice we could just do invoice and then we can go ahead and credit that and that is how it's going to work and if you use your trusty intern in correct balance we're gonna get a warning saying hey you cannot do that it's got to be doubt and so we're gonna go over all the rules on that and once it's injured you're gonna get a green confirmation then it's going to be entered and that is going to be available both in the cash on hand in the accounts receivable accounts as well as the general journal so if we were to look in the accounts receivable and we're gonna go ahead and see that transaction right here automatically so that's going to be great and as well as the cash on hand as well so that's going to go up automatically based on dates here based on the last time cycles so there's a lot to show you in this training we have had it in fact we are going to be able to have all of our transactions located here in a list of transactions so this is a great way to have your general journal and it's automatically dynamic based on the account we can also have a little refresh here in case we make changes so that's great now when you have all of this data one of the best ways to display that data in types of reports graphs or charts is with our reports and graphs masterclass and that would be the Excel advanced dashboard reports and master class so that's here if you have not seen that yet get a chance so when you combine it this account registered that we're going to be good the general ledger combining it with this master class you've created an amazing application that you can sell in the marketplace in fact this master class here this application I'm showing you is a single click graph so you have lots and lots of financial tools and you can create additional reports as well you also have the ability to toggle columns if you haven't seen this and you also have the ability to add custom reports so if we were gonna select specific columns and we're gonna select specific dates we could then create a custom report based on that day range based on that and into report name and then you have an amazing custom report that it's automatically geared for those specific columns for those specific and date ranges so it's going to be a really great training what you have this is a 15-hour master class so if you haven't seen that yet do check it out we have lots and lots of tools available for you in this as long as a drop down list it PDF picture which we can save as we have the ability to email as excel PDF or picture so tons of tools in this do check it out if you haven't done so yet alright let's get to the training I'll include the links to this masterclass down below so check it out in the description if you want it I've got a promotion going on right now so go ahead and check that out alright back to the dynamic general journal we go so this is an amazing application that's gonna let you expand in fact this is gonna be part one of perhaps two or three parts so because there's a lot to get to here we've got a lot to show you a lot of training we've got conditional formatting a lot of it going on here we also have a chart of accounts and this is the master chart of accounts where you can create your own accounts based on your own account type what you've got also our count two types we're gonna focus on that income our income is a money coming in cost of the goods sold now cost of goods sold otherwise known as CE o G s is specific expenses that have to do are tied to specific jobs or materials or labor that go in to specific jobs or as expenses are expenses unrelated to specific jobs and I'll show you how that works in a profit and loss coming on there are separated we have banker asset type of account checking account cash account those would all be bank or asset types credit card or liability accounts money that you owe in a credit card or liability we also have an equity account owner's equity when owner puts money in to a specific business that becomes an owner's equity account so we've got those six specific types now keep in mind income cost of goods sold and expenses they don't have balances those are not balanced type accounts but we do have total income total cost of goods sold now I am NOT an accountant so I do have some understanding of accounting so please if you are an accountant and you do see something that needs to be modified changed or updated I would very much appreciate your feedback on this so feel free to email Randy at Excel for freelancers comm and email me with some updates and I'll go ahead and put those updates this is going to be a multi-part application two or three parts so I'm happy to make those updates just in case okay so I got some rules wrong with certainly possible we also have transaction types and we can put whatever transaction types here now transaction types are a great way to set what type of specific transaction type you have so we can do bill payment credit card or whatever whatever type of count we want and then of course if we make any changes just click the check mark and it's automatically changing that green color will highlight and let us know that that's been done so we've got that back to the chart of accounts so you can create you I you have the ability to put in a description and a balance here I guess I can put in a balance I just haven't done that yet but I wanted a space for it so we can quickly see what the balances are on it now of course expense income and cosmic goats wouldn't have balances but we could put in a total I guess total expenses but we don't really have a date range on here so at least for the bank accounts for our asset liability and equity those would all have balances we also have a vendors and names listed your just names tied to specific transaction so that's what's something and of course I've got some rules here to remind me and to help you see how these specific balances are calculated and how they go about it so keep that we'll go over this in just a little bit those just a reminder here for me and for you so that we could see how those accounts are affected when they're told all right let's get to it we've got a lot so basically how but what we're going to do is we want to display the specific accounts it is basically a filter based on whatever we've selected here or based on the transaction dates that we've selected here and we have a pop-up calendar you may have seen this this is a pub calendar that I built a few years ago in fact I do have that available specifically in one of our training videos but I'll include it here as well and let's it pop-up calendar that you could actually use in any application this is a shape based pop-up calendar so that gives us a lot of the ability to have a cool look and feel so we can change the color of this calendar quite easily just by selecting a color something I built it is it is a great little calendar tool I guess I should add a year here but we do have the ability to go previous month and next month here so it's a great way and once account once the colors change it will remain so alright so we also have the calendar popping up here as well in the date frame so I'll show you exactly how we did that in case we want to make an update we just click the update and then it's automatically updated alright let's get to it so now we've got also an ending balance here we'll go over that and so that's pretty much it so when you select all accounts that is actually the general journal there's no balance associated really because we're focused on every single account here and then it's a great way we can enter transactions in a general journal so we can enter new transactions here or we can go to specific accounts and enter new transactions as well so we've got a lot of a lot of ability a lot of flexibility with this type of general journal net and I see as we built it out where we're gonna add a lot in fact I'm gonna add in split transactions let's split transactions something as if if you charge a specific if you charge something specific on your credit card and you have multiple different expense accounts that you want to use you want to break that down maybe you charge $100 on your credit card but you want to break it down into multiple types of expenses for multiple accounts that would be a split transaction so I'd like to add that probably in next week so we have the ability so for example if we you went out and you had a charge on your credit card for $50 maybe 45 is for meal and $5 is for taxes so you might want to split that up at and so we do have the ability to do that maybe not all that would be millet meals entertainment all right so we've got that covered and then of course we have our list of transactions this is actually our source data this is the database in which all of our transactions are originated from so this is where they go and the reason that we don't just add the transaction is here because when we add them in our general journal when we add them in specific accounts it gives us the ability to check to make sure that there's no issues for example if we're missing a date that would be a problem so we don't want to add in we want to make sure that we add in a specific date here and we also don't want to miss any accounts so it gives us the ability if we try to save a transaction it's tell us hey we need both to and from accounts you know in this case debit or credit account so we would need to put those in as well and we would also want to have the ability to add in specific names and of course we need the debit and the credit account so that's really really important as far as the name the matching name so if we purchase something in fact if we purchase specific clothing here in fact this would be let's say we have a transaction we're gonna we've purchased some clothes from some account and so we would just put purchase clothing and let's say if we paid cash for it we would just select our cash on account here cash on hand here and we just put our notes in here purchase clothing and then we would go into the debit amount which is actually what we're using the amount let's say it was ten dollars and then we want to make sure if we enter an incorrect amount here it's gonna tell us hey what those need to balance Wow we need to make sure that the debits and credits and accounting are always the same so having them in a register like this will allow us to do just that and we can even add a specific type of transaction here and do type and then click enter and it's gonna enter that automatically so when we go back into our transactions we're gonna see that that purchase was right here purchase clothing although you generally don't purchase from Acme building but you get the point so we have the ability to do that right here so it adds it all up here and I'll probably add a balance here maybe I'll add some type of balance here for that but there would be tied to the specific account so we'll keep that in mind this is just a temp where I call them then we're gonna be adding something and a little bit later on so you'll see that so we see we how we have specific accounts and we have conditions to make sure that nothing is missing and of course if we do want to make a change to that we can just quickly make a change and then save those changes and that quick green confirmation lets us know that that was changed so when we look back in we see that that date has automatically been changed here into both the debit and this is a dual so there's two transactions that are in one for the credit one for the debit account so we can see that has done there and we have our debit account here and our credit account here and everything else has been duplicated so we have that except for the amounts and that helps us bring our balance and we may use this row in the future to help us dictate what row was on when we bring the data back so far we're okay alright so when we have specific council I also want to list the ending balance of that so I want to know let's go ahead and find cash at account so I know that our ending balance is 294 a cash account I want that here so that's the ending balance it's always nice to see up there and also we have the ability to scroll up scroll down and have that header fixed all right so let's get into the nuts and bolts of this to see exactly how we did that and we'll focus first on the on sheet conditional formatting and then we'll go ahead and move over to the VBA it's gonna be a great training so we also have the formulas and what we did first of all we just froze that so I've got row eight frozen here and to do that all I've did was select row eight and into the View menu we just click the freeze pane and then we'll freeze the rows if we unfreeze it and then you'll see it's scrollable but then holding clicking on the row eight and then the view and then freeze panes is gonna freeze that so that we can scroll up and down this nice this gives you the ability to always be able to see the headers which I really like especially for the accountable and we may do an automatic scroll to the bottom so we may do like once you get your you have a lot of transactions you may not want to continue scrolling down so what we can do is we can always auto scroll to the last row with a value so that might be helpful so you can quickly enter transactions I also may do automated so if there display accounts cash on hand I made automatically have that select here so let me know if that's something you want to see so we may automatically do that as well and if you just if you enter transaction when you click refresh you don't save that transaction I just made it so that it's cleared out so you actually have to click Save or click this checkbox so that you can save the transaction so that's handy and also I had the ability to void I would like to void those transactions so I'm gonna have that ability hopefully coming up all right moving on so let's go over how we did that now the first thing I want to do is when I select a row I'm gonna highlight that row in orange and I want to use in a 2-tiered color light orange a little bit darker orange to do that and so the first thing is I want to know what row the owners the user has selected so over in columns a and B I've got a few additional details and you'll see that the selected row and b2 automatically changes so that when we select a row in fact no matter what role we select it's going to be the even row so for example the user selects row 13 automatically 12 is going to be placed here if the user selects 11 automatically 10 is going to be placed here in fact I need to add this you see this one didn't so it's got to start at calling D so let's let's make that change and we'll also show you how we did that but not quite not in the second here not in a second so we need to add that in and but it does work in the first row so we'll do that all right so moving on what I want to do is I want to show you exactly how we got the even row here in b2 so let's go into the VBA if you can go into the developers tap here and click on Visual Basic you can also use a shortcut key alt f11 if you don't have the developers tab available you can go into the file options and into the customize ribbon and just make sure a developer is selected here that'll get you the developers River so let's go ahead and we get into the VBA and now we've got some macros caught on the general journal this is the on sheet macros and what we were focused on right now a selection change selection change now the first part of this has to do with our calendar in case it winds up missing it'll replace itself so we're not going to focus on that right now and we're gonna go down here and we're gonna see if the target there's a selection we're gonna say if count large and this helps us avoid bugs when a large number of cells is selected you'll see sometimes if you're working with Excel with VBA and the user selects a large ourselves automatic who's gonna create a bug here but because we have count large is greater than one then exit sub did it doesn't create a bug here soul in fact let me just change the color you'll see there's numbers that are tied here I want to change this close to black so you can see that and I'll go ahead and show you what those are soon but that's a little bit of reminder ok going back in so we understand why we use account large now if not intersect the target g 4h 4 or d8 through be nine nine nine nine what is that that is for our calendar that's going to show our calendar so we have these two cells g4 and h4 or d8 through Dean $9.99 why is that let's take a look at that into our Singh we have here we want our calendar to appear under g4 when it happens to that we also want it under h4 we also want it if we select any specific in but not any specific one not not any specific indeed not not odd rows only even rows right only even rows so how do we do that let's take a look so that would be d8 d-10 d12 and so on and so forth so only the even rows so let's see how we did that so we can use this specific in fact this is the reason why it's not coming up why our intersect here is nothing I'll show you that in saying if the target row mod two equals one then exit the sub what is this it means if the user has selected an odd row if it was here if we did this it would be an even row if we do this one it will be an odd row so that's basically saying the mod of two if it's one right that means like the remainder of the remainder that means it's an odd row the user selected that ended we don't want to show the calendar sheet this we're not gonna go over to inch this checks for the making sure there's a pop-up calendar sheet and this shows the calendar and this else so basically this has the ability to check for that calendar in fact let me add this up here I'm gonna add this I'm gonna take this I'm gonna drag it right up here and why do we do that well what I want to do is I want to highlight the row I wanna highlight the row which does that here in to be two but I wanted to do it before we exit the sub on an odd row why is that so now when we select now when we select a specific now you see it highlights remember we'd selected an odd row and it wasn't working but now it is because I just moved it up I moved it up because there's no exit sub right now so that works just fine I just wanted to show you that remember we had a little bit of an issue when we didn't select all right so now so when we make a selection b2 is changed to the row so let's take a look at that when we make a selection from anywhere from d8 through I $9.99 that is our entire table it's nothing then do something what do we want to do if again here we go the target mod mod equals zero this means it's an even row if the user has selected an even row 8 10 12 14 16 right then do something then b2 value equals the target row else b2 value equals a target row minus 1 why is that - so that means if it's an even row if it's an even row then put that row in b2 but why why but if not if it's an odd row then take the whatever row they've selected subtract 1 because I only want I want to show the beginning in other words if they select this transaction I want to make sure that 12 is here not 13 because this is our starting because our transactions take up two specific rows so I need to cover both of them I always want to know what the first row of that transaction is right there's two rows I want to know when the user selects 17 I want to know the first row is 16 so on I always want to place it that's going to help us for a lot of reasons for our conditional formatting and for different and when we enter transactions - we want to the starting row so we want to be specific about that because this particular transaction takes up two different rows so it's very very important all right moving on so now we know what row now we know what row we know why I chose row 16 when we select 17 we know why it shows 16 so we did that with that but how do we get the color to show up well let's go into the conditional formatting and show just how we got that orange color just highlight any cells under the home and conditional formatting and manage those rules we've got several rules here we're going to go over the orange one right now these two our orange and all we need to do is base it on the row in b2 so when we edit that rule we'll see two conditions - we're gonna use two conditions and the row this is the lighter orange the row of b2 equals the row so in this case it would be eight right eight is the one highlighted in this light orange so that means B 2 is 8 and we need to make sure the d8 now you notice the missing dollar sign in front of 8 that means it's gonna be for any row in the table and the only thing you want to make sure of is that this 8 is also for this particular type of coloring this 8 for every row we want to make sure that our also that applies to starts on row 8 so there's two conditions that means that I don't want when we when we select a cell we select so nothing here I don't want it to be highlighted unless we actually put it in a data and then I wanted to show them what the user is on so I just didn't want to show this when we don't select anything we could probably add it so that that we had a row that we if we added in maybe one below we can edit it but basically when the user selects something else I don't want it showing up there's no data here only when their specific data all right so we've got that covered and so that's how we do the orange so let's go back into conditional formatting manage rules and take a look so we know that now what about the darker orange now when we edit that rule see just a little bit different that's going to be B - remember that's our even row plus one so that's gonna be one row below and of course d8 again we don't want it to show on rows with blank values so there's two conditions B 2 plus 1 in this case it's 20 plus 1 which is 21 and we have the ability that's going to give us our darker orange color and all we're doing is formatting that giving dark orange and of course we're going to apply that to starting at 9 and 12 9 here we want this show 9 which is the first row below and of course this one starts at 8 so we have different rows because we don't want it start so this one starts at 8 this one starts at 9 it goes all the way to 3/3 yet that's just a number I picked you'll probably want more to add more on to that cuz you're gonna be when you could use this you're gonna be using thousands of rows alright so we've got that covered so now we know how to do that now let's move on our conditional formatting now when I enter a transaction did you see that it went green I'm gonna use conditional formatting if we enter a transaction let's say we're going to enter another cash on hand and we also want to enter again it'll just put anything in here and we can do other income exactly and we can scroll down our list call it other income and we also have the ability to add in numbers here I left a space on here on chart accounts to add in account numbers we do have that I just haven't added mid for easy but account numbers just because your account numbers will be in fact there's no data validation on this let's just clear that data validation while we're at it so these are this is where you can add in account numbers but let's go ahead and clear that validate it data validation and we'll clear that clear from the selected cells ok so you can enter an account number here I've left a space for that alright continuing on so we have test notes here and then we'll just enter the credit of 100 and you'll notice that this automatically goes to green very quickly and you'll see that means it's the transactions been insured so that gives you a nice visual to note that the transaction is enter a confirmation so to speak and so how do we do that well the first thing we use conditional formatting I want to know what row it was just entered we do that through VBA so we've also done that through here under saved ro B 5 B 5 is our saved ro so soon as this becomes 20 right as soon as that becomes 20 it turns green so I have that turning green and then I have a turning off so I used to delay so basically all I do is in travail bi in 220 and then I clear 20 like that and I do it pretty quickly and you can delay it as long as you want you can put a delay on it we do that through VBA we'll show you how we do that in VBA shortly but let's show you how we do the conditional formatting so I'll put in 20 here and all we're doing is pretty much the same when we go into home in conditional formatting and manage the rules I have to highlight these specific cells and then we'll go into manage rules and we'll see that all we did here it's just a very simple row of b5 + 1 that would be for the lower cell the lower row and for the top row we just have b5 so that's how we call it those two rows just temporarily while the transaction is saved so our saved row when we go through the VBA you'll see that but our save row quickly gets added to b5 and then it gets removed so again gets quickly added and removed and that's how we create that green confirmation when an account is injured because I like a visual knowing that we could put a pop-up message but then the user has to click OK every time and that's kind of annoying we could put a we could put easily put a fade out message which you've seen but I wanted to try something a little bit different here something quick because when you just add a lot of transactions I think a fade out message is not necessary so all they need is just a very quick confirmation that the transaction has been injured and even saved so if we make a change to that any type we change we click save and then we get that green confirmation it goes out we could actually extend that for a little bit but you get the idea ok so we've got the ability to save and when we refresh that we'll see that it's based on dates now it's based on dates so we just added a date in here and it automatically gets Reese ordered based on dates and you can change that if you want you could reverse the order or do whatever you like if you want your current dates up at the top but I've just done right now all right so continuing on we have shown you both the orange and the green conditional formatting and now what I want to do is when I enter a new transaction I want the rows to keep going down so when I quickly enter a new date you see how the rows skip and if we delete that automatically the rows gonna and that's used we use conditional formatting for that it's just so this as we enter transactions the rows go down so that the user sees they have the availability of the next row and the next row on the next row and that's a nice little trick it keeps things nice and clean and a lot of users to enter transactions without looking on a big blank list it kind of keeps it nice and simple so how did we do that well I used a several conditional formatting rules and we'll go over those rules right now we highlight the table going into the home conditional formatting manage the rules we'll see that we have three specific rules based on the blue the blue and let's take a look at this one here edit the rule now this one applies to D 12 through 3 3 3 8 and what I want is this rule let's take a look at this so basically in this rule we have it's starting out where our table starts out d8 does not equal empty D 9 does not equal if one of those two do not equal empty and and other conditions D 10 D 11 and modern I know it's a little bit confusing but let's go walk through that step by step okay so let's start out here if d 8x so we'll start at the lower row it's a little-bitty we're saying if there's two D that are empty in a row empty in a row then make sure this is blue but as soon as the user fills out either one here or something here if this one or this one remember we start out at 8 we start out at 8 or above actually but basically it's used for any specific row so we're saying if something if the first option or the second option has a value then don't color the rows below but the ones below so we want to color those so that means we can have this one or this one has a value but this one is empty this one is empty then color it blue so let's go back into that and see exactly how we did that manage rules so again we look here and we go into this edit or the or the one above or the one below if those have a value if there's a not empty not empty and d-10 and d11 or black that means the one below so we have we get we have one here one here those can't value these two are blank if the next two are automatically blink I just messed it up if the next two are blank then color it blue let's take a look at that format and also on odd rows too so we have one for odd one freedom what are we gonna format it's the same blue background and we look on the border we want the I want the automated border to whatever the automated border is here at the top but cleared out everywhere else so that means I want that thick blue line that thick blue line is our actual border this is our actual border so if we were to room strip out all the conditional formatting you would see every row has that thick blue border but I only want to show up on the last row I only want that thick blue border to show up in the last row so again what's going back into this rule and so we only want that to show on even rows right even rows I don't want to show up on an auto auto would be 25 I want to show up on 26 or 28 or 30 right we don't want it to show up on 25 right because we want to use that dotted line here so we only want to show up 26 27 so if we were to remove that so let's let's remove let's remove these two then we want to show up on 24 you so see so it's we want to make sure that that thick blue line only shows up on even roast that's why we use again the mod we've used that both in the code today and we've use it in the conditional formatting so continuing on so we have that that's why we want that thick blue border let's take a look at the other blue and at the rule and this one here is if there is four in column D if there's four that are blank in a row color not only do we color it blue but we do more so let's say there's four in a row eight nine ten eleven remember it starts out but these could be any four because there's no dollar sign there's no absolute before the number that means it's for every single row in our table okay so if we format this let's say we see no boarders in this format no borders that means if there's no data indeed I don't want any borders to show up and I want this fill blue so we use that rule for all of the data down here and let's take a look at this this is also very similar very similar but this particular one is going to be on the odd rows the odd rows remember this one be eight here we go this world is the last rule d8 does not possibly does not have value or d9 but I also want to show this for the audre so we had one for the even and one for the odd but for the odd format and let's look in the border here there's no borders no borders no borders in the odd row so that so that basically gives us the ability to not show any borders down here and as soon as a user enters a specific data in one of them automatically the rows show up below so that's how you do it so that's how we do it I notice I went a little bit fast but we got a lot to cover today so I don't want to go spend too long in conditional formatting of course I will make this available just go ahead and check out the links below and I'll make sure this is available and of course we do appreciate your support so these classes the master class as well as the ultimate excel resource guide I'll include the link down there below if you want to pick those up that helps us keep this training free so I really appreciate that alright moving on when we let's go ahead and take a look at any others that we might want to see in the conditional formatting before we move on to some code we will see that we now have bro we have another one what is this let's take a look at this rope if d8 does not equal empty and the mod row equals 1 that means odd rows odd rows I want to put a solid blue line at the bottom odd rows what is that that is for this here you see this here on 19 that's got a solid blue 21s got a solid blue 23 it's got a salt but why do we put a solid blue on the odd row this has a value or this or this has a value here then we want eight solid blue line here so even if we enter this even if we enter this here when we enter the date all we get a nice solid blue on the odd row on rows 23 21 19 so otherwise it's a dotted line so that helps us with conditional formatting so we go back in here and conditional formatting manage rules take a look once again so the conditions are d8 we do not have a blank we could put an or d8 but that's okay because there's always going to be a date so that means the first row must have a date and it must be an odd row so in those cases put a solid blue line that's how we get the alternating solid line not for every row if we were to chain if we were to remove this it would show up for every single row so we don't want that I only want that on the odd rows okay alright so we've covered that let's take a look at this rule now in this particular case we have D 8 D 9 to 10 to 11 to 12 in this case when all those conditions what is this in this case we have just the upper row here the upper row and that helps us to give us that thick border at the bottom when all those conditions are present so I wanted to show you that as well that helps us with the formatting so these are the rules we just went over all the rows we went over the green we went over the thick border we went over to the orange and we went over the blue the three blue that help us determine and show just those rows with values and the newest row so it gives the user a guidance as to what to do next so we have that guidance so once it's deleted there always have at least one available row at the bottom or they get into transactions that is the point of the conditional formatting alright so we've covered conditional formatting let's go ahead and get back into the VBA on the selection change to see what else we are doing here all right continuing on we went over selection change and also I want to save button on some things I wanted to show the Save button but I in fact I want to turn it off when the user has selected something else so if the user selects d8 you're not d99 that's okay we're gonna keep that Save button fact we don't need this at all that was and then what they want to do is set I want to set the new transaction to false if they've selected some transaction so let's look at that we we have two differential here in to b6 we're gonna show whether it's a new transaction or not these are existing transactions so I want to make sure that b6 is false but when they select a new transaction I want to show that we actually have a new transaction once we in turn use that buy b6 of b6 is going to tell us if it's a new transaction or not but I want to make sure that we know it's not a new transaction when we select so b6 tells us it's not a new transaction also the transaction numbers always going to be here and it's gonna be hidden but this is our transaction number so if we look here we also have a transaction number a list of transaction that transaction number is actually carried over to the C column so we can track so we know that any transaction that were users entering that doesn't have a value here it's gonna be new so that's we need to know whether it's new or not why do we need to know it's new or not because we need to know whether we're going to add that new transaction down here or whether we're going to update it here so for example if we select a specific transaction let's look into let's say transaction number 9 we see said so far to Fred and we want to add it last name to that Fred and before we added it we have before we added and we'll see here on transaction number nine if we click here we'll see that it's sold to Fred the memo here in both rows but if we want to change that all we need to do is click this updated green check box and now when we go back into the sir now we say Fred Fedders it's been changed automatically so we need to know that so we need to know what transaction number is so that we can locate it we also have the ability to locate the row we know the transaction rows here how do we know that row well what I've done is created some named ranges for the list of transactions that it's going to help us moving forward so how do we do that under the formulas name manager let's take a look at some of the name ranges and they start with all transactions so when we look here transaction let's bring this here and open the refers to so we can see that they're all offset formulas we need those named ranges are dynamic so as our table grows so does this name let's take a look at the transaction number we're gonna edit that and see how the transaction numbers the name we've been given if they all start with trans dee underscore and then whatever the label we've chosen and this is gonna be offset d4 d4 is the header row why did I include d4 in the header row that is because I do not want an air to come up when all of the data is cleared when you get this file you may want to clear all of your data I'm including the header row so that this offset transaction doesn't have an error there's always going to be data but I've set it 100 or one row below so this way it doesn't include it so when we tab into this we will see that it actually starts at the one row below below the for all so we're going to count all of the values in here we're gonna count using count a we're gonna count also from the header row and we're gonna count all the way to 9 8 7 except we're gonna minus 1 because we're starting at the header row we don't want to include that in a row we don't want to include the header row in the counting but we do want to include the header row in case there's no data so there's no issue so we subtract 1 and that gives us an accurate named range a dynamic game rate so that as we add values this changes and we've done the same thing for each of them we've got transaction type here I'm sorry we've got transaction date here we have transaction debit account here we've got transaction credit account here so we can see the credit so that counter we've also got debit amount and credit amount so the transaction credit amount is here that's gonna help us for totals we've also got transaction debit here that's going to help us for totals so those are all dynamic they're all based on specific data in this table and that's gonna help us moving forward so next up I want to know when user selects it I want to know if we have a specific transaction row so how do we know that we know the selected row is going to be 18 right so we need to select a transaction number we're going to index what are we indexing we're gonna index the selected row 18 and we're gonna use C we're gonna index C 1 through C 9 9 that's gonna index all of these numbers here and tell us what wrote what is in row b2 so for example we're gonna index everything here and I want to know what's in row 18 on that index 8 8 is used 8 is going to be our result so for indexing the entire C row right we're going to remember it when we index there's a few we need the first part is the array the second part is the row number and the third is the column number so with the column numbers just one because we're indexing we're not indexing more than one column we need to know the row to get the value but we know it arose in b2 I showed you how that it so that's going to tell us our selected transaction number so as we selected our transaction number is going to change unless we have a new transaction then of course what transaction or is not going to be there because it's a new transaction so once we have our Trent what selected transaction number we can know our transaction row that is our row in the database which is 17 so for example so selected transaction numbers 13 our database row that means a row in the transaction is 17 so when we look at 17 we will see that our first row of this transaction is located right here in two lists of transactions 17 so that gives us our number so we because we need to know when we make it update I need to know what row we're going to be updating so that's very important so I need to know that through our DBA code so we need to know the row the original transaction not just the number but the row as well so we've got that there so that also tells us where to make those updates all right continuing on with our code if it user selects a change I don't want the green check box to show up because they haven't made any changes but what if they make what if they make a change what if they've changed this to 4 4 when they make a change I want them to be able to save those changes and they can quickly do it by selecting the green check box now this green check box once we make a quick change it's gonna show up let's take a look at that right click on this box all it is is a shape of the square and then an icon above it I've given that a name called Save button we're gonna tell that we wanted to appear in column J I want that Save button appear in column J of whatever row that we have selected and of course if we select outside the South we select outside I want to remove it outside the table I want to remove it so let's look into the VBA code and see how we did that here is selection change if we select outside then else in others if we select inside d8 we're going to do these things else else means outside that range then hide that button outside the range hide the button inside this range don't hide it outside the range hide it so again let me show that to you again in if it's if it's if it's already changed right and and the user selects outside I want to hide the button because I only want them focused on this particular transaction when they're making changes so that's how we do that all right so how do we get that button to appear when user makes a change to any see any transaction actually I wanted to appear so all we need to do is double click on anything and exit out or make a change and it's going to appear so how do we do that we do that on work she changed and I will be up here worksheet change here so if a user makes a change to anything in da 9:9 and b1 is false why is b1 false well there's also there's two types of changes there's the type of change when a user double clicks and makes a specific change or changes an account or changes a a date or changes a transaction type that's one cup change and there's another change where we actually select on specific count and have it reload automatically so that type of change is dictated by one cell so I don't want the green to show up when that type of change has made two types of change so how do we differentiate between those two types of change well use this load journal right and I'm gonna put this in b1 is false so when we run the code to refresh this this goes to true and then it goes back to false so if this is true that green check box is not gonna show up it's only gonna show up when this is false now when we run through that code I'm gonna show you how this becomes true and then it becomes false it's too fast I think it's too fast to see it now you can't see it oh you can see it very quickly you can see it very quickly change from true to false so when it's true those types of changes don't aren't gonna cause that check box to show up in a row here only when the user makes an actual change so that's what we need to make sure before we show that green check box that b1 is false if it is then continue if the current Row is an even row then b2 Baynton we're gonna make sure that the target row and b2 the road that they've changed so again we want that row in B - I want the even row so then if they've if they've made a change to an even row this will be 0 then b2 equals that but if they've made a change to an odd row 17 1921 then b2 is going to be ro minus one because I always want the even ro to show up in b2 I always want so if they make a change to be 15 right I want to make sure that 14 is showing up here and b2 so that's we want that that's so we that's the first thing 1 the next is we're gonna run a macro called show save button that's the macro that gets that green button to show up let's take a look at that and view that Mac through definition we're gonna see this small macro here called show save but and of course we're focused on sheet 1 sheet 1 that's really important cuz we're doing everything was on sheet 1 and she one of course is our general journal so we're gonna dimension the selected row is long that's a long and then b2 if B to his value Beach is very important so for some reason it's empty it should not be but if it's empty we're gonna exit this up just to double-check the selected row here this long is gonna be b2 value and now with the shapes Save button with that button right with that little icon shape that's the remember this is the name I've given it say we're gonna do four different things or five different things the first thing I want to do is I want to place it to the left of J Jade give it left column J and whatever selected row I also want to put it on the top of J in the selected top row and then I wonder what I want to do is move it a little bit to the right minus 3 would be left okay so I want to move it a little bit to the right and I want to move it a little bit down minus 2 would be moving it up right and so I want to move it a little bit and then I want to make it visible so we're gonna do those five things left this is these four lines position it this line actually makes it visible so let's take a look at that CIND we'll zoom in just a little bit so we can see so if the user makes a change to this it's gonna show up and of course it's not gonna allow us it's gonna say they're not equal which is exactly what we want and let's zoom in here and take a look at this you'll see how it's a little bit down a little bit to the left now if we don't use those increments it's gonna put it right like this right on top and right on the left I don't really want it there I want it right in the middle I wanted to put I'm gonna put it about down three spaces and a blue the right three spaces so that's what I want to move it down them to the right and that's how we do that so that when we change this and we actually make it accurate and then we want to save those changes we just click the green and then those changes are automatically saved all right so now you see how we get it to position and why when we actually make it change our green check box appears so we've run through that macro now let's go ahead and take a look we have low transactions and we have transaction save let's go into the transaction saved so we just did that we made a change and how do we save that register now remember the cool thing here is we can use this green to save existing or to for new for new transactions if we have a new specific transaction let's say we enter a new cash on hand account and we can use any actually and we put in the name here we can use this green one on new as well so the only thing difference is the row we either gonna take the row and let's see 125 and the credit of 125 and we can give it a transaction type any type and we give it another account here so we can do let's just say owner's equity or test income or anything like that and test notes and so on this particular there is no row so we want to use the same macro if we right click actually if we right-click inside inside of this shape and we click assign macro sorry I right-click assign macro you can't see that it's just down below right click assign macro and I see that the macro that special sign is called transaction save transaction save so we use that regardless of the new or use but we just have to differentiate in the code so when we click Save it says both to and from accounts aren't required so let's go ahead and make sure that that happens there we go okay so we've entered the transaction and now we can move on so you'll see that now we have the brand-new transaction right here oh sorry right we here here automatically sorted by date here in the transaction which is really really helpful you we can sort by transaction number as well so there's a few ways we can do that all right so into the merican let's just see how we do that how do we save based on an existing or new transaction so back into the DBA we have this macro call to save transaction and the first thing we're gonna do is we're gonna dimension a few variables here the last transaction er was long the selected row as long and you know what row the transaction row I need to know where in the database the transaction and the transaction number so we're gonna need all those and we're gonna focus primarily on sheet 1 so if which she want for the beginning the selected rows in B 2 we did go over that remember we did go over that a lot B 2 is our selected row and I also need to check for three things I need to check for a balancing transaction that means the credits and the debits must equal each other that's important we need to check for the proper accounts and we need to check for a date all those things are required notes are not required and the transaction type is not required but these things are so the first thing we want to do is we want to be able to make sure that our accounts so that would mean this amount equals this amount or this amount equals this amount right so our debits and credits or credits and debits the hand counting these have to be equal it cannot we cannot make a change to an existing and have it not equal if these two are not equal and then you try to say but we need a confirmation to this head please make sure your entry is in balance debits must equal credits debits must equal credits so we have to make change to both that is a dual counting system we have to make sure they're the same in order to interest so now when their intricate does save it and that is what I like about having a separate entry to the separate as opposed to just entering it's so easy to make mistakes if you're just into them but when we have a separate entry we can have those mistakes check first so that's the first thing we want to check for it is to make sure that the balancing transactions so we know our selected row so if G and the selected row let's move this down here let's move this up here and we'll bring it down so that you can see so we can follow along here so if G and the selected row do not equal H in the selected row plus one so that would mean here if G and the selected row plus h and the selected row plus one do not equal each other then there's a problem or if H and the selected row plus G in the selected row plus 1 do not equal each other there is also a problem so we've done that right in here if G in the selected row value here this is the first check or G plus 1 right and H do not eat which other then do what then give a message box please make sure your entries and balance debits must equal credits and then exit sub all right next up we need to check that there's actually account so we want to make sure that in the selected row is not empty or e in the selected row plus one or not empty so that's right here these two fields both accounts are required so we need to make sure that they both contain values E and E in the slick road they must both have that we don't want the user to have the ability to enter transactions if they're both accounts are not valid and last up we need to make sure that there is a date here so when we look at here date if D in the selected Road equals empty then mesozoic police enter value date we kind of make sure D in the selected row contains a date dates very important transactions so we need to make sure so we've got those three checks all right now that we've made sure that the user has entered all the information into these three checks we can then check to see if it's a new transaction or not and remember B 4 tells us if it's a new transaction or not B for transaction but of course if this is empty if our transaction that we know it's a new transaction women over this formula ready we still match formula based on the transaction number in B three matching B 3 we've went over the name drains transition number and then of course we're gonna add for why are we adding four because our transactions actually start on row five so if if our first trends number returns the one we need to add four so that we can get row five this would matching would return one for this so adding four would give us the row five we want the row number not the match number so our road that's why we add four on to this of course if there's an error it's not found and we want to keep it blank if there's an air that would mean that no rows found it would be a new transaction so if if this is blank for example in this case it's blank so we're entering a new transaction B for is blank and we would know that the row must be so if it's blank we need to find our last call a last row of value based in column D and then the next one would be our first available row that is going to be our row otherwise so either be Thor either 33 is it going to be our row for the transaction or it's going to be whatever is in here before 23 in this case so we differentiate our row so if we go into the code here if B 4 equals empty then we have a new transaction else it's an existing transaction so we these just this few lines of code tell us a new or existing transaction everything else in this macro it won't matter whether it's new or existing because we've already differentiated there's just two items we need to focus on between new and existing what is the transition transaction row or trans row in this case so if it's a new transaction it's sheet 3 T 3 is our database right our sheet 3 here's transactions D 99 and Excel up row plus 1 that is going to give us our first available row first available row in this case it's 33 that's our first available row if it's a new transaction and the next up is the transaction number I need to assign a brand new transaction number to this because it's a new transaction so how do we do that well that's in B 7 how do we get that let's take a look at B 7 in our general journal B 7 we're gonna use the max formula we the max maximum of all of the translation transaction numbers all of them plus one that's gonna give us our first unique transaction number that's why they're consecutive on one if there's an air for some reason that would mean perhaps there's no transactions at all if we've cleared up the database there's an error and there would be no transition so in that case just set the first one to one that's where we have one here if there's an error we wanted to one so that our first transaction number so if there's a match if there's no transactions at all we've cleared up the database this would be an air there's no max right so that means it would revert to 1 as our first transaction number so this is going to give us our next transaction over because we've added one onto the maximum of all of the transactions so b7 is going to equal our next transaction number that's going to be four new transactions so here transaction numbers b7 4 new but what about if it's an existing well if in existing we know our transaction is in b4 the existing transition row and B 3 is our existing transaction number now we're ready to go now we're ready to enter all of the information in this over to our transactions table here in fact we're gonna use two rows now use two rows in traditional accounting two rows the debit and the credit aren't separate row so we're gonna do just that and then everything else we're just going to repeat we want to know our in fact I should freeze this let's go ahead and select on the row five I want to freeze that row so into view and then freeze the panes we're gonna freeze it so now we scroll up we can see we want our debit account in H we want our credit accounting high alright so back into the VBA we go let's take a look at what we're gonna be doing so the next up sheet three D what is D let's take a look at here and then go into our VBA cut I'll shrink our code a little bit so we can see it D of course is our transaction number D is our transaction number so sheet 3 D in transition row through D cuz I'm gonna put the same transition for both both of the rows I want to put them both here that transaction number on both so we want to do that here so D plus the transaction row plus 1 both of these both of these equal the transaction number if it's an existing it just gets automatically replaced but it's gonna be the same value all right E what's you need that's our date and I want to put the date in both of these and call them E I want and of course column E our date is coming from D and the selected row D in the selected row here or it would be in 1012 all of our selector and of course our selected row comes from b2 so that's where our dates going to come from all right next up F both let's go back into transitions F where it's in F F is our transaction type so we want that in both values at F so f and the transition row and F plus 1 equal D and the selected row plus 1 that's our transaction right D and the Select row plus 1 is our transaction type RFR row is 16 plus 1 is 17 DS here our transaction table here here here and here okay so this is where we put in transaction type goes there in D so we put that there all right next up F is our name or vendor I just want to put that in column F our vendor or name here F so we pull that of course we pull that directly from here F and our selected row F is going to come from select row so we've got that F and our selected row that's where it's going to come from next up we need to do run a check I want to make sure that some users into the debits or accounts first or either way so we're gonna check if G and the select Row is not empty then do something what is that if G and the selected row is not empty then we know that as the debit first debited users debit it first so we are going to make sure that cash on hand is our debit account and we know that our credit account is interest paid in this case so we want to make sure that we do that otherwise if they've injured generally if they've entered the credit first here and left this blank we want to make sure the cash on hand is our credit and our debit would be interest paid based on what they've entered either here or here or here and here so we can do that with this code here if Gina selector does not equal NP then our debit account is in H right our debit account we're gonna put in H isn't is coming from E and going to H coming from E e and the selected row in the selected row and it's going to go right here into H and our credit account is gonna go when you I so we've done that here our credit Row is in I and it's coming from E in the selected row that's the row below otherwise if G is equal to empty if G is empty that would mean that the credits come first the credits come first which is a bit non-traditional but I don't want to make sure our credit account or credit account is in e'en the selected row is coming from H going to H so this is going to be our selected row plus one is our credit account coming from same account here but it's in this case it's H H so we want to make sure that H our debit account is here else else what H right plus one H of course is our debit account would come from e plus one right the one below so in this case right let's just go over that one more time it's a little bit confusing if the user has let's just do that for example let's make a change if the user has elected to do this and this even though it's non traditional then we know our credit credit account is cash on hand our debit account is interest paid so we need to make sure that our shows that that our debit account interest paid would go into right here interest paid here and our credit account would go to cash on hand our credit would be cash on hand here so we want to reverse those in that case so we need to run mix it through the code there so we can change that and we've done the same thing with the amount now with the amount exactly the same let's take a look at that here under debit amount if G in the selector Road we're gonna take our mouse because we have to put in our debit amount and our credit amount debit amount and credit money and I also want to make sure that this if our debit is here I want to make sure our credit is clear here and I've cleared here and put it balance here so it's either gonna be this or this this or this so then I want to clear those values out there I want to make sure that there's only two instances of a value one in the debit one of the credit and not not here here so I want to make sure that's an we do that through these lines of code here so all we're doing here is we are taking our values and we're putting it into column J and column K and then clearing out the contents just so that there's only two values or we're doing it here in here basically all I'm doing is I'm just gonna put in a value here and a value here clearing out the other two okay so that's pretty much it again on transaction 11 here we're gonna put in the debit here and the credit here so that's how we do with those lines of code next up what we do is we want to put in the memo of course that's gonna go in column and our memo is gonna go and call and Men and it's gonna come from the transaction row plus one it's gonna come from the transaction here's our memoirs or a transaction row here's one so it's gonna come from F and the transition row plus one so that's how we put that there I want to put that they're here and equals F in the transaction we're a plus one that is where it's coming from and it's going to M in the transaction plus one through and both values in it all right next up the range C in the Select row equals the transaction number I want to put that transaction number right here so if it's a new transaction I want to make sure that transaction number goes right here into C and the selected row because that's how we do it okay so C and the selected row equals the transaction number I want to hide the Save button to do that with this line of code we are going to take b5 and make sure b5 equals the selected row that's very important because we want to show remember if it's I want to show it in green right so we do that here so if B 5 because the selector OC is 22 I wanted to show in green but I only want to show it for a few seconds so this is how we do that in the code so B 5 equals the selected row then we're gonna wait a certain amount of time then we're going to clear B 5 that is how we do the conditional formatting so if I were to take this and make it 2 3 and we were going to save the transaction you see it build let's clear it out just so we know so let's say we made a change here we want to save those changes let's go ahead and just click it and now we want to save those changes we're gonna see green lasts a little bit longer now 3 seconds so you can change that by easily maybe I'll make it too I think three is a little bit too long one's a little bit too quick so - but you can change this to the number of seconds that you want that green to show up to show the user that that is and as soon as we clear the contents then no longer that green conditional formatting work and that's it so there you go that's the next thing I want to do is when we enter it I want to resource this list I want to take this list and I want to restart it based on the date so that we always have our newer transactions at the bottom this is a great way to sort that so next up I want to restore it so we can do that with these lines of code right here let's bring this down a little bit so we're going to restore it to list we're gonna use with sheet I guess we couldn't do that okay so with sheet 3 we're going to re sort this list and Howey the first thing we want to know is the last transaction row from sheet 3 which is our transactions D in the last row and need to get the last row we're gonna use D so in this case our last 32 so we need to get the last row and then we're gonna sort the list based all the way from the information from D through n so we're gonna do that starting with on e5 e 5 is the date right 'if 5 is our first date that is what I want to sort it based on 'if I've right here so that is the gonna be the first date and we're gonna sort on the table is going to apply to all the way from d5 through and five so we can apply that here so we're going to set the sort and the range is going to be D through M I guess yeah call them doesn't bro doesn't matter so deep here and it's fine this this is just it's I haven't used this yet we haven't used this rate up but I'm thinking about using in the future so I just put a spacer there so it's not there's nothing in bro right now but in the future there might be alright so d few an the length this is gonna sort our list based on date and that's it now we're gonna reset the calculation that just turns on screen updating and calculations that macros right here reset calculations stop calculation this speeds up the process alright so we've gone through we know now know how to save a transaction but how do we load the transaction if you remember correctly we have a specific macro when we changed this account we automatically get the transactions loaded and it's based on the date here so when we select all the transactions it's gonna refresh and show all of them so how do we get that to show up well I have we have gonna use an advanced filter so we're gonna take this day then we're gonna run it through an advanced filter based on some criteria the criteria is gonna be based on the dates based on debit or credit account if there are any and of course we are going to then get our advanced filter results we're going to take these results and we're gonna bring them in to our general journal here so if we select just a specific account let's say accounts receivable will show just for specific transactions based on these dates so again going through our list we're going to use our criteria accounts receivable as the debit or accounts receivable as the credit bring it any results based on these dates that are provided these are actually dates they're based on numbers but we're using formulas to bring it in and that's gonna be helpful so you don't have to worry about dates based on a formatting which is really helpful and so we're gonna take those results whatever results we get and we're gonna bring them those results just the limited results bring back into our general journal we can do that all with the macro refresh it's the same macro that we run when we click refresh you really probably don't need that refresh button but for our purposes here and of course these row numbers they'll be hidden and just changed the font color to light blue and they'll be hidden alright so that is how we get that and let's continue on with our macro that refreshes the code here into the macro let's go up here and to big and bring this up so that you can see this macro and we're gonna look in the transition module transaction macros and we're gonna be focused on one called load transactions load the transactions so of course I need to dimension some and it's like the last results row what is the last results row last result row is going to be here the last results these are our results so the last years well I need to know when we bring over the information I need to know the last row so we need that that's very very important the results row is long we're gonna run through all the results in a4 next loop also I need to know the transaction row that's going to keep track of our row as we run through our transactions here it's gonna keep track of the row that we're running through that's important because we want to skip to and then we also want to have a last transaction data row because we're gonna run an advanced filter so I must know the last row of our original data here in this table in this case that last row is 32 so we've got to know all that information and we're gonna set the load to true b1 value equals 2 remember we went over that we need when we're loading it we're gonna be making changes to this table but these changes I don't want this green on this type of change I don't want this green checkmark to show up on those type of changes so to avoid that we set b1 to true during the macro and then once the macro is finished or before the max first we set this back to false all right moving on let's through our macro here we we're gonna stop the calculations this speeds up the process we just went over that macro we're going to clear sheet 1c 8 through H 9 9 9 9 and what is that that is starting here see A through H all the way not the balances we're gonna clear this current table I want to delete everything in that table because I don't want anything to show up and then when we refresh it I want it all to come back so we want to first thing we want to do is clear out any of the results in our accounts just like that so I want to do that through the line of code and we can do that through this line of code right here and then with sheet three that is our transit transactions sheet I want to focus on that so we're gonna focus on that here right all the way down to here so we're gonna focus on that cheap and of course we're going to clear any criterion results in fact I believe just the criteria I mean just the results here so into the transition if we look down here v3 in fact let me change that note it's just our results not the criteria the criteria it's controlled by formulas so we don't have to worry about that our criteria here's our criteria what do we want it's all based on formulas so we don't need to add it in which is really nice it's all based on farms we'll go over that in just a moment so we don't have to worry about that all we need to do is really clear out these results I want to clear them out so that we can get the table ready to bring in a new results new results so we want to clear that out we can do that v3 of course with G 3 AF although you're gonna clear those results I want to get the last row of the data right we just went over that the last row we need to know that and of course if the last row is less than 5 and go to no data that means there's no data we don't want this macro to run if there's actually no transaction data next up we're ready to run our advanced filter starting in d4 that includes our headers all the way over to and in the last transaction row that is our main data starting in d4 right here I want to take all this all the way to the last row actually we used to end all the way the last row in this case is 32 and I want to run that advanced filter and so we're gonna run it we're gonna take these results where the results are going to be Q through T now how do we get these results we've got dates these are actually dates now in the as you may have seen me run advanced photos where I actually entered the date here but now I'm going to use a formula and a formula is really gonna help us for when many of the regardless of your date format this is going to work so we're using now a lot more often date formulas here in the criteria so that we don't have any issues with the date formats so that always works so for example this says if the general journal is G for is blank then put blank otherwise greater than or equal to general journal G for what is that that's the from date that is the from date G for is our from date so that means we want all the transactions greater than or equal to April first and our next formula is going to be based on H we want all transactions less than or equal to April thirtieth a less than or equal April 30th so I want to run that and also the last countries I want to base it on this account on a based on whatever account selected however if the user has selected all accounts then I want it to be empty I want it be empty so we can do that in this here so for example it's empty here if or the general journal is blank if the user has deleted it right there's no space then or it's all accounts or they've selected all accounts then the result is blank otherwise the general journal equals before so you see I've selected all our results of blank but what if I change that to a specific account if I change that to accounts receivable now this the debit and the credit so we're gonna say or this or this okay so we want all of that all of that and we dis is and and greater than that date and less than this date and within the debit account or or remembered some line below or greater than the same date less than this date or the credit accounts well I want to return values with either the credit account of accounts receivable or the debit account of accounts receivable I want both of those both those options here so we have that here so that is our criteria based on formulas that's gonna get us our results so that way we can so credit in this case accounts receivable can show up here or accounts receivable can show up here either one and of course we want it based on our date range only within the date range so we can do that with these dates based on these dates and based on this display account all right so we've got the criteria so the criteria of our advanced filters based from q2 through t4 of sheet 3q we including the headers q2 through t4 that's our criteria right here q2 through t 4 and then we're going to run our results on the results are gonna be from v2 all the way through a f2 that's all we need to get our information copy to Range copy to Range v2 through a f2 and the unique is false why did I have unique false because if the if the user is entered the same transaction twice why not just include it I want to make sure that we have a lot of duplicates here for example I want to make sure that that could happen that in case just allow duplicates in this case we're going to allow duplicates just in case users into the same transaction we wanted to show up always all right so next up once we've run that advanced filter I need to know the last results row what is the last row of our results that's important because we're gonna bring that information over so in this case our last results row is 10 so then I'm gonna run a for next loop from row 3 all the way to row 10 and bring that data here into between 8 and 15 on bring all that data and in fact I want a copy I want to bring the first debit roll over and then I want to bring the second information over here and the balance is calculated so we're gonna worry about that all right so we can do that with this line of code here so for example the first book before I did I want to sort the list based on the dates before I bring the information I want to sort this I want to take this data we don't know what orders and I want to sort it from v3 all the way over to a F in the last row sorted based on w3 which is our first date first date based on that is the key w3 about a basin so the earliest date appears in the latest day so there are always in date order our results so we can do that sorting the fuse on w3 as I just mentioned and we're gonna sort those ascending we want those ascending so that the newest appears last and the earliest appears first then of course our certain range is gonna be v3 through AF in the last results role that sorts our data based on dates before we bring it back into the general journal all right we don't need that line of code now the transactional who is gonna set that at 8:00 that's gonna be our first row of transactions because we're gonna track that row we're gonna skip every two but I want to make sure that we transaction our first set our first row to be row 8 which is gonna be here alright so in that that's and then it's gonna go from 8 to 10 to 12 to 14 until we're done so we need to set it first so the results row we're going to set the results row this is going to be the beginning of our loop we're going to say from 3 to the last results row step 2 why we doing that so into our transaction list took a look at our results row we have two rows for every single transaction so we want to skip to we're gonna go for starting at 3 all the de last skipping 2 so it's gonna go from 3 to 5 to 7 9 and then 11 okay 11 just to 911 it'll be done so 10 wide in this case is 10 the transaction number 11 so we're gonna skip those two and for each of those we're gonna do something so what are we gonna do every two rows so for every two rows we're going to say sheet 1c in the transaction row and H and the transaction row equals Z and the results row through a what is that V through a well let's take a look at that the V through a a is our first row if you take a look at that the way that I set these up one day before this is all of the first line of code so we're gonna say this line of code here starting in cc1 all the way through h-here equals dis right here right here so that's the first line what about the second line the second line is actually here invoice a be through AF a be through AF we're gonna bring that I'm gonna bring that right here D and that end the transaction row plus 1 is equal to all the way here so I'm bringing that information right here it's starting with invoice and all the way to 150 so if you see that starting with invoice and all the way to 150 in fact it's gonna be all the way to the credit a be through AF so how do we do just two lines of code right so the first the first row C in the transaction of equals V in the results row here the second one is sheet 1 D plus 1 the second row down from the transaction rule all the way through H and the second row down equals a be through a F a B so a B in the second row so we've got a B here so our second row is going to be here right here our first row here second row here again first row here second row here so we're bringing this information and I'm gonna bring in it here first row here second row excuse me first row starts in C cuz includes a transaction number here second row starts in D and goes over to here so second row same thing so we just keep doing that through our loop first row here second row here so we go through that and we do that loop all the way down and each time of course we're increasing the transaction row that's starting at 8 and then it goes it adds two then it goes to 10 10 to 12 and then or team so again starting it so we and make sure we want to add row' here we want to add one row here so that's very important and we can remove this line so we're gonna add add to each time to the tray and then we so this is our loop right here some tab that back over this is our loop right here that's all we want to do so that's how we get the data in the next thing we're gonna set the low to false we're setting the load to false alright because we've done that and then we're gonna reset the calculation that is all we need to do with this let and that's gonna load all the transactions in there we've already gone over the show so say button so that's we've covered that and so we've also gone over the save transactions now we've got some formulas we've got a few more to show you but gonna save that for you next part we've gone way over time on this and of course make sure you get your version here I'll include the links down below so make sure you get it there and of course if you have not already make sure you enroll in our amazing dashboard master class that's gonna take all this data and create amazing single click reports oh well is our resource guide if you want a thousand resources including 100 PDF downloads 100 free Excel courses and of course 100 websites and blogs 100 utilities tons of listings it's an amazing application it's fully updatable I'll include the links for that down below again that helps keep these videos free each week so I really appreciate your support don't forget to subscribe to our YouTube channel and thank you so much for joining us and we'll see you next week for part 2 we're gonna do split transactions we're gonna go over some of the formulas thank you so much for joining us [Music]

Channel: Excel For Freelancers

Views: 160,191

Rating: 4.912394 out of 5

Keywords: Excel General Ledger, General Ledger In Excel, Excel Accounting Register, Excel Accounts Regsiter, Excel General Journal, General Journal In Excel, General Journal Excel, Accounts Journal Excel, Excel Accounts Journal, Accounting In Excel, Free Accounting Workbook, Free Excel Accounting, Free Genreal Ledger, Free General Journal, Excel Free General Journal

Id: Js4pQR2ASsM

Length: 87min 44sec (5264 seconds)

Published: Tue Apr 23 2019