When you are trying to keep proper accounting books reconciliation is the way to do it and today i'm going to show you how to do it we're also going to show you how to navigate past and we're going to do it all from scratch we're going to be designing this from scratch i'm going to show you how to add new ones and also we're going to show you how to automatically save existing ones and load those existing ones simply with a search we'll be able to load in previous reconciliations just like this and we're going to do it all from scratch as a sample workbook i do bring these trainings to you each and every tuesday absolutely free i love your support there's so many great.
Ways you can support this channel if you like this content one simply like and share that's a great way to do that sharing these videos liking them comment below that really helps another way is to join our patreon account we've got a brand new patreon account with tons of incredible benefits such as downloadable pdf code books automatically updated trainings and workbooks so for example there may be some features that i leave out or that i add on later based on your suggestions those updated trainings those updated workbooks are all available on patreon that includes any.
Type of feature that you may want to see i'll include that any type of fix that you might need or we might need on a workbook or any type you want me to focus on a particular part of workweek i do all that with the feature focus and fix inside our patreon so hope we'll get started on that that just starts at just three dollars a month and it's a fantastic way to support us once we get to 500 members we're going to be a free course for just those members i'm going to be sharing that entire course brand new course only for our patreon members so that's our goal.
500 members okay great let's get started on this don't forget to subscribe and click that notification icon bell that'll alert you when we got these changes all right this is the sample so basically what i'm going to be doing in this is i'm going to creating it from scratch so based on this sample we're going to create have the ability to have an account the ending balance statement date search id beginning balance and then if there's any cash back or something now keep in mind that if the account type is like a credit card it would be cash back or credits and interest paid where if the account type is a checking account it would be interest earned and services but i want to design all this from scratch so what we're going to do is we're going to close out this this is a sample we don't need to save our work and i'm going to start right here now.
Going to format that cell i'm going to give it a fill is a great one training for if you're just getting into vba or you have a little bit this one i'm going to walk you step by step so sometimes we do these things if there's a simpler application like this we can do them from scratch other times what we'll do is we will generally create them and then sometimes i'll go over the code step by step with you it really depends on the size of the application the type of the training we go through so i've got i got something for everybody in these trainings okay so what i'm doing right now is i'm just giving it a fade effect the top and the second line over here and then i'm just going to give it a general color and that's generally what i do throughout just kind of keep an eye on it and we'll go probably down i'm going to go down a lot of rows because we got a lot of data possibly so we'll go down to about q let's say 300 it's fine for now i'm just going to.
Give it our baseline color this is going to be our baseline this turquoise color if you want to get this exact color scheme let's take a look inside we're using this one we are using solstice so this one's called solstice if you have that that's the one we're using you can use anything you want okay so let's give it a heading right now we're going to call this let's call it a count just as you saw inside the sample account reconciliation hopefully i'll spell it right for a change manager okay obviously we're going to need a bigger title that reconciliation i probably spelled it wrong all right so let's take a look i'm going to give it a font of 30 and then what i want to do is also i want to give it a specific font something that's common i use this arial rounded a lot and then we'll go in the middle okay so that looks pretty good that's going to cover it.
I try to keep these consistently and i'll give it some icons on a little bit later okay what we want to do is we want to start out some fields and we're going to have a few fields here starting in two what i want to do is i want to let's just start on the fields here that's a little bit easier more clear so i'm going to merge and send it this one because i want column d is going to be for the selection as you saw and column e is going to be for the transaction data and a few other things but what i want to do is i want to have that account drop down list here so what i'm going to do in row three actually let's give it a little bit more space row three what i'll do is i'll merge and center that so i'm going to merge the center of that and then let's do the right justify and we're going to call this the select account so we want a drop down list of accounts select.
Accounts and i've got an admin with a few accounts listed so we're going to select a specific account and then we'll have a field here that'll be for the account then i want to have an ending balance ending balance as you saw on the sample and then lastly what i want to have is a statement data we want to know this date of that statement that's really important because that date of that statement is going to determine what transactions loads we may not want transactions after that statement date but we do want transactions before that statement date okay and here what i want is i want to know what reconcile number if we're loading a previously.
Saved reconcile i want to know what id that is reconcile right so let's do a reconcile id and then i want that id up here probably let's put it here under actually let's put it here i'm going to copy paste it i'll put it in j that's going to give it why is that because i'm going to use this column as the id because it's a smaller column so this is where i want the id so we put something like 1 or whatever here so that's where i want and i'll left justify so that's the currently selected or if it's a new idea we'll do that okay so we have a statement date here and then also i want to skip one row have one row for space and row five what i want to do is i want to put basically it's going to be based on an account type so let's just put in a temporary something.
Posts Related:
Called cash we're going to change that cash back okay it'll change in a moment because it's going to be dynamic okay and dynamic based on the account type remember bank accounts and credit
Card accounts are handled very differently so we want different text based on that and i'll put another one just put fees in here i'm going to put something temporary it's going to be a formula and then i'll just uh right justify those here and then we're going to have the let's right justify and then here what i want to have is i want to have the charge in this case the charge amount what is that amount charge amount and then here i'm going to have the date so we're.Just going to put the date of whatever that charge is so that means if you've got cash back or cart or something on your account you know when you get your bank account there's a certain amount of fees or maybe you earned interest i'm going to put all those transactions there and then again i want an amount here and then the date here so i like the way that that looks and uh so now what i'm going to do is i'm just going to hold down the control and let's merge and center these so what i'm going to do is i'm going to copy i'm going to paste special and then i'll paste the formats there all right and then i'm going to merge and center these just like we did above and then i'm going to right justify just like we did okay so i've got that one i like that and then i'm just going to hold down the control and then i'm going to format these i'm going to give it a border here we're going to use the same consistent color here so that border is going to be this.
Darker turquoise here then i'm going to surround it with this thin line and on the right side we're going to put that dotted line and then likewise i'm going to do something a little bit different here these are the fields that are going to be the input fields so in here i want something i want to call these white so we'll give it that white color the border on these is going to format those cells and then the border is going to be that same color but on the top right and bottom okay so we've got our fields now these are all going to be dates so i can format those as a short date that'll be sufficient and then i'm going to right justify these fields holding down the control.
Right justify those so we've got to reconcile id our form server so what we'll do is we're going to skip a row and then we're going to put in row 9 we're going to put our main header we're going to call this account transactions because we want all of our transactions to list here based on whatever account we've selected here i'm going to merge and center that all the way across here and then we're going to have our subheading here so our individual columns in this case we're going to have a selection here in column d select all just as you saw in the sample i'm going to have a transaction date here i want the description here and this one we're going to do a merging center because i want the description to be long so i'm going to merge and center the description all the way over here like that and then next up what i want is the check or reference number and.
I've got some sample data we'll go over the moments reference number and then next up i want the debit amount debit amount and we want the credit amount that will increase or decrease based on the account type credit amount if it's a credit card that credit amount will increase if it's a check-in or bank account that debit card is going to be the one to increase so let's go ahead and give it some formats i'm going to right click and format those cells we're going to give it a fill we're going to use the same fill effects that we did on the upper to keep the theme consistent so i'm going to go with this medium dark to medium light here and then i'm going to paste that down okay then i'm going to click format those cells and then this one i'll do a border and also just fill effects the fill effects are going to be the secondary color which is the lighter fill effects right here and then we're going to click ok and now i'll just give it a border all the way.
Around that'll be sufficient all the way around we can actually give it the color if you want you can give it a black or we can use the same border color so if we use the border color here the same border color that we're using all the way around in the inside as well okay so saving our work so we do so far we've got our dates so now what i want to do is i'm going to skip and i'm going to put some summaries in here here what i want to be able to do is i want to be able to search for an id search as you saw there's a search id so we can search for previously created reconciliations and also here i want the beginning balance i want to know what the beginning balance of that is usually on our statements it comes with a beginning balance and we can write that in so the beginning.
Balance there also i want to know the cash back in fact what i'm going to do is whatever is in here equals whatever's in here because this is going to be a formula i want to put here cash back and i also want to know the fees so the next one is going to be equal to the fees this text because this text cash back is going to change and we'll go over some formulas just in a minute it's going to change remember based on a bank account now what do you mean by account in the admin let's take a look very very simple all we have is simply a list of accounts and then we have the type here we can use the drop down list i'll put a drop down list here bank or credit card right because they're handled a little bit differently so we want to make sure that we differentiate.
Between the two i've also got a named range if we under formulas in name manager i've got a few named range to help us out we have account name now that account name is going to be a dynamic named range using the offset formula and it's going to be based on those names and i also have an account type again another dynamic named range but this one's going to be on the type and that's going to help us determine the selected type when we select a specific account so it is this name manager this account name here that we're going to be using in the drop down list here.
So if i want to select that account type i want to know the name of that account so we go into data data validation here and we create a list listing and what type we're going to use f3 we're going to select it from our list and this media account name so we're going to click ok so now we can select between whether it's the check-in or all the accounts associated we're going to need that that's very important so what else do we have we're going to i want to insert a select all or check box here we can do that using the insert i'm going to insert a symbol now let's take a look at this whether it's an unchecked or check box vba is going to handle that but if we take a look at there into the wingdings font this is character 168 we're going to alternate between this unselected box and we're going to alternate between the selected box which is character 254.
So between 254 which is selected 168 which is unselected we're going to be alternating with
That we'll start with that and we're going to close that okay so we want to do the same thing here but we want to make sure that the font is the same throughout so what i'm going to center that and then i'm going to go all the way down let's just say to a lot of translation we'll go all the way down to let's go with d1000 there go all the way down there that's about as far as i want to go and then i want to make sure that that font is the wingdings font because only that column that is going to take on that check mark so i'm going to hold down the shift and then select the wingdings i'm going to enter that so now that they're all wingdings and that's what i want there okay okay.So now that we've got the transaction dude i'm going to center these here making sure they're centered i'm going to bold them and then this one as well it should be bolded as their header okay so as we enter we're going to put some conditional formatting here that's going to help us saving our work so continuing on so here we have the search id we have the beginning balance we have the cashback these two are going to be user filled so i'm going to color those white because the user's going to actually fill them these two are not so for all of these and so else we want fees then what do we want i want to put that ending balance what is that ending balance because it's going to be our summary right here then once we have the ending balance i also want to put the clear balance what is that clear balance that's going to be a formula that determines what is our clear bounce based on the selected transactions which one we're selecting after that i want.
To know the difference what is the difference between the ending balance and the clear balance very important when we're reconciling statement do so what is that difference and we're going to put that difference in right here okay so i'm going to right justify these and we're going to also format those cells all the way around here and then we'll give it a just a border we'll use that same color that we've been using that dark green and all the outline and all the inside but the inside here what i'm going to do is give it that dotted line okay i like that this one i want a little bit different i'm going to make sure that this is format cells i'm going to give this one a little bit thicker border on the top and on the bottom and then probably the left and the right just so we can differentiate that's very important and i'm going to make sure that it is bold so good okay we're good to that we'll add some conditional formatting onto there so cashback this is simply.
Going to be whatever is located here the amount the fees are going to be at whatever is located here equals the amount here and then also what i want to know is the ending balance now the ending balance is going to be equal to whatever we enter here that's the ending bounce that years are going to enter beginning balance and then the clear balance that's going to be a formula we'll go over that in a moment and then the difference basically is the difference between the ending balance and the clear balance so that's just a formula so all but i do want to do is round that out so we'll go over this formula soon and then we're gonna enter the search id so that's pretty much it.
For that now all we need to do is add some button sets and some conditional formatting and we're going to be good to go okay but here let's see we got to add some more information here so what do we want to add as far as the admin area and the columns there in that area i want to know if the reconcile is being loaded or not so it's going to be a true or false boolean so so we're going to put that there i want to know if it's being loaded i also want to know what the account type is i want to know is it a bank account type we can use a formula for that so how do we gonna know that well that of course is based on whatever has been selected so we're going to start with iferror and then we're going to index now remember we have a named range based on that account type so that's exactly what i want what is that account type that's what we're indexing we're going to base it on the match and we're going to match what row is it we're going to match to see when this f here is.
We're going to match we're looking at basically f3 and we're looking up in the array of the account names let's type up here so we can see it up here account names right we're going to look for that and we want an exact match okay we want the column is going to be one because they were just the account types with single column and there we go that's the index but what if there's an error if there's an error i just want to show blank okay so that way we have bank account if it's showing and if we select we said that we have a credit card okay so now we've differentiated between which is exactly what i want and the next one in four i want to know what row we've selected right if i have the recon row we have a database here with two databases and we're going to go.
Over those now the first database is called the transactions now this is all of our transactions we've got transaction date we have a transaction account we have a description we have a check or reference number we have the debit amount credit amount it's either or but it's not both we have whether it's been cleared or not right whether it's been cleared we have the recurring id what id is associated with that remember when the reconcile if we save it it's going to be one two or three as we save this we can that way we can go back to a previous reconcile and make changes or just look at it okay so we want to save we want to know what reconcile that was and we also want to.
Know what row what is the row of the reconcile is it row 11 row 12 row 13 because when it comes back i wanted to come back in the exact same row that we saved it so that's going to be very important so we want to know what row i also want to know what row of the database that is this current row this is row 6 7 8 so we're going to use that and we're going to use the formula to do that so we've got our database and we also have our reconciled database right we'll reconcile one we have an account right we have a beginning balance if there's any an ending balance then a statement date and all that's going to get saved so when i save that ending balance and that statement date it is going to get saved directly inside this database here and so what we want to do is we want to keep track of the reconcile ids now i've created a named range for that under the formulas.
The name manager and the account reconcile id or rec id and again i've used an offset formula here if we zoom in on this reconcile starting in a2 we're starting in that header row but we're moving one row down not skipping any columns we're going to count based on all of the rows starting in the header row but we're also subtracting 1 because we don't want to include the header row we do want to include the header row because it prevents errors and when there's no data so that's important okay so we understand that we have that recurring id so if we know that and we want to know what row if i.
Want to know what row one is or i want to know what row on two is how do we going to get that well it is that formula that we're going to place right in here so in four i want the reconcile row and what is that row so i'm going to look right here inside k2 i'm going to determine that using a formula so this is going to be equals if air if there's an error i'm going to match and what are we matching we're matching we're looking up this value and what is the array that we're looking at we're going to look up that ray that we're just talking about which is the ring style id and i want exact match okay but i want the row i don't want to just found it so i don't want just to find it so i want to add 2 onto that because that's going to get us our row because our first one starts in 3. if there's an error i'm going to show blank okay so that way.
Number one is on row three number two is on row four so that's working just fine okay so what we're gonna do is i'm gonna left justify these and i'm gonna right justify these here and then what we do is we're gonna give a color probably all the way up to let's say we'll give these ones up until five a certain color because there's some things that i'm going to put in i don't want that black font and then we'll give it this color let's just say this orange color here and then what i want to do is i want to have some information here so let's go ahead and all the way down here add some.
Rows so what do i want in here i want to know the next id what is the next reconciliation next recon id we can use an equals if error there's an error i want to use the max formula i'm going to max the reconcile id but i don't want the maximum i want the max plus one that's going to tell us what our next idea is if there's an error why would there be an error well there'd be an error if there was no date at all so if there's an error i'll just want to set it to one that's going to be our first but there's no error and our next one is going to be four okay so what do i want in here i want to make sure that when we load up i want to make sure that they have the account they have.
A statement date i want to make sure that we have everything we need in here when we load it just in case we do so at least i want to make sure that we have a slight account and a statement date here i want to know how many fields are filled out here so in case we want to change the required fields it's very easy that's one way to do it required fields and we can use account a equals count a and so basically i just want to count holding down the control here and here it's going to return one right as we fill those in and so we fill those in 456 these are amounts so i'm going to hold down the control here and here and these are all amounts i'm going to sign those amounts same thing right here these are all amounts here and i'm going to do the same thing here.
Okay so now we have amounts okay so i want to know how many fields and this is the date so if we put in 8 15 we see the date all right and i like to left justify these here saving our work so now what i want to do is i want to make this dynamic this text dynamic based on the account type well let's continue with these i want to add a few more i want to know the required fields for the service charge right if there's a service charge being added here here here i want to make sure that the user has filled in this and this and this and i don't want them to leave any blank so what i want to do is i want to count the number of fields that have text and i want to make sure that it's three before we actually save it so we're just going to call this service charge.
Fields and again we're going to do the same thing equals count a or counting all the text and basically holding down the control here and just selecting these three fields okay we're just going to make sure that now it's zero which is fine but as soon as we add some text it's going to go to one okay i'm going to do the same thing here but this one we're going to call this let's call this interest fields and i want to know interest because it's going to be either interest paid or interest costed so equals count day i'm going to count those fields just a quick way of making sure that the user has filled in the required fields as in the vba we could say if f7 is blank if h7 is blank if j7 is blank right then do this but it's much easier to say is this.
3 right as soon as this is 3 right we put an amount here and put in a date here as soon as this is 3 we know that it is automatically have the required field so that's an e quick way to tell saving our work so that's it that's all fields so i'm going to put one more in here i want to know whether we're going to hide the transactions based on a certain date so that's going to be a check box so i'm going to insert in this case i want to insert a form control and i'm going to put a form control right here and i'm going to put some text right here and i want to know as you may have seen in the sample i want to know hide transactions after the statement end date hide this is going to.
Be an option transactions after the statement and date i'll make this bigger so that's what we want to do so i want to know whether we should hide the transactions based on after this date right so maybe we don't want to see any transactions after the statement date we have an option to do that and i'm put this format that control and i'm going to put the cell link here based on b9 and click ok all right so we have that there let's give that a little bit of a color the same thing and we're going to call this hide transactions okay and then put a question right there okay so as soon as this.
Gets checked it goes to true that's what i want very good all right so we've got the basic part of it we're going to put in some buttons we're going to put some conditional formatting now what i want to do is i want to put in some conditional formatting based on some information here just a few different rules and i want to add some dates let's format those dates again let's go down to e 1000 and then format all those dates accordingly i'm going to format all of these just holding down the shift and give it that short date so they're all the same i like that that's going to be just fine so now we have that and i guess we could center that let's just go all the way down here and center that all the way down okay generally i'd never go down this far but.
You don't go down because it does take up space but there's a small application generally you only want to go down to the limit great so we've got a description and we've got to check them out we've got everything we need let's uh we're adding some conditional formatting so what i'm going to do is just going to highlight some cells here go down to condition formatting we're going to do a new rule okay i want to base the formula and i want basically a few different formulas first one i want to do is i'm going to basically color the odd and even rows based on a condition so it's going to be based on two conditions actually so it's going to be equals and and what are the two conditions the first condition is i want to make sure that e and any row starting at 11 any row starting at 11 so we want to get rid of the absolute awesome does.
Not equal empty that's going to be the first and what is the second condition i want it going to be be based on odd row so odd rows is going to be one the mod of one we can get rid of that equals okay so that's two conditions so for odd rows what do i want to cover them i want to give it basically this background color but a little bit lighter so i'm going to format those i'm going to fill it here's that background color but i'm going to go into more colors i'm just going to make it a little bit lighter clicking ok clicking okay okay that's one rule now what i'm going to do is i'm going to copy that and i'm going to click ok and then what i'm going to do is conditional go back into conditional formatting manage the rules actually and what we want to do is we want to base it back on a lot of rows not just up to 16 so we'll go to 9999 okay click apply so that's going.
To apply it to everyone now we're going to get a new rule i'm going to form this and i'm going to paste that same rule in there except i'm going to change it to even rows now in this type of format and then i'm going to go into again selecting that color and then more colors i'm going to give it a very light color nearly to white okay and then click ok and then okay again and we'll just double check the range and we're going to make sure that this is also the same range and then give it just a large range it's fine sufficient clicking apply okay and that's what exactly what i want i'm going to do one more so i'm going to do a new rule again using a formula and what we're going to do is we're going to base it in this case on just a single condition so equals in this case e 11 getting rid of that absolute 411 so it's every row does not equal empty basically what i want to.
Do is i want to give it some dotted lines in the middle so i'm going to give it a border and i'm going to choose this this turquoise color here and i'll go a little bit darker here maybe this one and then i'm going to use a dotted line here and here and clicking ok but i don't want to apply this to the outside borders i only want to apply this to the inside so what is that range going to be it's going to be starting on e11 and i want it to go only to i not all the way to the end so i and then the last row okay so it's gonna based on that so then nine nine nine nine nine nine is enough and then apply okay so that's that's exactly what i want so as we add information here.
You see we got the nice conditional formatting we've got that dotted row so that's exactly what i want to give that so the conditional formatting is done we're good with that let's give it a little bit of a button set and then we can start adding in some vba so i'm going to insert here and basically some shapes just some basic shape button sets and i'm going to move over to the right here and we're going to add them in right here and basically i just need three buttons save new and delete that's going to be sufficient so here we're going to give it the style the basically the style of our form is here i'm going to call this save yeah we can go capitals i think we'll go capitals on this as opposed to the sample and then what i'm going to do is i'm going to format this one just the way i like it so we're going into the size and properties and then go into the text box here.
And then we'll do .03 on the left but the right is the most important because i'm going to right justify that i don't need anything for the top and i don't need anything for the bottom that's sufficient enough and so then what i want to do is i'm going to go in here and then right justify it in the middle and that's just the way i like it there okay so we're good on that all right once we have our save button the way you're like what i'm do is i'm going to duplicate that twice and i want to create ones for new and for delete so i'm going to put that down here so we're going to call this new and we're going to call this delete and that's going to be for be able to delete those in case we don't want to not necessarily the transactions but the reconcile so what i'm going to do is i'm just going to line those up here and then i'm going to space them vertically so we're going to distribute.
Them vertically and now what i want to do is i want to create some navigation buttons so insert shapes and i'm going to insert this triangle here and i'm just going to put it right about here okay i'm going to make it a little bit bigger because we want to be able to see it and we'll do the height on point 4. so i like that but i do want to give it the same look and feel as we have so again give it that same button here and now what i want to do is now the way i've got it the way i like it i'm gonna duplicate that and i'm gonna reverse it so i want this one here to rotate it and this one horizontally so this is gonna be our previous okay so we've got that there i like that there we'll bring them up a little bit bigger point for cool pic so i'm good with that now all just want to do.
Is i want to make sure that we're right justifying those here and i'm going to left justify this one here to make sure that everything's lined up like that and then we'll just bring these as well all the way on the left everything's going to be lined up on the left not this one this one everything's going to be lined up on the left that looks good everything's good lined up and now what i want to do is we'll just make these a little bit bigger so we have room for the icons and we're going to add icons generally you want to add your buttons first then your icons to make sure that the icons end up going on top right so the things you add after go on top so i'm going to hold down the control line this up and group that that's going to be our navigation we have a save new and delete perfect.
So let's go ahead and add some icons insert i'm going to insert some pictures here and i've got some icons saved up so basically i'm going to hold down the control and insert those i'm going to change the height on that to 0.25 that's going to give us a little bit smaller that's what we need if we take a look at them i've got one that's going to be used for the icon this is our icon that we're going to put in right here so we want that there i'm going to merge and center this here and then we have space for the icon so i'm going to bring our icon here and bring it down here like this and i'm going to give it a color that's closer to the theme which is about like let's say this one here okay yeah that's closer to the theme so we've got our our icon and i'm going to color this font same the same here like that okay so now let's go ahead and put in our save we got this for.
Our new we got our save for our checkbox and we have our delete okay i'm going to hold use my selection i'm going to line up those icons to make sure they're all lined up horizontally like this and now we're ready to group and save so i'm going to undo that so now what i want to do is i want to group these so i'm going to hold down them lining them up grouping them up and again lining them up grouping them here and we're good to go and last one is for delete so everything's looking good and now all i want to do is use my selection tool and make sure that we're not sizing these and measures so i'm going to right click format the properties go into the properties here and click.
Move but don't size we don't want that changing after we're okay i'm going to save our work use our unselection tool okay good so we've got things looking really good we've got our navigation we've got our conditional formatting we've got everything just the way i like it and now we're ready to add some vba into this but before i do that i want to make sure that our formulas for our names are important right this particular name right this is going to be either interest earned or cashback or credit it's going to be based on whatever our transaction type is our account type i should say is so it's going to be based on that so equals if this here our account type equals.