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.