Collaborate on Free Rent Receipt Template Excel for HighTech with Ease Using airSlate SignNow

Free rent receipt template excel for HighTech

hello this is rainy with Excel for freelancers and  welcome to the expense tracker in this video we're   gonna be teaching you how to create an amazing  expense tracker that is fully dynamic it's gonna   allow you to automatically take pictures of  receipts with your mobile phone and get them   automatically uploaded in this expense tracker  it is going to be an epic training we're gonna   start from scratch a blank worksheet and create  every step every line of code and every format   and formula with you I can't wait so let's get  started alright thanks so much for joining me   today I really appreciate it if you have not done  so yet please subscribe I create these videos each   and every week for you and unique videos where  I show you how to take excel to the next level   creating real applications for the real world  solving real issues so I hope you'll join us make   sure you to click the notification icons bell at  the bottom that's gonna help us out and make sure   you get your trainings each and every Tuesday  we launched them so I really appreciate you   joining us in this episode we're gonna create an  incredible and dynamic expense tracker it's gonna   track all of your expenses and all you need to do  is take a picture with your phone of the receipt   that receipts gonna get automatically loaded into  Excel all you need to do is add a few details and   it's gonna save a full picture of their sheets  gonna be displayed you're gonna be able to track   all of your expenses by month by year it's gonna  be an epic training and a beautiful application   if you have not done so yet please check out our  mentorship program if you love Excel and want to   take it to the next level is fact especially in  this day and age when working from home is so   important you can do just that and I'll teach you  exactly how you can earn amazing income with your   Excel skills by creating applications that can  be sold night and day all year around and I'm   doing that in my mentorship program so you hope  will join us for that in this last week of the   mentorship program we created an amazing invoice  application or we created a dynamic pop-up menu   it's one you won't want to miss a menu like you've  never seen before where users can create their own   custom invoices I'm going to show you how to  take that talent and bring it into your own   application so you can create your own invoices  and sell them on the marketplace so you can get   yourself reoccurring them I hope you join us you  can find us at my excel mentor.com and learn all   the details about this amazing program tons  of students are really happy with the training   they're getting and increasing their skills so  that they can become independent and financially   free alright let's get started on this training  I've got a lot to cover so we're gonna try to   get it all in in the amount of time so bear with  me as I design this we're gonna take those first   few columns columns a and B and we are going to  make them for admin so we're gonna call them great   because I want to make sure that will eventually  hide those columns we're gonna put some things in   there that we want anybody else to see the first  thing what I want to do is I want to make sure   that we have a title on this we're gonna call  it its expense tracker and that's just gonna be   the general name and let's go ahead and give it a  nice larger font perhaps something like our Arial   black and we'll increase the font something nice  and then I'm gonna extend it over to probably all   the way through J so I'm gonna merge and center  those cells and give it a format so I'm gonna   right click format and just give it some kind of a  fill pattern maybe we're gonna go with this color   right here and I'll do a fill effects so we do  a fade down I've got a different format here on   this one so give it a little bit of a light green  color and the row below I'll do the same but just   a little bit lighter that way we'll have a nice  fade effect throughout there think and we'll go in   fact we'll go all the way over to let's say calm  and I want to extend this way a little bit we're   gonna use a lot of that so we'll format those  cells and then give this fill effects here just   a little bit lighter using that medium color and  then we'll do that okay great so I just want to   fill that and that we can also unmerge these that  I'm just gonna drag these cells over to extend all   the way to let's just say yeah I've got a really  amazing in this we're gonna show you how to get   all the contents of a folder inside Excel you know  listen we're gonna be that without VBA at least a   portion of it without VBA and then we'll use VBA  to refresh it so it's gonna be really a unique   training something you have seen before and so I'm  excited to show that part of you because that's   brand-new we have never done that before I can  bring in all the contents of the file folder okay   so basically what I want to do is I want to bring  in the logo now just kind of an expense tracker   logo but I've got one saved here we're gonna cut  we're going to do this really basic here I'm gonna   insert a picture here and I've got a wallet here  picture I'm just gonna go with that's something   basic there I want to graph at the top of this  will increase this one down there we go what I   want to graph at the top of this something really  nice and basic so let's create a background for   that box so I'm gonna insert a shape here and I'm  gonna use a rounded rectangle here and I'm gonna   start let's say around D and go all the way to J  probably down to about say 8 something like this   I'm just want a very slight rounded corner here  like this and then I don't want any borders on   it so I'm going to say no outline and we're gonna  give it a shape fill of white so that's good I'll   put a little bit of a shadow behind it so it  looks so it kind of Bray's out so I'm gonna put   our graph behind that and I'm gonna duplicate that  because I want a title and then I'm gonna shrink   it up so I'm gonna put this title section up here  and I'm gonna bring it up here and then I want to   call this title section something like expense  overview or not it's in white font now but I'm   gonna change it and I'm gonna change it to black  font so that obviously it's a actually I'll go   ahead and change the background to degree and then  it's gonna be there we go so that's good that's   what I want to see but let's give it a title and  something like a little bit of a capital probably   a little bold so it stands out more so we can see  it's gonna there we go and bold okay that's nice   expenses overview make sure I spelled that right  overview good alright so this is where I want our   expenses to show up but I want to put a line graph  over here so that it's kind of looks nice and   clear so we can see an overview of our expenses  and we'll get on to that graph sort shortly but   now that I have that what I also want to some  field I want to I want to know by year so I want   to know I want to be able to select different year  so we're gonna have different year so let's just   say like 2020 or something like that and then  this field we're gonna have different year so   let's color that why cuz I want the user to be  able to select different years from a drop-down   list in fact we can just go ahead and build that  right now the other data validation I'm gonna   create a list here and I'm just gonna put in let's  just say - 0 1 9 comma - 0 - 0 & 2 0 - 1 of course   you can customize this I mean years you want but  I'm just gonna put 3 years so now we could user   can select between the drop-down list okay and  then I also want some dates so I want to be able   to use her in to receive so we're gonna put in a  date here and then I want to type and then I want   the receipt here also so the receipts gonna go in  hatred so I want the total receipt here and then   also I want to know the amount of course we have  to know the amount of the expense and I want the   category this way you can assign categories such  as different types of expenses and stuff we'll   create a small list for that all right so let's  color these I'm gonna put these in in white here   so we can see them clearly so we know the user  is going to be filling those in so we're gonna go   with a white font and then I want to put a border  around them so I'm gonna right click format those   cells and I'm gonna put something a little bit  closer to the theme maybe a light here actually   fill white and the border we're gonna go to the  border something like a little bit light border   just something really I'm gonna go with a very  light theme here and kind of just not a dark color   here so I don't put the dotted line on the left  and then I'm gonna basically do the same thing   here under the date here and then I'm gonna write  justify these I should have left justified those   also gonna format those cells to give it a nice  look and feel this is where the user is going to   be entering their data so that they can do it and  will do a table down below - so that you can see   that and then I'm gonna put the left border top  and the bottom here okay so now we're getting to   see our form and how these are gonna be the form  slits I want this a little bit darker like because   I want these five fields are gonna be our form  so I'm gonna format those cells sorry it's off   the screen right click format I'm gonna go with a  little bit darker color on here I'm gonna put in   an outline the idea is that I want these form to  be into the user I don't have some buttons up here   let's create those buttons we're gonna do a very  basic button let's zoom in here make this look   nice I want these rounded edges the same so we can  do that notice when we shrunk it up we we want the   rounded edges so I want those the same let's bring  this out a little bit there we want them matching   the top looks nice and all the way to the left  okay good so we get a nice view now what I want   to do is I want to create some buttons so again  I'm gonna insert shapes here and I'm gonna use the   rounded rectangle and then this time I'm just  gonna use it really we're not gonna use icons   today we're just gonna use something really really  basic something really nice and that it looks good   and we'll go with an orange because I want to  really I want to stand out in this case and then   no outline and then what I want there I want to  push put ad expense so many's a + ad expense this   one we're gonna keep it really really simple  give it that kind of a flat design it's gonna   kind of look cool you know something a little bit  different and then we'll write just fight and then   I'll make it a little bit bigger and then bold in  fact let me make those that's a little bit too big   but I'm gonna go I'm gonna shrink it up and I'm  gonna make them capitals cuz I want to so we'll   make it a little bit smaller I want it that big  alright that looks pretty good and bring it down   okay I also want to do the same thing I want to  check for receipts I want to know if we're gonna   be having receipts so there's gonna be receipts  stored in the folder and we need to know are there   any receipts check for receipts in fact let's not  do this let's make a dynamic but even better I'm   gonna make this green what if we have a button  that has different text based on whether there's   receipts or not so let's do that we're gonna  have this is gonna be dynamic text but we'll   just keep this temporarily but I'm gonna show you  how to create a dynamic text because if there are   receipts in the folder that need to be brought  I'm gonna say I want this to say there are six   receipts or review six receipts or something like  that so we'll put that in so it's just temporary   but it's looking good so far we're gonna put a  graph up here let's start to build out our table   now I want an expenses table here so I'm gonna put  this house are expensive so I'm gonna merge and   center this and then create expenses and I want  all the expenses here so we'll have this a date   amount type category and then receipt location  and then I'll want to probably a picture I want   a picture of the receipt right here so we'll have  this all stored in the table down below let's zoom   out a little bit so we can see that okay so we've  got that so let's Center that and I'll give it a   bit of a format so I'm gonna format those cells  I'm just right clicking it's a little bit off   the screen but all I'm doing is right-clicking  and going to format cells you know how that is   so I'm gonna give it a fill a fill effects of  a lighter color basically the same thing what   we've done using the same theme that we have going  from the medium to the very light and on the top   one I'm gonna go just a little bit darker so on  this top one I'm gonna do it a little bit darker   format the cells also keep in mind that there's  a free download just with the links down in the   description every once in a while there are some  issues you know maybe I missed something in these   live videos or something you never know and so but  of course I always make sure there's a corrected   version available to download and it's a free  download just check the links in the description   whether you're on Facebook or YouTube format the  cells again we're gonna go over the border and so   there'll always be a downloaded version for you  I make sure to get that to you each and every   week okay give it a just a basic border there we  go so this gives you a nice and now we can color   this just something basic so I'll go with a white  background and I want to go with a border form the   scroll absolutely let's go up here and then we're  gonna format those cells just something basic so   that we can get by and then what I'll do is I'm  gonna put a thick border on here and then I want   the top and the bottom here like that something  like that there we go that's nice so we can   really stand out we can really see it I'm gonna  bring this down here alright so we've got our   basic covered now we've got everything pretty much  covered now we can start to build out the formulas   and the formats and the named ranges we also  have to add a lot of named ranges because I want   to make sure that all the expenses get tracked and  to make sure that everything is going to be synced   completely and we have gonna be a really great  formula that we're gonna show you how to bring   those names or receipts into the formula okay so  just add no fill on that okay so now everything's   consistent so we're starting to build out  let's see what we want to put inside this   hidden admin area first I want to know if it's a  fall to receive we're going to be loading so you   can basically add expenses whether they're with or  without a receipts but I need to know that because   I need to know whether we're gonna have a link so  I'm just gonna put folder receipt and it's gonna   be just a true or false true or false something  like that we need to know we also need to know if   it's a new expense so let's just put new receipt  and that's gonna be boolean true or false also   also I want to know how many receipts to review  how many do we need to read this is gonna help us   build out our body text do we have four receipts  in the folder that we need to resume do we have   20 and we have none I need to know that I'm we're  gonna use a formula on that okay I need to know   the button text that's in button text this is  gonna be our dynamic text let's call this button   text so what I want to know is I'll go through  that formula shortly but then also I need to know   the selected row when we select a row from this  table I need to know so for example let's say it's   21 now you put you need to know that we're gonna  highlight it based on that I also want some months   we're gonna be tracking how much per month so we  need to know the month so let's just put months   here and with months here and the amount per month  I'm just gonna use short names on these we don't   have a lot of space so all you need to do is use a  short name is just scroll down January all the way   to December here and then I want a total here well  I told all of the total amount here so it's gonna   be basically equals the sum of whatever all these  months are and then we'll add in those formulas   once we get some amounts okay great so we want  the sum of all of that so it's starting to come   together now and let's color these so we know we  can decipher exactly what what they are and then   give us some borders and the same thing for here  just so we can color it and borders then I'm gonna   left justify this so we can see it all right  it's coming together now we're gonna start out   with some formulas and D but we really need some  data to start with I've got some data actually I'm   gonna put it right here I've got some data right  here I'm gonna copy that data just some basic data   I'm gonna put it right in here and I'm gonna paste  it in here and then of course we do needs formats   don't we so we need these date formats so I'm  gonna format this as a short date and I'm gonna   format this as an amount okay good it's starting  to look nice together we're getting an idea and   let's make this bolt so it stands out a little  bit more control B will get us there let's add   some types and categories we need some lists and  we also need some named ranges too let's create a   dynamic named range for our expense date table so  let's do that let's just highlight some cells go   into the name range go to the formulas here name  manager here and go new and I'll want to create   a using offset so we'll just call this expense  date and then tab down and we'll start it out   with offset and we're going to include the header  but we don't want to include it in case there's   no data we want to make sure there's no errors  including the headers going to help us but we   also have to go one row down and then comma comma  count hey I want to count all the text I also want   to include the headers - I'm one included but I'm  Adam make sure we exclude it we're including the   headers because when there's no data there's  no air but we also have to let's just use a   highroad 999 and then what I want to do is I want  to subtract one from here so we're gonna count a   and then minus 1 and then comma 1 that's just the  column tab out tab back in make sure the dancing   ants cover all of our data that lets us know that  our formula is correct it is correct so let's do   that now let's do another one for a mount so new  and then we'll just call this a mount let's call   it expense amount and then we're gonna tab over  and what data we want will we want again equals   offset and I want to start out in the header but  then I want to go one row down comma comma count   hey what are we counting we're counting all of  the data including the header but subtracting   1 again 9 9 9 and then close parentheses minus 1  because we're gonna take one out from the header   comma 1 and then any print sees tab out tab in  make sure that encompasses all I ever dated great   ok now that we have our data now that we have our  formulas we can start to build out our graph here   so let's do that let's start with some months I  want to know how much in January we want a formula   actually that's gonna total all these mounts but  only in the month of January so how do we do that   well let's keep it start we know what do we know  I know this month one right now I know it's on Row   one so how do we get that so what is the formula  we're gonna be as renew sunnites equals some ifs   yeah that's right we can use some this could be  multiple criteria but what is this some range the   some range is of course our expense amount expense  amount that's what we just created an image and   what is the first criteria it's gonna be based on  the date so it expense date is what we're gonna be   focused on and what is that it's going to be the  first criteria of that it's gonna be greater than   or equal to the first of the month or January in  this case for this row and what is that the date   of what January if we want the year we've got the  year here f4 to make sure it's absolute comma now   what is the month well the monthly r is january  we can't use the word january we can use 1 which   is the first month of the year so how do we get 1  we know it's row 11 so how do we go from 11 to 1   well we just subtract 10 so row which is 11 minus  10 is going to get us one now we have the month   what about the day that's gonna be 1 alright  great so that is our first criteria what about   our second our second criteria is going to be we  want that to be less than or equal to the end of   the month so how do we write that well first  of all the criteria in this case of course is   the same which is expensed date we have that and  what about the criteria for that quotation less   than or equal to and the end of the month reviews  eeo month for that again the start date in this   case would be date what is the year same year and  we're gonna use that make sure we're going to f4   to automatically absolute comma what is the month  say month as before as we did in this case it gets   gonna be row minus 10 that's gonna get us 1 and  it's gonna allow us to drag it down comma what is   the day 1 okay so we get the end of that month and  in this case we wanted to meet number of months   would be 0 so I'm gonna close that out now we have  the end of month and now we're gonna close out our   summits so we hit enter we see it's 25 which is  exactly what we want if we were to change this to   115 we see that it changes to 37 which is exactly  what we want okay great so we have our formula   work and if we made sure we have absolute on what  we need to which is e 10 to keep that consistent   now all we need to do is drag that formula down  to all of our months and it's gonna automatically   appear excellent it looks looking really good  we don't have any data for May which is correct   no data in fact we can just change this to five  five so we can get data in May and we have a total   here excellent all right let's now since we have  our data we can build out our graph our little   graph here or a little line chart it's gonna be  really nice let's go ahead and insert that let's   highlight all the data here not including the  totals yet and we're gonna insert and I'm gonna   just gonna go for a basic column something  really simple like this but I want to change   the look and feel I don't really want it to look  exactly like this we're gonna update it because   I want to keep it very simple so to do that let's  get rid of this series we don't need that'll just   click the lead on that I also want to change the  color of this so I'm gonna right click and go to   format the data series and I'll just gonna go to  the fill I want a solid fill maybe just something   consistent like this green here would be perfect  I want a green consistent okay now I don't also   don't want this border around here so we can right  click and then go to again format the chart area   go to border and this click no line there we go  now we got something very nice now I also want the   total I also want the total what is the total of  this so let's create some something to link this   total I want to know the total so insert I'm gonna  insert in this case a text box and I'm gonna link   that text box to the total so I'm gonna put it  right here and I'm a safe equals what do I want   it equals the total and this is going to be this  one b23 so now we have it now all we need to do   is format B 23 as it occurence e because I want to  format as currency nice and we can get rid of the   borders here shave fill will do no fill on this  and will do no outline on this one but I need to   know what that is also let's increase the font a  little bit make it bold so we can clearly see it   stretch it out a little bit and what is this let's  add another text box here so we know what it is   just call this annual total annual total okay that  looks nice and then we can Center this just so   we get an idea of how it's coming I'm gonna also  Center this one so they're both centered and then   make this a little bit bold here okay now they're  both bold and we just get rid of the border on   this one I don't need a border on this very good  now we got our format all filled out and we got   our design really pretty set up we can add some  more firm moves in here looking really good now   let's see how do we get this I also want another  button here I also want to duplicate this and just   put save expense so something like save expense  and we can actually add a check mark into here   just like we do if we did it let's add a space we  can do insert you know sometimes we have logos but   this time let's just do a symbol something keep  it simple let's add this symbol right here so we   don't have to add logos okay good we stretch the  button out a little bit save expense well it looks   kind of nice all right and let's add title to this  as we do save expense button and then we'll just   call this one add expense button add expenses  okay so we've clearly label our and then we'll   have this called check receipt but okay good so  we've got all our buttons labels so we know when   we're gonna say them and now we'll just we're  gonna only gonna show the save and the Attucks   not at the same time so we can put them over  on the top of each other I also want to remove   this and format the gridlines so we don't want  any good we don't need any gridlines just have   a nice clean look excellent now it's looking nice  and I'm gonna group these all of these together to   graph everything together so we can move them all  together it's gonna be very nice that way we can   shrink them down and we'll shrink them a little  bit and what I also want to do is I want to list   out all the files now I've got a folder here of  all the receipts let's take a look at this I've   got a bunch of receipts here and I've also got  an archive folder so what I want to do is I want   to link this folder so to do that I'm gonna show  you how to link the folder and all the recipients   on how to load that so I'm gonna copy this folder  and then we're gonna go down in to put that file   folder in a specific file and I'm gonna put that  probably in over here and let's just say l2 here   and what I'm gonna do is I'm gonna also I want  every single file in that folder so to get that   I'm gonna use a special formula I'm gonna use the  backslash and then I'm going to use the asterisks   which is gonna do note every single file how  do I list the files I'm gonna list of all the   files in that folder I don't put them right here  nin so how do I get that well the first thing we   do is we have our file path right here in l2 so  how do we get all the files well we can use the   named range for that plus a formula I'll show you  how to do that so into the formulas name manager   we're gonna create a brand new one we're gonna  call it list files and then what I'm going to   do is I'm going to give it a refers to what is it  it's going to be equals files what are the files   it's going to be the files of l2 all the files  of l2 then we're gonna use index to pull those   and extract those out so click OK and click close  so now that we've created the name now all we need   to do is create an index equals index what are we  indexing we're conditioning the lists files that   system only created and what is the raw number  we'll the row number is the index number right   move the files go from 1 2 3 4 so we just simply  need to number those the same as the row number   to do that we just type in row and then open a  closed parenthesis and then close the parenthesis   a that's gonna get us our first look receipt 16  that is the same exact file here receive 16 so   when we drag it drown it's gonna show 17 12 and  13 right below it so all I need to do is copy the   formula and bring it all the way down here and  paste the formula excellent except I don't like   this ref so how do we do that we'll just wrap that  in if air so if air and then we'll just go to the   end comma and then just leave it blank or we can  put it to 0 I guess and that's it so then we can   close the parentheses so now when we drag it down  or copy it paste the formula down we won't show   that hair paste front look great so now we have a  list of our files perfect so now we can take that   combined with this and create our receipt image  and put it in here so now how do we do that how   do we show the picture well I'm going to show  you how to do that with just a little VBA code   now that we have the file now that we have all  the files we can go through these files and bring   these into our application and then show their  seats and then enter the information so let's   soup how are we going to do that well the first  thing I want to do is bring those pictures in with   just a little bit of VBA code let's go into the  VBA code and show you exactly how we're going to   do that so inside the VBA code here we did we're  gonna create a module so insert a module and I'll   just call this expense macro so in the properties  we're gonna name this call this cost expense the   macros not put all the macros right here make it  easier so we can see them all I want to show the   pictures and enlarge and I also want to show them  down here under the table here in fact we don't in   fact here in the patient location I'm gonna create  a nice picture for us we don't we can change this   actually to the grey font and show that and then  format we don't need that let's put the picture   in here so we'll remove the borders here and then  we'll show the nine we'll show a nice making out   of the left border here and then a right border  and then the bottom so we can show the picture   when we select on it it's gonna be great but if we  need to get rid of this bleed so that's the end so   we can just use the space and that's gonna do it  drag that space down here so it clears it now we   have room so when I select I want to show a nice  picture here when I'm working on something I want   to show a larger picture here so what we need  to do is we need to get this information when   we check for the receipt I need to bring this  file before gonna start at the end we'll start   at the bottom and work our way up bring this  file into the receipt and display the picture   really need to know how many receipts that we're  gonna bring in I want to know what this button to   say six receives or something like that how many  receipts I wanted to display a dynamic contents of   this button to let me know how many receipts that  I need to review so how do we do that when we have   a dynamic content that we want words on a button  we can use this link it to a cell so how do we   do we can use count a count the text so equals  count a what are we counting we'll do counting   all the list files what's that gonna get us for  that's exactly what I want perfect so for so now   we know this is for but if there's no list files  I need to also count if it's zero number these   are zeros so it could be zero so how do we know  if it's not zero we should count those two just   in case to make sure it's not zero well to keep  out all the non zeros but I really want to do is   once this gets down to zero it's still gonna count  zero so we want to make sure that we're not kind   so how can we do that I want to count make sure  that we're if we're counting all the non-zero so   equals if count if in this case just to make sure  let's include the range what is the range here I   want to make sure that we include the full range  starting at n 1 all the way down or however long   we want to go and then in this case we are going  to use common in this case would be does not equal   0 so does not equal 0 then only then do we want  to count this false otherwise just put 0 ok add   the parentheses here comma okay good well I want  to check how many receipts that we need to review   so let's put in a 4 million here ok so what is  the formula that we want to put in here I want   to put in a dynamic text so basically if there's  no receipts I want to put check for receipts   if there are receipts I want to list how many  receipts there need to be checked so we can use   a formula based on the value what's in b6 so what  is that formula let's type that in equals if and   then b6 equals 0 if it's 0 then we're gonna put in  check for receipts what if it's not zero if it's   not zero then I want to list how many receipts so  then in that case b6 and quotation space receipts   to review so that way we've got a dynamic text  now all we need to do is link this button with   whatever's in b7 we can do that with a formula  equals and then just click b7 and that's going   to link it automatically so now it shows that we  have four receipts to view so all we need to do is   just make that font right again and bold and there  now we have four receipts review so it's perfect   so we know now that as soon as this changed when  all of these get reviewed it's gonna go to 0 and   this text is gonna change perfect so now we're  ready to go in and add some DBA so we can get   this working just right alright so let's get on  that inside your VBA we've created of course macro   I've just have one you'll hear no problem and  we're ready to start out let's add some code to   save him variables that we're gonna use to route  all the macros expense row as long receipt row as   long and then of course we're when you picture  in picture path as string and receipt folder as   string so we're gonna use those throughout all the  macro so it's better to do bench them what's the   first one I want to check and review receipt so  sub check and review receipts okay so what this   is gonna do is gonna check so the first thing  we want to do is to make sure with sheet one   that's all focused on so for this training and  I want to enable calculations but that's gonna   do is automatically calculate that formula that  index for it's only gonna get calculated when we   do something in VBA otherwise it will so lets  dot enable enable calculation equals false and   then make it true again that's gonna force the  calculation of the sheet dot enable calculation   and then equals true and then calculate that's  gonna force it dot calculate okay let me show   you why that is let's move one of these into the  archive okay so now we only have three but if you   take a look here in our sheet it doesn't update  I need this to update let's take a look at this   let's bring these let's bring it down here so you  can see everything on the screen notice it didn't   update even when I calculate the sheet formulas  calculate or calculate now it doesn't update so   how do we get VBA will help us update it so watch  as soon as I bring this down and run this macro   we're gonna see that it calculates so I'll bring  this down for you shrink it up a little bit and as   soon as we run this it's gonna calculate you see  how those receipts automatically change to just   three now so this codes gonna help us calculate it  keeps track of the trim it keeps track of all of   their seats that we need alright so we understand  that so that's why we have to add that part of the   code so let's continue on with the receipt code  and we know why we're adding it now next up I want   to check is b6 I want to know if there's receipts  that need to be added is b6 zero or not that's   gonna tell us whether we need to add receipts  if be six that value is greater than zero then   we know we need to add receipts so right so let's  just put receipts to add next up what do we want   to do I'm gonna run a macro of course to add a new  receipt so let's just put it add new receipt and   I want to make sure that we create a macro at the  same name so I'm gonna copy that and go below this   and just put in sub add new receipts so that's  gonna create a sudden we don't have any code   there but I want to make sure that we run that I  also want to know the receipt row what row or we   on the receipt I'm gonna track that we defined it  up there or you see PT row equals what's it gonna   call it's gonna equal whatever is in b6 dot range  b6y b6 cuz b6 is going to tell us as we selected   b6 is always going to tell us the last row the  last row always because we know it's number three   so as we reduce it remember this was four but we  just moved one so b3 is always going to tell us   what row as we added we're gonna add from the  bottom on up as we each Adam we're gonna move   it to the archive and as we move it to the archive  it's gonna be one less so that's gonna really help   us so we're gonna start out there and then we'll  use this as the last row so we're gonna add them   from the bottom up so we need that to add it so we  need that row that's important because I need to   bring in all the name I need to bring the name in  that full file path I need to put it in right here   so we're gonna do that with this trendy b6 okay so  we're gonna call that the receipt row next up what   do I want to do not want to make sure that we have  our entire final path I want to add this whole   path I want to we know the folder right we know  it's this is the folder and we know the picture   name here so how do we combine those well I can  combine this along with that but I need to make   sure that we don't include this pastures here it's  important for our formulas we need it for here but   we don't need it inside our large file name so  let's get rid of that and combine them and get   rid of the Astro so we can do that with some code  so the first thing I want to do is I'm going to   say dot range I twelve that's what we're putting  it I twelve dot value equals why I twelve this is   I twelve I'm gonna put it in this cell right here  okay so I twelve equals what is it can equal it's   gonna equal basically whatever is in l2 l2 because  that's our long folder but not just dot range l2   but remember we have to remove that asterisk so  I have to replace something so I'm gonna replace   it it's gonna be equals replace and what are we  replacing we're replacing the asterisk so comma   and then the answer is here what are we replacing  with nothing okay so now we have the folder path   so and what and the filename we know it's in N  n dot range and and what and in the receipt row   we know the remember let's string this up so you  can see it a little bit I'm not gonna need that   we can bring it down here we're not gonna need  that much so and and the receipt row we know the   receipt Road in this case is three and receipt row  alright great so we know that that so I twelve the   file path is going to be again basically it's  going to be this entire l2 whatever's an l2 not   including the asterisk plus this that will build  out our it's gonna put it right here a full file   path I want to display the picture here in 2k  and I want to get ready to put in the date the   type the amount in the category okay so to do  that we can just continue building out so now   we're done with that we've built it out and we  can say before I want to set the full two receipt   to to I want to know that the receipt is coming  from the folder so I'm gonna set a setting here   folder seat beat form must be true so let's  go change that so we know dot range b4 equals equals true set folder receipt to true we know  that's gonna help us because we need to know   where that receipt came from it came from our seed  folder okay next up I wanted to run a macro that   actually displays now we have the file now we can  display the macro but I want to run that use that   a macro for that so we're gonna remember called  display main picture let's build out that macro   so we can see it I'm gonna copy that again and  we're gonna add it to here sub paste it display   main picture so we'll add that in as well what  else do we need to do okay so what if b6 what   if there are no receipts to add then we need to  add an else else in case there are no receipts   no receipts to add then what do I want to do  in that case I wanna hide I want to hide these   rules I only want to show these rows if there's  receipts to add and I want to hide 12 through 14   so I'm gonna hide that maybe even 11 through 14 we  don't need that we don't need the too much spaces   so let's hide those rows if there's no I don't  want I just I wanted to look like this nice and   clean right I don't want to show any more maybe 11  through maybe one other one so I want it to look   like this I'm nice and even and make sure that we  also size these buttons ingly we don't want   them to size with the cells so I'm gonna right  click everything and then go into the properties   and make sure that we set the properties to move  but don't size with cells okay so I want to hide   11 through 14 so how do we do that we can just do  that with one line of VBA code right here else dot   range 11 and and what in the colon right and then  quotation marks can't 14 and then of course entire   row hit equals true dot entire row dot hidden  equals true it's gonna hide them all hi rose   for receipt entry we only want to show those more  actually entering or seat next up I just want to   put a little message box there are no receipts  to enter or to relook input review to review   let the user know there's no and then of course  I want to set the full to receive default dot   range b4 value equals false so why are we doing  that while I was sitting at b4 to false because   I want to make sure that we know that there's no  folder receipts that are coming in well that'll   help us later on we know what because we're gonna  display receipts based on whether they're selected   here or whether they're select them display them  here and here lots of receipts display just so   we can see them very easily all right next up  what else do we have to do we're done with that   little Macker so let's create some more I wanna  what about when we add a new receipt we started   that let's continue that right now when we add a  new receipt we just need to clear some fields so   with sheet 1 what are we going to do we're gonna  basically I want to make sure that we show rows   11 through 14 we need to make sure 11 through  14 are not hidden we want to give the user the   ability to add in these fields so we need to make  sure that 11 through 14 are not hidden we can do   that just simply by I'm hiding them and in this  case it would be false so all we need to do is   copy this paste it here and put in false unhide  rows now that we've done that what else we need   to do I need to make sure that we've checked to  see if b6 value is greater than 1 then we want   to show the saving next I'm gonna have the user  the ability to save and next so let's duplicate   this button and I want to give them also ability  to go save and this one's not gonna be linked so   let's not link that this one we use are going to  be able to enter the text so call this save and   next and then just give them a little air save it  next this way they can continue to move through   the receipts so we'll give them that ability great  all right what is next next up I want to show that   button I want to show that button let's get that  button a name so we can clearly know we'll call   the save next button if not range b6 not values  greater than zero then what do we want to do then   dot shapes save next button dot visible equals  Emma so true equals visible else of course it's   not visible right we don't want to show it if  there's no receipts that are gonna be else make   that invisible hide that shape but there's no  receipts equals and is so false okay so we're   just gonna basically I'm so right in to show  or hide review receipts but and basically that   buttons just gonna simply save the receipt and  move on so now we're gonna show it now that we   do it now we just gonna clear some fields right  when we're adding a new receipt I want to make   sure that these fields are all clear so let's do  that right now let's clear those from what fields   are they dot range e twelve I guess we have let's  move this over so we can see what we're looking   at here bring it down here and then we can bring  this up here e 12 we have e 12 we have G 12 I'll   go through these in a second efore teen we have  G 14 and we have AI 12 let's just go through this   and we're gonna clear the contents about clear  contents clear fields let's review that double   check we have e 12 we have B 14 we have G 12 G  14 and we have I 12 those five fields we want to   clear what else do we want to do well I want  to make sure that we're showing some buttons   and hiding other buttons so let's take a look at  these buttons I want to save this button here and   I want to hide this buttons show in hide shapes  ingly dot shapes save expense button dot   visible equals msot true right I want to display  the Save button and I want to hide the add new dot   shapes add new button or I was what was the name  of that I don't remember let's just take a look   also in the file we can store the files we called  it add expense button so we want to make sure the   add expense button is hidden expense button dot  dot visible equals M so false so we're hiding   false hi add new button make sure we comment out  and then show save button that's gonna be Fred new   and then I want to just select let the user know  we're gonna select let's say e twelve select e 12   so we'll can write some code for that now it'll  be at dot range e twelve and then select gives   the user the ability so let's take a look at that  let's run that code and see if there's any issues   with that save our file first before running our  code run that all right take a look all right that   looks good all the fields are cleared out our  save and next just showed up our we have three   receipts to view save expenses so let's move this  over and add that macro assign the macro and now   this is called add and your receipt so we click  that button it's been add expense alright now   that it's working perfectly so it's ready to add  new expenses now let's assign the macro to the   three receipts to review so right click assign  the macro and we'll call this check and review   receipt so click on that and we'll click on that  and it's nice now we have the full file path the   correct file path here where you want to and we're  ready to enter a new alright we'll click check and   review and we'll take a look at that alright now  we have save expense save and next three receipts   to review we've already added the macro right  click assign the macro recheck and review that's   gonna get a sign there everything is looking very  good we are ready to move on what I want to do is   I want to display the picture I want to display  it right here so let's write some code to display   we now have the full file name so let's go back  into macro and write the code so we can display   that picture so let's write some code we'll scroll  down here let's create some code to display that sub display main picture there's gonna be one  on the table to that I want to display so we   make sure to differentiate between those two with  sheet one if there is already a picture I want to   delete it so we need to check that on air resume  next so just in case there is no picture what   does that picture dot shapes and we'll call  it receipt pic and then I want to make sure   that's deleted anytime we add a picture I want to  make sure that any existing one is deleted so we   can do that and then on air zero that's gonna  check as if there is no picture it would cause   an error so next up we're ready to go I want to  make sure if we're gonna display a picture we   need to make sure that I twelve was not empty  so that's the first thing we want to check if   range I twelve dot value equals empty then exit  so can't do anything if there's no picture next   up picture path is equal to of course I twelve so  we can copy that we just wrote it here remember   we defined picture path as a string up at the  top here we remember it's all the way up here   picture path here so we've already defined that as  a string so that is going to be the picture path   next up what I want to do is I want to make run  another check to make sure that that's accurate   it's an accurate path otherwise it'll create an  error so we can do if directory of picture path   dot VB directory that's the directory equals  nothing or empty in this case double quotes   then exits up because we not only do we need a  path we need accurate path sub exit on incorrect file name okay file name okay so now that we know  it's correct now we're ready with what are we   entering we're inserting a picture dot pictures  dot insert picture path we're gonna insert that   picture path and then of course with the shape  range we need to do a few things I want to lock   the ratio right so we can do that with lock aspect  ratio equals MSO true lock that aspect ratio what   else don't want to height and maybe around 350  equals 350 and then of course that I want to set   the name name name is going to be equal to receipt  page always a specific name okay so now we have it   now we've set the name and everything now we can  work with it once we've set now we can close out   these two and widths and focus on our picture in  itself so with dot shapes the one we just created   you see hey what are we going to do with it we're  gonna do a few things we're gonna place it where   do I want to place it dot left I want to place  it right around sheet one range K for a K for   dot left and also want me the same thing with  the top top sheet one range k for dot-com okay   so that's gonna place it now we've set the size in  the place and then then I want to put just in case   there's an error bear always good on error go to  no pic just in case there's an air to avoid some   air so we can go down here just put no pic okay  so we can close up that we're pretty much done   with that we can run our code check for errors and  see if our pictures display if we already have the   file path so we can shrink this up and then run  this code and then we already added macro for   this one here we don't need that okay so and then  run it here and then we just have dot left and   we forgot the equals increment doesn't use equals  but top left and top top does does use equals okay   there we go now our receipt is nicely so now when  we load our receipt it's gonna display this nice   receipt that's exactly what I want now what all  I needs you so the receipts display the user can   see the receipt now all they need to do is enter  the values and then save the value right so to   put in there type will want to type and we need  a category let's add some types in there let's   just take this type here you can just put in three  types let's add a list here somewhere on here it   can be we can put it anywhere really and actually  we can we can bring these out a little bit and   put it in all this I'll put in our types here and  I'm just gonna paste in the values here just some   anywhere we can paste those values in okay so I'm  gonna call these types and then we're gonna a so   I also want categories two categories here so we  can shrink this up and put it some categories I'll   put them in here categories and I'll just copy and  paste them and then reduce the duplicates so you   can do those and then paste them in here all right  so we have professional fees entertainment and   then business okay perfect that's enough for now  you get the idea we can delete these duplicates   here so we have categories too let's add this  categories and of course you can add your own   and now that we have this I'm going to add some  data validation here data validation list here and   call this categories and then this would be type  data data validation and then list now we have our   types equals types okay so we have our type and we  have our categories here and we have our mouth now   what do I need to do now I need to save it and we  need to save it we can also format this as us all   right looking very good now all we need to do is  write the macro save it so let's write the macro   and save this now I want to use the same macro  for both save expenses and save and next so it's   actually gonna be the same macro that runs both  of those so keep that in mind as we build out this   macro back into the VBA code let's expand this  you can see you can see more of the screen and   we'll add some additional scroll up we got review  we've got add new and down here we will put in a   new macro and we're gonna call this save expensive  sub save expenses and we're gonna of course we're   with sheet 1 we're focused on sheet 1 we want to  make sure that we have some required information   we got it makes least make sure that there's a  date here that date is very important and we also   need to make sure that there's an amount so we  need to make sure that at least e 12 and 14 have   value so that's the first thing we're going to do  just to check if dot range e 12 died value equals   empty or of course 'if 14 equals empty if either  one of those are empty and we need to let the user   know so e 14 equals empty then what and mess box  please fill in the required date this is cowboy   date and amount fields exits up ok so at least  they can at least they know they must add those   because those are too important also I'll need to  know is this a new expense or it's an existing or   if they click here it's going to load the expenses  so I need to know is it is it a brand new one or   is it existing we knew that using we will know  that using what is in B 555 is true well know   that it's a new expense because we need to know  the road the rule is either gonna be here the 21   or it's gonna be the first available row in this  case 24 so knowing whether it's a new receipt or   an existing one that we may have loaded above it's  gonna help us so b5 is gonna tell us just that if   dot range b5 dot value equals true then it's a new  then it's a new receipt else else existing and if   okay so now we need some so now that we know we  just need the expense row is equal to remember   expense rows been defined up here expense row as  long evils if it's a new one we're just gonna find   the first available so that would be dot range  and we'll use D and then just use a large value   9 9 9 and then end Excel up dot Rho plus 1 dot end  exhale up up not down dot Rho plus 1 dot row will   get us the last row of the value plus one will be  the first available row ok so we have the expense   row if it's a new receipt but what about if it's  an existing well if it is existing it's gonna be   located in BA expense row equals dot range b8  Valley ba it's always gonna keep track of art   and I'll show you how we're gonna do that existing  Roah basically what's gonna happen is it's very   simple if the user selects on here this is gonna  change we can write that right now make it very   easy let's add some additional formatting so I'm  gonna go down here I'm gonna highlight these cells   and we're going to home conditional formatting  manage rules I want to know what row they're   selected new rule use a formula equals B eight  equals row that's probably easier bro so when that   happens what I want I want to show something very  distinct I'm going to set a distinct fill color   fill effects I'm gonna do the same theme using  these two colors as a little bit letter darker   and then I'm also gonna change the font to light  and to bold so it'll show up okay great so now   when you use a selects on I wanna we'll add some  code when they select on now we know the roads   very very clear okay so let's continue with the  save and then we'll load do the load after that   so we'll continue with this so we can finish  so now we define the expense row whether it's   new or existing now everything else is the same  right everything else in the Mac is the same so   all I essentially want to do we won't use data  mapping there's only a four or five fields here   so basically what I want to do is we're saving I  want to bring this the date down to whatever the   row is I want to bring the amount down to whatever  the row and you know the type in the category and   the receipt so all five of those basically need  to be saved out to whatever row whether it's an   existing or new broke so new roads gonna be saved  right here so how do we do that well just very   simple we could just write some code dot range  D of course that's the date and the expense row   dot value what does it equal equals dot range e  12 and that's the date now let's take a look at   a DNA expensive equals D 12 D in the expense row  equals D 12 perfect so now what we need to do is   write similar code we can copy and paste this  for the remaining four to five fields I think   it's a five total date so we have date E but of  course that would be amount what's next the next   one will be type next one will be category and  then I'm gonna write in another one for of course   we need the Racine e'en but I'm gonna write in  there that would be the file path for the receipt   because I'm gonna say that too so let's update  our this would be e F G and H can I skip the   rope purposely so now we just need to update these  system be4 teen e 14 g 12 g 12 and then we have g   14 so that's gonna map like a map it's actually  instead of mapping we're typing it out in this   case but i want to only want to add a receipt from  the archive if the receipt from the folder moved   to the archive folder right not only do i need  to take this receipt i want to put it into the   archive folder right because i it's already been i  want to i don'