How To Create A Drill Down Profit & Loss Statement In Excel From Scratch [+ FREE DOWNLOAD]

hello this is Randy with Excel for Freelancers  and welcome to the drill down profit in loss   and this week I'm going to show you how to create  this incredible profit and loss sheet with drill   down capabilities and we're going to do it all  from scratch you're going to watch every step   every formula every function and every line  of code I cannot wait so let's get started all right thanks so much for joining us this  week I've got a really fantastic training and   we're going to do it all from scratch as we do  from time to time when we have a relatively easy   application this week is going to be incredible  now we've done profit and loss before but never   with drill down capabilities and we're going to  show you how to do that just on a single click   we're going to go over every step for you right  here so I hope you'll stick with us until the   end if you have not subscribed yet go ahead and  click that subscription down below and of course   that notification icon Bell that'll ensure that  you get the trainings as we release them if you   like this template all you can download it for  free using your email or your Facebook Messenger   from the links below however if you want 250 of  my best templates and a great way to support the   channel you can do that for just a low price  and that's all of my templates for a very low   price and I also have the availability of the PDF  code book so you can get every line of code in a   beautifully organized PDF F documents and that's  with every single template that's going to help   support our Channel I'll include the links down  below that's a great way to support us all right   let's get started this is what we're going to  be creating I've got another blank workbook in   which we're going to start on but this is the  sample we'll be having from and two dates we'll   be able to print report refresh the report we'll  be able to drill down on any type of income cost   of goods or even expenses just by clicking on it  and of course we're going to be able to drill up   just by clicking on that okay so that's going to  create our report for us it's going to include   total income cost of goods sold and then the  gross profit which is simply the total income   minus the total cost of goods sold and that's  going to get us our gross profit and then we're   going to subtract the expenses in that and that's  going to get us our net profit that's a standard   profit and loss drill down capabilities is a  great feature because we want to know we if we   have 24 951 materials sold we want to know what  transactions are making up for that right we want   to know all the transactions within the given day  period of those transactions so that's that's what   it's going to do us for it's going to give us all  those transactions now this data is going to come   from a database called transactions here I've got  a transactions here this is all the income expense   transactions so this is our original data this is  what we're pulling it from and so what we're going   to be doing is pulling it and then bringing it and  summarizing it and bringing it into the profit and   loss so how do we create one we're going to be  doing that of course from scratch so we're going   to get started I'm going to move this one aside  and I've got a brand new one actually which is   just going to be blank here and this is the blank  one that we're going to be starting on so we're   going to create every single step for you step by  step including all the code so we're going to get   started right away on this first thing what we  want to do is we want to give it a title so what   we're going to do is we're going to call this  drill down and I've already updated some of the   fonts to make it easier profit and loss right so  that's what we're going to do drill down profit   and loss that's we're going to be creating we  want some icons in there we want to create some   buttons and we want some from in two dates now  the from two dates we're going to be putting   I'm going to put that from date right inside e so  what I want inside the D3 we're going and putting   the from right I want that from to go right here  I want to know and then we're going to skip one   that's going to be our date and then we're going  to put in two here we want to know the two days   so these are going to be dates let's just say 1 1  22 and then we'll put in let's say three same with   our sample 3122 okay so here we've got our dates  now I'm going to call these in white I also want   to background on this and we want some buttons so  why don't we insert the buttons now and what we'll   do is we're going to do insert okay and then what  I want to do is I want to insert a shape so we're   going to do illustrations and then shapes and then  we'll just do a rectangle we're going to create   two buttons one for the print one to refresh the  report so they're going to be about yay size here   and I'll give them a specific color in a second  so this is going to call refresh refresh report   we've got some icons to go along with that and  something like that but what I want to do is I   want to leave room for the icon so I'm going  to right justify that and we'll also want to   give it a color that's relatively specific to  our which is kind of a brown I found so give it   a shape format and then we'll use a shade fill  now I've got some pre-saved colors so we'll do   that right and then what I'm going to do is I'm  going to duplicate that for the print button so   I'm going to do d control D is going to duplicate  that I'm going to bring that right over here and   then we're going to call this print report so I'm  just going to update that here called print report   so we got the print report we can make this one  a little bit smaller we want some icons I've got   some saved icons actually we got borders around  here which we don't really need so I'm going to   hold down the shift and I'm going to select both  buttons and then I'm going to go into the shape   format shape outline we're going to go none so  it's basically no outline on that we don't need   an outline now we want to do is we want to insert  the icons right I've just got a few icons here   saved in the table so we're going to just insert  and then we're going to do a picture right and   this is going to based on this device and I've  got a icon here we're going to insert insert   and I'm going to hold down the control I've got  the printer the refresh and our icon that we're   going to use so I'm going to insert all those  I'm going to change those to right around 0.2   so our icon here this is going to be our drill  down icon I'll make that bigger okay now we've   got some icons there in white now we can't really  see them but that's okay I'm going to insert a   background on this that's going to help us also  you saw the background on the sample so we're   going to click on the background and I've got one  saved already and it's going to be this background   here that I've created and I'm just going to put  that in here okay so now we've got a background   so now we can see our icons which is kind of nice  I'm going to move that print report here and I'm   going to move that refresh right about here okay  so I'll increase this a little bit and then what   I'm doing is I'm going to hold down the control  and I want them equal of course inside our middle   align the middle and we did the same thing I'm  going to group it I like to have that group and   let me do the same thing with here I'm going to  line the middle so I'm holding down the control   while selecting both I'm going to line that middle  and I'm going to group it all right so I like that   that's the way it looks now I'm going to take  both those buttons and I want them aligned in   the middle as well they're relatively worse so I'm  going to color these two holding down the control   I want that to be the white background so we're  just going to click on here and then we'll go   click on the white background that was according  to our sample so we have that okay so now we also   don't need grid lines in here so inside the view  we're going to unclick the grid lines okay so now   we've got our our title bar now what I want to do  is I want to title bar I've got one already merged   and centered but I also want to create some named  ranges on this actually let me clear those near I   have some save but I want to clear them because  I want to recreate them so what I'm going to do   is I'm going to call this one from date so that's  what we call fro and from DT okay and that's the   name range I'm gonna assign to this I'm going to  call this one two date so we're going to go to   and then DT so that's going to be the two date and  that way we can use these in formulas we can use   these in the code it's really easy to work with  when we have that okay so what I want to do now   is I want to put that title right here so I'm  going to call it profit and loss but I want the   title to be dynamic based on the from and the two  date so we'll use a formula on that so we're going   to do is going to do equals and then what we're  going to do is profit and loss LL loss okay and   then what do I want to do I want to put let's put  a dash in there and then I want to do from okay so   now I want to do the colon and then a space and  then what I want to do is and right so I want   to add in the date right so basically it's from  date right but I want to format that front end   I want to give it a specific format because if I  just do from data may not be formatted correctly   well let's take a look and okay so we'll do that  so and I also want to do add in the two days so   then we're going to do quotation marks we're  going to do another space two then the colon   and then I want another space and then I want the  two date here so and the two date okay two days so   we're going to look for that okay so let's take  a look at that and then we can see now what we   could do is we see it's not formatted I also want  to give it uh of course so we can see it because   it's in white so what I'll do is I'm going to  format that right click that on the format those   cells sorry it's off the screen but basically  what we're going to do is we're going to give   it a fill effects and then I want a two color  fill effect so we're going to go from let's say   this light brown here to this darker brown here  so we're going to give those two colors here okay   so that's the color I'd like and click OK and okay  now we see that those dates aren't formatted right   so that's what I was talking about what we need to  do is format those so inside the formula we can do   that right so what I'm going to do is I'm going to  use the text formula and I want to give it a very   specific format so what is that format I'm going  to give it comma right so then inside quotation   marks mm slash DD slash y y y y okay we'll do four  y's okay then I'm going to end parentheses we're   going to do exactly the same thing for our two  dates so it's text then what we're going to do   again is we're just going to copy this right here  because we've already created it we don't need to   do that again copy that paste that in there okay  and we're good okay so we're going to hit enter   so now we see profit and loss from 1 1 to 331 if  we change the change the dates here right we are   going to see it automatically change inside  that header that's what I want because when   we print that report we want to know what dates  are there so we're going to change that okay so   it automatically changes very good so the first  thing what I want to do is I want to put inside   here I want to start building that report out now  what I want to do basically I've got some a little   bit saved information we're going to save our work  so far so we have the header pretty much set up we   have the two buttons that we're going to use so  what we want to do is I want to put income here   I want to put income in that so and then I want to  put total income and then expenses cost of goods   sold and so on and so forth it's going to go on  column C okay so what that's going to do is it   going to involve a macro when I refresh report  I want all that information to be displayed in   there and I want it to come from our transactions  so what I want to do is I got all of our income   and expenses here I've got a transaction date  we've got an income type whether it's income   cost of goods or expenses right well obviously  we don't think we could do this this should be   cost of goods sold and that's sufficient along  with that we've got the account that's associated   right we have like materials sold labor started  interest earned those are of course the accounts   associated with income and then maybe we have  materials purchased and things like that those   are cost of goods sold we have a description  and then we have an amount so what I want to   do first is I want to separate them I want to  know all the incomes within the given date I want   to know all the cost of goods sold cogs that's  cost of goods sold never mind the formula here   I was just using that for some other thing but  basically what we're going to be doing is I just   had the accounts here and so basically what we did  is we used this formula to look up the individual   account so that was a little quick way that I  could associate this account with the income if   we simply index that now of course we could get  rid of that you know index that we could just   copy and paste that but that's fine the way it is  so what I really want to do now is I want to make   sure that that we associate all the income all the  cost of goods sold and all the expense and I want   them separated so what I'm going to do is we're  going to create an advanced filter right like this   right now we want the advanced filter is going to  be a transaction date now we notice that we want   to make sure that it's greater than or equal the  from date that means all of our transaction dates   we want only to create an advanced filter and we  want the results greater than or equal to from   date we also want the transaction dates less than  or equal the two date and then what we want to do   is we want to have VBA add in income or expense or  cost of goods sold once it does that it's going to   create some results and I want those results to  go in this area I want all those income results   to go here and I want all the accounts to go here  and I want to know all of the expense results so   basically we're going to create an advanced filter  as we insert this income we want those results I   want those accounts to appear here then I also  want to know the totals and to know the totals   we're going to create a formula and this formula  is right here okay to help things move things   along right because we still have a lot of code to  write so what we want to do is as we add accounts   and we'll add them in then I'm going to bring  down the totals I want to know the total for   a specific account for example if I want to know  the total for materials sold and I copy that and   I put that in here I know the account is materials  sold right so I paste those values in here we know   based on this right this this formula here it's  going to be based on that first row that it's 24   000. how are we going to know that well what I've  done is I've created some named ranges for this   and we can use those named raises income type  account and amount let's go over some of those   named ranges so that we can familiarize ourselves  with what I've done so we're going to go into the   formulas and the name manager and we're going  to look at some of those named ranges the first   thing we want to know we've already focused on  from date right what we're going to be doing is   we're going to go into the transaction now I've  created four different named ranges one for the   account one for the amount one for the date and  one for the type type so that's all based on this   here so let's go over those if I tab into it we  see that we've created an offset formula offset   so I want to know all the transactions based on  column C column C is where our account is and I   want to count all the transactions from column A  I'm just using column 8 account and that's because   I know that every single transaction is going to  have a date so that field is required in column   A that's why we're using column A to count it now  we're also including the headers now that's very   very important because when there's no data or  when data gets deleted we certainly don't want   to create an error so we always want to create  include the header in Row 2 and then we're going   to go one row down we're going to offset it  one row down we're also going to count we're   also counting including the headers except we're  subtracting one so what that's going to do is make   sure that we have the name range that includes  all the data so when we tab into that we see those   dancing ads they go all the way down to the bottom  of the last row of data here as you can see and it   is located in row 367 so we know we've encompassed  all the data and I've done just that with all of   them I've got one for the amount field here I've  got one for the date field here and I've got one   for the transaction type field here so knowing  that we have those four transaction account   transaction amount date and type we can use those  in formulas to sum up using the sum if to notice   how many materials sold within a given date period  that's why we used named ranges so when we have   an account Associated here and I bring down this  formula it's going to Total all of the materials   sold for the given date using sum if what are we  summing we're summing the transaction amount and   what we're going to do is we're going to include  we only wanted based on those transactions if the   date is greater than or equal to from date this is  why we create those named ranges because it's much   easier to read this than it is a cell address I  also want to know the transaction date now that   transaction date also has to be less than or  equal to two date and I also want to know the   transaction account we need to make sure that is  only for the transaction account located in aj3   which is that first cell okay so the idea is when  I take this formula and I bring it down how far   are we bring it down well it depends on how many  accounts are associated here our Advanced filter   that we're going to write the code is going to  create those accounts here based on the date   range then what we're just going to create an  advance based on the criteria here it's going   to create all the income of course we're putting  income here VBA will take care of that putting   income here it's going to list all those income  accounts then we know we're going to determine   the last row let's say the last row is 11. I'm  going to take this formula I'm going to bring it   all the way down to row 11. that's going to ensure  that we have the totals then all I need to do is   just bring these directly inside the profit loss  after I put the word income here then I can just   simply take those accounts and bring them in here  and then I can total it up here so given that we   also have some totals we need to know the total  income the total cost of goods sold and the total   expenses well this summary total is going to help  us a lot doing that right so we have income cost   of goods sold gross profit expenses and net profit  so this formulas can help us out because those are   always going to be standard based on every single  profit and loss so we can use again a formula to   determine that again using sum if we're going  to do transaction type this time we're going to   do the transaction amount based on the transaction  type of P2 only those income accounts based on the   transaction date greater than or equal to from  date the transaction date less than or equal   to two day right so that's going to determine  all the income so we know and I've given this   a named range called total income see the named  range in the upper left total income I've done   the same thing with this exactly the same formula  all we need to do is bring down this formula for   income and cost of goods sold I've given this a  name range called total cost of goods sold that's   going to help us inside our VBA we'll be able to  use this name range inside the code it's going   to be very helpful I also want to know the gross  profit the gross profit is simple math right is   simply the total income minus the total cost of  goods sold and that's going to get us our gross   profit next up we want expenses right we're going  to use the same similar formula again based on the   expenses here right based on what is located  in P5 right here also based on the transaction   date transaction date same exact formula only  differences we're basing it on this that's going   to get us our total expenses now once we have our  gross profit and we have our total expenses we can   determine the net profit because the net profit is  simply the gross profit minus the expenses that's   going to give us our net profit so again every  single one of these has a name range associated   with it right here total income total cost of  goods law gross profit total expenses and net   profit we'll be using all those inside our code  so we know how we get to the information here   we know how we're going to get those using the  advanced filter which we're going to go over   so we're going to do that for each income then  we're going to do it for cost of goods sold then   we're going to do it for expenses right and that's  the first part of our report that's how we're   going to create that p l so let's get to the code  and start writing the code that's going to help   us create this right here okay we're going to get  to that right now we're going to go into the VBA   inside the developers Visual Basic here alt f11  the shortcut there and we're going to create I've   already created a module called profit and loss  this one here is our sample okay that's the one   you sample that includes all the code right we're  not going to be using that one right we're going   to be writing it from scratch so we notice here  we've got two macros right there's nothing in them   one's called profit and loss refresh and the other  is called print report okay so we're going to do   those right now all right so the first thing we're  going to do is dimension some statements here for   our variables so we're going to Dimension the pl  row I need to keep track of the row that we're   on and the profit and loss as long okay next up  I want to know the last transaction we're going   to be running Advanced filters so we know the need  to know the last transaction row and that's also   going to be a long variable next up I want to know  the last income Row the last cost of goods sold   row and the last expense row so last income row  remember we run those Advanced filters we need to   know how many income there is right as long okay  also I want to know the last cost of goods sold   row as long and also the last expense or last  expense row as long so that's really important   okay great so next thing what I want to do is I  want to make sure that we're going to clear when   we refresh that data I want to make sure that  we're clearing everything out of our report so   let's take a look inside that report now and we're  going to clear it out so what I want to clear out   I want to clear everything from we'll just say  B we're not going to probably use that column   B let's say B7 we'll keep this as income this is  always going to be income nothing's ever going to   change so B7 right and we're going to go all the  way to let's say I 999 I want to clear all that   out so we've already created a this is called  profit and loss so that's the sheet that we're   called profit loss dot range B7 through I and then  let's do a large number dot clear content so we   want to just clear that report clear contents  clearing the report first clear all values in   existing report next primarily what we're going  to do is we're going to work directly with the   transaction database right that's where all of  our data is located so we're going to do with   transaction database and I want to make sure that  also we determine the last transaction row so last   transaction row is equal to a 999 and xlip row I'm  using Auto hotkey to automate that so you saw the   typing a little bit fast so that's called the last  transaction row last transaction row okay once we   have that last transaction row what I also want to  do is I want to clear the contents of any previous   data inside that so what do we mean by that let's  take a look inside and we get some results right   if we've created some results what I want to do is  clear that out okay so taking that out so inside   the transactions I've got some data here remember  data's here so starting from a j3 all the way to   a q I want to clear that out right so any data  that might be here from previous filters I want   to clear it out so we can do that here dot range  we're going to start out at aj3 aj3 through AQ   and we'll just use large number 999 dot clear  contents okay clear so we're clearing that out   clear contents very good so once we see a clear  previous results clear previous results Great   once those are cleared then what we'll do is we're  ready to set the accounts for the advanced filter   remember I said we're going to alternate once we  create that advanced filter inside the workbook   we need to create that so we've got our Advanced  filter right here we've got our transaction dates   here we know we're going to do but what I want to  do is I want to set this the first one is going to   be income second one is going to be cost of goods  sold and the third one is going to be expensive so   AC3 must equal the word income so we're going to  write that here so dot range AC3 AC3 is going to   be equal to income setting that criteria income  and what that's going to do is set the income   criteria so we only want to know those particular  income accounts within the given dates right so   only those transactions okay set income criteria  okay now we're ready to run our Advanced filter   okay and our Advanced filter is going to be just  this we're going to focus on that again that was   Auto hotkey so what are we going to do so the  first thing what I want to do is I want to know   a that original data is A2 all the way through  e but we'll use we're going to use the blank   for the results sometimes we want those results  so I'm going to go through just adding one more   column for blank in case we want to use that A2  through F so that's what's going to be A2 so let's   make this 2 through F and where's our criteria our  criteria is located directly inside here so let's   take a look at our criteria and see what cells  our criteria is here aa2 all the way through AC3   so that's what we're going to set our criteria A2  through AC3 and what do we want the results to go   well we want the results to go inside only our  income so we want to set our income where's our   income here our income is located right inside  here we want those results I only want the   accounts to come here aj2 that's where our results  are going to come that's the only place that we   have so let's set that our results going to come  just that the formulas we're going to bring down   in the second line of code so our results are  simply because I only want those accounts right to   a J2 so we just need to change this to jaj2 that's  sufficient unique equals true okay once we have   that what we want to do is we want to determine  the last income row so here we're going to go the   last income row it's equal to dot range and we're  going to focus on AJ 99999 and XL episode actually   I got I got that as a let's see this one right  here XLR okay that's a quick AJ this is going   to give us our last results for our lasting real  based on column AJ okay great once we have that   that's the last thing row let's put a note on that  last income row okay next up what I want to do is   I want to make sure that there there's actually  income so if the last income row is greater than 2   right then what I want to do is bring down that  formula let's say greater than 2 then bring down   that formula how are we going to notice that  well let's take a look where that formula is   located that form is located directly inside  AK so what I want to do is I want to take this   formula I want to bring it down as many rows as  necessary inside a k so we can do that with this   line of code then dot range AK 3 through a k and  the lasting crew and last income row dot formula   we're using dot formula equals and where's that  formula located it's located in ak2 dot range   ak2 dot formula okay formula okay there we go so  bring down income formula why don't we just run   the code and see where we are now and see how  how far we've got all right let's take a look   we're going to save our work always especially  before running the code see if we got any issues   last row right we obviously we don't have a  variable called last row we need to make sure   that that's last income row right that's the last  last transaction row that's the one we created up   here last transaction action row that's the one we  want okay running the code again here no issues on   that let's go ahead and take a look at the data  we see that we've got here we've got numbers here   they don't necessarily need to be formatted but  they do need to be formatted once they make it   into the report so again we have the results of  six results we've brought in the formula down   here you see the formulas automatically based  on our values here based on our account so we   know the total expenses are 1734 for the period  gross profit 49.24 and so on and so forth this   should not be really here that's not really an  account here so I would change it's not really an   account here so we want to make sure that's kind  of a just a let's call this uh um something else   something out that doesn't make sense so let's  just call it return income labor earned let's do   labor earned sold okay I like that better because  it's not really that doesn't make much sense huh   that label there okay so let's rerun it here it  makes a little more sense here and let's go back   into the data here and take a look at it and make  sure that we're on on par here and I see that we   have here let's pull it up here okay good I like  that here labor sold interesting so these are all   the income accounts expenses not isn't is not an  income so we should update that but let's take a   look at that I don't want that as an income so  we'll take a look at income and we want to make   sure that I want to remove that expenses this one  this is called the labor sold okay that's better   some some data issues here so we don't have to  worry about that so it looks clear all right   continuing on I've just rerun the macro here we'll  take a look at it here and as we see here we've   got some income accounts here so materials sold  labor sold interest earned and dividend earned   perfect and we've got the accounts associated  I like that okay great so now what we're going   to do is we're going to Simply repeat the process  for the cost of goods sold and repeat the process   for the expenses running that advanced filter so  let's go ahead and do that and let's modify that   accordingly here inside the code what we want to  do is simply duplicate it so but I'm going to take   this here we've already cleared the content and  then I'm going to drop it down we're going to   focus on the cost of goods sold so this is for  income so let's put just put a little note here   called income results and now what I want to do is  focus on cost of goods sold so let's put in cost   of good get sold results so I'm going to paste in  that and so now the income criteria this is going   to be cost of goods sold so we're going to change  this to cost of goods sold only those accounts   that are associated with that so we're going to  set that cost of goods sold focusing only on that   one now cost of goods sold now what we want to do  is we have the same criteria right but we've set   it and we have the same original data we have the  same criteria we want the results in a different   location where do I want to put those results I  want to put those results right here located in   am previously was AJ free income now we want those  costs to get sold right located in am2 is where we   want those results so we're going to update this  right here instead of aj2 we're going to put am   and then we want the last cost of goods sold  we have a different variable on this time cost   of goods sold and this is called the last cost of  goods sold so we're going to update that variable   cogs and again we're going to update it here we  also want to make sure here last cost of goods   sold so now what we want to do is we want that  formula that form is not going to be AK anymore   that formula is now going to be where it's located  right here in a n so a n is what we're going to be   looking for here so we're going to update that to  an3 last cost Goods solder making sure that there   is data a n and then we're going to use the last  cost of goods row there we go so and of course   it's going to come directly from a n all right I  like that let's continue on and we want to make   sure that we have everything and we're going to  run that macro and then we're going to go ahead   and take a look at the results generated by that  okay so now we see we've got cost of goods sold   here right here everything's here looks really  good just the way I like it and the farm is Run   Okay lastly all we need to do is repeat that  for expenses so I'm just simply going to copy   this here and then we're going to modify that for  expenses adding a row and then we're going to call   this pasting that in there m calling expense  I know I'm moving fast we got a lot to cover   and of course you can rewind this and watch it as  many times as you like so we're going to set that   this can be called the expense scratcher so we're  going to update that through expense criteria I'm   going to change this we want to set the criteria  account type to expenses we want called expenses   let's make sure that we have that right we only  we're going to focus on those ones that are called   expenses right we're only looking for expense  type that's all we want inside the type and we   want those results to appear right here in AP and  we want that formula in AQ so that's what we're   going to add it up okay so we're going to create  we want the same criteria we're going to set the   same here criteria except this time it's going to  be expensive we want those results to come into AP   and we want the last called expense row exp that's  the variable that we created up here last expense   row and we want it inside this time is going to  be a p right we want to know the last expense row   based on AP we're going to update this to expenses  the node and then what we're going to do is I want   to know the last expense row update that variable  and we also want to do right if it's going to be   AP this one our form is going to go into a q  q is where our form is located for expenses   so we're going to update this to AQ and we're  going to update this to AQ and we're going to   change this to expenses and we're going to change  the one above to expense formula we're going to   change this to cost of goods sold we didn't do  that before so now we've updated everything so   we know let's take a look at this this is going  to be API results we're going to check for the   last row of our last expense row and we want to  make sure that it's greater than 2 if it is we're   going to bring down the formula for last expense  right updating this last expense row that formula   I like that let's run it all right take a quick  look down and see what we have created inside our   results so we've got our expenses account here  we've got our totals here remember the form we   don't need to format it for currency or anything  like that accounting because we're going to bring   it over we're only focused on the data so we've  got all of our results I really like that now what   we're going to do is we're going to learn how to  bring these over into the profit and loss okay so   we're going to continue to write some code just  for that so the first thing what I always want   to keep track of the profit and loss row so the  first row is going to be seven so we're going to   set that variable to 7. so let's go ahead and do  that now so back into the code here and we'll do   P and L row equals seven so it's going to set the  initial set that initial it's going to change Row   for P and L so now once we've set that initial row  what I want to do is I want to check to make sure   that we actually have income items right so if  not what we're going to do is we're going to skip   right we don't need to bring over any income items  if there are no any income items so how do we   know that so if the last income row is less than  three means we have no results right then go to no   income so we're going to we're just going to skip  a few lines so I'm going to put no income and so   what that's going to do is Skip what we're about  to do so I want to bring over the income items but   if there is no income items then we need to skip  them so first thing what I want to do is I want to   add that plus icon for all of the items right  so I'm going to focus on that profit and loss   what I want to do is remember you saw that plus  there that's going to allow us to expand it and   I want that plus to appear right here in column  C on the right side here I want to put that plus   but how many do we know how many do we need will  we need to know the total number of income right   we're going to add in so we know if we've got 5  4 four different income items we need to add in   four different pluses right here right so that's  what we're going to do inside the code right now   okay so we're going to say the profit and loss  okay profit loss dot range and we're going to   start out with column C so that column C and the  p l row we're starting out that p l row and okay   Colon c and what and the last and the p l row so  we're going to add in that P and L row we can also   add in the number of income rows plus the last  income row minus three minus three so we're going   to subtract 3 that's going to be because our first  one starts on Row three also so that's it and the   value of that is going to equal what it can equal  plus right so let's take a look at that plus okay   so that's we had to add in expander okay so we're  adding in those expander icons and let's update   this and one and sign in there okay so now that  we've got that what we're going to do is we want   it in the value so where are those values located  we're gonna they're gonna come from a j3 right we   want to add in those amounts so where do I wanna  put it I want to put in those accounts right here   so we've got the pluses in column C in column D I  want to put those accounts now those accounts are   going to come directly from AJ Okay so we're going  to add in that now all we need to do is just copy   and paste and then make the updates accordingly  so let's do that right here copying this right now   it's going to come focus on column d right this  is column D and what do we want to add in there   well that's going to come in equals dot range it's  going to come from a j that's we want aj3 through   a j and the last income row last income row that's  why it's so important dot value and I was going to   call this the income accounts income accounts okay  we also want to know the values right the values   are going to come from a k so what I'm going to  do is I'm going to copy this here I'm going to   paste it down here and those are going to come  of course from K right K of the next column over   that's where our amounts are coming from so these  are the income amounts right so we'll change this   to amounts amounts now the income amounts are  going to go where well I'd like to put those in   column H right column H is where we're going to  be putting them in right inside here all right I   want them to come right here so column H is we're  going to put in the amounts so let's go ahead and   update that code right there H here and H again  all right okay I got two workbooks open one's the   sample here so some see another coupon okay so  H is where we're going to have those inquiring   counts okay very good so that's very good that's  all we have and then no income here okay let's go   ahead and run that macro saving our work before  we run it placing it okay and we'll update that   that should be PL and then we'll do that PL and  then PL skip the word all right continuing on   here let's go ahead and see take a look at our  work inside here we see materials good we like   that and then the amounts coming inside column  H perfect okay and they're already formatted so   I like that there okay so what I want to do is  now we're going to put in the total income so   let's go ahead and add that in here so we need  that total income row okay so even if there's no   income we're going to put that total income row  so I'm going to go down here now is what I want   to do so I want to check that last thing I'm going  to see if we have uh income values nothing if the   last income row is greater than 2 meaning we're  having values then we need to update that p l or   Pinot row is equal to the piano row plus the last  income row minus two right I want to add basically   all the income rows right minus two okay this is  going to update the Prof p l row P L row okay so   now that we've updated the p l row I want to go  ahead and add in that total income label so it's   going to be the profit loss.range we're putting  that directly inside column c c is we're going   to take it and the p l row dot value equals total  income that's our label so total income so that   is ADD total income label okay so now we had to  have added that value where's that value again   it's going to go in column eight so I'm just  going to copy this and I'm going to change the   column associated with that it's the same row this  one's going to be column H now H is simply going   to be the value now where what is the value that  we're going to be adding we haven't named range   under that if you remember inside our transaction  we've already calculated that total income we have   it directly here so the total income we have a  named range called total income so I'm going to   copy that named range I'm going to go back inside  the code I'm going to use the brackets for that   I'm just going to paste in that bracket and that's  it that's all we need that's going to be the total   income let's go ahead and run that right we'll add  in the conditional formatting at the end right so   let's go ahead and put that in take a look at  that see what we've created here make sure that   we have the total income perfect that's everything  we need here remember conditional formatting we'll   be adding in later okay so now we've got the total  income now we're going to add in the cost of goods   sold so next up cost of goods sold let's go ahead  and add that in now all right let's go ahead and   increment the p l row P L row is equal to the  piano row plus one plus one so we increment it   right we want to go one row down P and L row  so now we can add the cost of goods label it   I want to add that label in so we're going to do  range actually let's go ahead and add that profit   and loss.range and then C is where we're going  to add it in C and the piano piano row dot value   is equal to cost of goods sold that's the label  for it okay again we want to increment the row   one more time right as we move down right we only  want the label on one okay so we're incrementing a   row one more update to add because goods sold now  I need to make sure we want this label regardless   if there are no cost of goods sold or not right  so we're going to go one row down but what I want   to do now is I want to add data but I only  want to add data if there have been cost of   goods sold so it's going to be very similar to  what we've done this so I'm going to copy this   just as we've done the income and I'm gonna go  down here I'm going to paste that in and we're   going to update it so this time we're going to  do if last call cost of goods roll is less than   three then we're going to go to no cogs then we're  going to skip down c-o-g-s okay I'm going to add   that label right in here notes cogs colon there so  now that we have that let's go ahead and put that   make that a smaller all right so I like that so  what I want to do is I want to make sure if there   is cost of goods sold we're going to add this  in if not it's going to skip directly to here   so the profit loss C and the p l row through last  and this time we're going to go for the last cost   of goods rule so cogs when models just added  in right here so we're going to copy that and   wherever it's used we're going to paste it in here  paste it in here and then we're going to update it   so we're going to make the updated here and then  we're going to add an expander icons for the cost   of goods sold again we're pasting that in here  last cuts of goods Rule and last cost of goods   row here again here as well and then we're going  to change it here the label these are going to   be the cost of goods accounts and we also want the  cost of goods amount now where are they located of   course they're not located in the same place those  amounts and accounts where are we pulling it from   well of course we're going to distract extract it  directly from here so our we've already covered   our income accounts our accounts are coming from  am and our amounts are coming from a n so we need   to make those updates accordingly so our amounts  are coming from from accounts coming from am and   our amounts are coming from am and then a n here  so our mounts coming from an very good I like that   now we've updated it so let's go ahead and take  a look at that we're going to run the macro again   saving our work before we run it running that  macro continuing on okay and again fixing that   that variable there p l row which is adding that  l in there okay continuing on all right again one   more time here as I did it twice copied and pasted  both all right updating the variable accordingly   and then go ahead and continue to run it I like  that let's take a look at the results down here   and we see that we've got the profit and loss and  then we've got a cost of goods sold here like that   we've got the amounts great now we're ready to  add in the total Rubble we only want to add in   the total row of course regardless if there's data  or not so let's go ahead and update that total   row right here so I'm going to update I'm gonna in  fact we're going to copy this just the way we have   it just like in fact I can copy all this because  we're going to use it again I'm going to paste it   down here so right here considering on so now we  just need to update this accordingly again cost of   goods sold right we're focused on that if the last  cost of goods Rose greater than two then we know   we have it then we're going to add in so we're  just going to copy this because we'll be using   it frequently updating our variables here the  last cost of goods roll we're updating it right   we need to increase the last the p l row based on  how many cost of goods there are there were okay   so now C is going to be the total again adding  that label in called cost of goods sold and we   want to know the total our variable is going to  be total cost of goods sold updating that this   is the total cost of goods label here and this is  the total cost of goods amount right we already   have that variable located that named range is  located again we're going to increment it here   and we're going to add in here so we don't this is  going to be expenses right the next thing but not   expensive before we want to know what do we want  to do we want to know the gross profit so let's   add in the gross profit okay great so we want to  add bet and I also want to add in the amount right   so what is the amount of that inside the next H is  going to take it actually let's go put the profit   in I so the next one is going to be I I am the  gross profit what is that value here it's it's   inside the brackets right brackets here gross  profit we're going to go over both of those and   see where I've got those from we know the total  cost of goods sold here and our gross profit   we're going to be putting in as we increment it  okay saving our work let's take a look at those   named ranges that we created previously just  so you can familiarize with them once again so   inside here the transaction we're pulling that  data directly from here the cost of goods sold   that total cost of goods sold and we're pulling  the gross profit here and I want those to appear   directly here if I now assign that macro let's  go ahead and assign that macro to the one that   we're working on click assign macro we only see a  few we're going to click this workbook that's the   only one profit and loss refresh is the one that  we're working on now we can click this button we   can see so now we have total cost of goods sold  18. that's correct and we have the gross profit   in column I at fifty two thousand eight fifty  four thousand two hundred and eighty okay great   I really like that that looks really good if we  want to check our work all we need to do is just   do this equals sum right just to make sure that we  have a thing everything Associated we want to sum   that on make sure that it's accurately correct so  we see eighteen thousand zero six one it's exactly   the way we have it that is our total we can do  the same thing for income so now we've got our   gross profit now what I want to do is I want to  add any expenses expenses is going to go directly   in column C so let's go ahead and we've already  added that in so continuing on we've incremented   the row ready for to add in those expenses and  we can do that now it's very very similar to   what we've done so again what we can do is we can  just copy and paste saving a whole lot of time   so continuing here we're going to add in here  right in here we're just going to copy all of   this these cost of goods sold and we're going to  update it for expenses so going down here and then   we're going to update this so I've pasted those  in there and we're going to update that after   gross profit we're incrementing a row and let's  go ahead and add in expenses and we have expense   so we're going to change that label here's the  label and column see expenses now we're going to   increment the total right and we want to know the  last expense row so we're going to expense row exp   that's our variable here I'm going to copy that  because we're going to be using it frequently so   we're going to go to no expenses right in case  there's no expenses I'm going to drop it down   here right in the end with and we're going to go  to no expect exp that's what we're going to skip   to if there's no expenses exp all right so now  that we have that so what I want to do is I want   to update that so C is where it's going to take  it right last expense row that's we're going to   focus on let's change those variables last expense  row and last expense row okay we're going to add   in the expander icons we want to do that where's  our expense accounts coming from right our expense   account expense expense accounts are coming  directly from let's take a look so we can see   and they're going to come directly from here right  here AP is our accounts AQ is our amount so that's   what we want to pull it in AP and AQ so pulling in  the data here AP is where our accounts are coming   from we're going to change that to AP and we got  to make sure that we update that to last expense   row right here as well the variables need to be  updated here and also here so we got that and our   formula total amounts are coming from AQ so we're  going to update that here so we've got the expense   accounts the expense amounts located there I like  that and then we have no expense and then what   we're going to do is we're going to add in the  label right we certainly need to know the label   that label is going to go directly inside column C  and then it's going to go after we update that but   we need to do is I want to make sure so we're  going to update this we're going to copy this   that's we're going to focus on here right after  we added the amounts then what we want to do is I   want to increment the p l row based on the expense  so I'm going to add change this to exp all right   I'm going to change this right let's go ahead  and update that right I want to increment the   row there so here's the last expense row update  the p l row right I want to add in the label for   the total right it's going to be total expenses  here so we're going to change this to expenses   and I also want to know the total expenses right  we've already covered expenses and we're going to   add in expenses label right so we can just copy  this right for the reward and we'd have to retype   it and I'm going to add it in directly in here for  our label and our note here so we're updating the   p l row we're adding in the total expense label  we're adding in the total expense amount right so   that's going to be the same and that's going to go  directly inside here okay great but actually let's   go ahead and put this up here so that means even  if there's no expenses even if there's no expenses   I still want to update the row but only if there's  expenses I still want to put in a total expense   I still want to put an amount even if there's  nothing because I want to show that zero okay   great saving our work so far right we're going to  go ahead and run the macro here let's take a look   and see what we have created here inside that and  we're going to pull it up right here okay bringing   it in okay so we have expenses we have all the  expenses here and we have our total expenses   awesome that looks really good let's go ahead  and double check our numbers we're going to do   equal sum just to make sure that we have it right  we're going to add up all these expenses and make   sure that we got the 39 974 perfect just the way I  like it great now all we need to do is add in our   net profit I'm going to skip a row and then we're  going to add in net profit right here and I want   that net profit to go directly in column I okay  good so let's take a look at that inside the code   and we're going to continue on with the code we're  going to start by incrementing the p l row so we   can copy that and we're going to increment it one  and then what we want to do is we want to update   we want to end the net profit label and we want to  add in the net profit amount so we can basically   just copy this here and then paste it right down  here and then make the updates card so this is   going to be net profit I should have added a space  in there net profit right this should be a space   here and I also want to add in the net profit this  is the named range I believe net profit is correct   and so we're going to add in that and then what  I want to do all right so let's take a look at   that then we'll run the macro take a quick look  at it I've already run the macro bringing it on   Okay so we've got net profit of 14 000 right our  net profit of course is our gross profit minus our   expenses so if we were to double check that we're  going to say equals right simply our gross profit   minus our total expenses and that's going to get  us our net profit so we enter that we see it's   14 306 that is the correct name perfect that's  exactly the way we want it everything's adding   up perfectly and now what we'll do is ready to  add in some conditional formatting right we have   everything here all right so let's take a look at  what kind of conditional formula we can bring this   up here for a little bit so we can see more data  okay the first thing what I'd like to do is every   row that includes total I want to give it like a  light brown look so what I'm going to do is I'm   going to highlight all this here and then what  I'm going to do let's say we can go to probably   here we don't need to know the totals right gross  profit and net profit will do something a little   bit different with that so I'm just going to go  all the way down right we could and we're going   to we're going to go into conditional formatting  home right and then what we'll do is we're going   to go to manage rules we're going to create a  brand new rule based on a formula so we're going   to do equals we're looking to find I'm using the  find command what am I looking for looking for the   word total so we're going to put that in quotes  total and where do we want to find it I want to   look for it in column C so we're going to use  column but I want to use every single row then   column C so I'm going to move that dollar sign no  absolute there I'm going to call them I'm looking   in the first character right and if that's going  to equal one then I know it's been found so we're   going to format that and what I'm going to do is  I'm going to use the fill effects that similar   color here we're going to use the light brown here  and then we're going to use the light brown here   also same color here clicking OK I also want it  bold so we're going to click bold and click OK   and then okay once we are and then we're going  to apply that and we see that that's now been   applied to ever everyone has totals perfect that's  just the way I want and I also want to know which   ones are profit I want to give that profit going  to be that brown dark brown for both of the ones   that include profit well profit you see is the  only ones that have gross profit and net profit   are the only ones that have values and call them  I so what I'm going to do is I'm just going to   Simply highlight those all the way up and we'll  go all the way down We'll add to that and then   what we're going to do is we're going to go into  conditional formatting and manage the rules right   and I also want to update this tomorrow so we'll  change that to 999 okay apply that okay now what I   want to do is I want to click a new rule I don't  use a form or determine and I want to make sure   it's going to be only focused on this right any  value with column I right column I does not equal   empty right so of course it's going to be for any  row so we're going to remove the dollar sign six   does not equal empty and then what we're going  to do is we're going to format that I'm going to   give it again another fill but this is going to  be that darker brown that darker brown similar   color that we've been using and I'm going to use  so now this is going to be of course the dark film   so I'm going to click OK and I want the font to be  white on this case so I'm going to change the font   to White and I also want it bold to clicking OK  all right and clicking OK remember this is only   going to apply click all right and then we want  to make sure to apply J and then we'll just use a   large row here 999 and then click apply and we see  that that is now automatically applied so we see   that we have perfect so we the rows that are are  half contained values in column I are highlighted   with that brown and that's exactly what I want  continuing on what I also want is I want to be   able to add in some conditional formatting I want  some lines to go above here right so you see some   lines right above here I want to add in those  for every value I want to put a line above it   so what we're going to do is we're just simply  going to highlight those here and I'm going to   go down here all the way down and then I'm going  to add in a brand new conditional formatting here   so home conditional formatting and then we'll  click on a new rule using a formula here and in   fact these cases I want to make sure that there's  a value in column H right we'll use H in this case   so we know because there and that's what I want  to add in that so using column H here for every   single row does not equal empty I want to give it  a border just above it so it does not equal empty   I'm going to format that let's bring that over  into view formatting it we'll use a border here   so border we'll sign it that color that's one of  the similar colors here and we'll give it a dotted   Top Line border clicking OK click OK all right  that looks nice I like the way that that looks   it's coming together now so we have our our p l  pretty much Associated here what a click refresh   is automatically going to refresh now of course  we want to print it why don't we add the Mac or to   print it and then what we're going to do is we're  going to show in that macros that automatically   expand and reduce it so let's get that print macro  done out of the way all right so going back in our   code here let's go into the right module here this  is the one I want print report is what we're going   to do so we're going to Dimension the print range  as a string okay that's again we need the range   because I need to basically print regardless of  how long it is so what I want to do is set the   print range the print range is going to equal to  the profit loss dot range I want to know the last   row so we're going to start out where are we going  to start it out well we'll probably started out   somewhere in let's say B5 right so B5 is going to  be that first row we don't want to print the top   ones B5 all the way through I and whatever the  last row is that last row is going to be based   on the last value here right here in column C  so to do that we can say B fives are starting   B5 and then what is it going to be through well  it is through column I and we need to determine   the last row and that last row is simply going to  be based on column C that's going to determine the   last row so what do I want from that well I want  that address from that okay so we're going to   click this dot address I want to know the address  of that is that address is going to be the range   address of print range okay so once we have that  print range what we can do is we can simply add it   let's go ahead and add that up in here and we need  an and that right because it's going to be the and   we want to add in that last row and meant Morgan  news we can set that print area so we're going to   do the profit loss Dot Page setup okay we're doing  the page setup then that page setup we're going to   set that print area and what's that print area is  going to be it's going to be equal to that print   range we just said print range once I have that  print rate all we need to do is simply print it   out so we're going to do again profit loss dot  print out and what are we printing out well I   want to print out to the default printer right so  we can do from one we don't need to do that just   comma two copies one copy preview I'll do false we  don't need to preview it active printer true we'll   set the active printer so it'll print it active  printer print to file no call it print the file   name ignore print area is false we certainly do  need it to print to using the print area great   so I like that print report that's going to be  set out saving their work make sure we assign   the macro to that now what we're going to do is  we're going to assign the Mac to this button that   we've created so we're going to click assign macro  and print report clicking OK and not my default   printer snagit so it's going to print it and then  we just need to update that to the set the sheet   name here there's no sheet name no width or profit  loss okay that's sufficient there and then we'll   continue on that and then we'll go okay so it's  going to print out to my default printer and we're   going to check it out all right let's take a look  okay that looks very nice it's encompassed all the   data we've got very looks very good got our full  profit and loss report okay so it's now printing   right now all we need to do last up is just add  these expanders when I click on this plus I wanted   to expand when I click on the minus I want it to  reduce just as we did in that sample so how are we   going to do that well that's going to be based on  the selection change event so let's go back into   the VBA we're going to focus on the page profit  and loss right we're going to focus on select   when I make a selection change that's what I want  something to happen Okay worksheet and selection   change before I forget there was one more thing  I wanted to do on change this is going to be   very very simple so basically when a user makes a  change to a date here right or a date here I want   this particular report to refresh so any change  on E3 or G3 we we want to refresh the report well   that's relatively simple so how are we going to  do that I'm just going to go inside this here I'm   going to copy that name here report refresh I'm  going to go into the worksheet change meaning when   a user makes a change to one of those two cells so  we're going to go back into the profit and this is   going to be worksheet change so let's focus on  when the user makes a change and the user makes   change to which cells we're going to focus on just  those two cells E3 or G3 E3 or G3 when they make a   change to either one of those cells what do I want  to do right I want to then run the report then run   that report right run report on date change okay  relatively simple so going back inside here now   when they make a change let's say they change  it to 4 31 right we want to refresh that report   okay obviously we need the date 4 31 there we  go that's the correct date making a change here   all we need to do is just change to April 30th  all right perfect I like that that's looking   really good now let's go ahead and update making  those changes on that so we can easily expand so   I'm now that's going to be on the selection change  right selection change we make a selection we want   something to happen well what are we going to do  well first thing what's the if not intersection C   we're going to make a selection change where where  are we making that selection change column C here   that's the only one so we're starting it out in C7  that's going to be the first one and it's going to   go all the way through along we'll just do C7  through c9c7 through C in the 999 use a large   number is nothing then what do we want to do then  something then and if I also want to make sure   that the target is either a plus or a minus if not  we don't want to do anything so if range C and the   target dot row dot value does not equal plus the  plus right we're only focused on the plus and and   range C in the Target row and the target dot row  dot value does not equal the minus right it's got   to be one of those two otherwise right then exit  the sub exits up only if the users clicked on one   of those the Plus or the minus so that's all  we're going to do there assuming that it does   we're going to Dimension some variables so we're  going to do Dimension the last data row as long in   the last data row I also want to know the last p l  row last P at L last pedal row as long we're going   to have an advanced filter so we need to know the  results of those so the last result row as long   and I also need to know the number of results how  many results are results result rows as long and   I also want to know what row has been selected so  selected row as long great once we have all those   variables then what I want to do is I wanted to  mention also the shrink rows how many rows are we   going to be shrinking you mentioned the shrink  right when we shrink and how many rows are we   going to be shrinking shrink rows as long as long  great all right so also if Target dot count large   is greater than one meaning the user has selected  more than one cell then we can exit the sub then   exit sub exit on selection of more than one cell  also if the target value is empty Target just in   case Target dot value equals empty then exit  so just to make sure that we have clicked on   something proper okay now that we want to I want  to know the last row of the p l right the last   row remember it's called C so the last PL row is  equal to column C right column C is going to be   the last right we don't need the dot here we're  focused on the sheet so we don't need that okay   and I also want to set the selected row variable  so the selected row is simply equal to the Target   or equal to the Target so we're going to get all  of this regardless of whether they selected A Plus   or a minus so now we're going to get into the  differentiation whether they select the Plus or   the minus if they select for the plus we need to  drill down and expand it if it's like the minus we   need to shrink it up so first of all we're going  to turn off the event so application dot enable   events equals false we're going to turn that  on make sure we turn it on and also application   dot screen updating equals false we're going to  turn those on before we get to the end no false   just in case let's do it right now application dot  enable events equals true turn that back on and   then also the same thing with the screen updating  so application dot screen updating equals true   okay so we're turning that back on so everything's  going to happen in between here so what do we want   to happen well first of all I need to know if  we're going to be expanding it so if range C and   the target dot row dot value equals the plus sign  plus then we know we're going to be expanding then   we're going to be expand let's complete that  else and if else we're going to shrink so for   the expand what are we going to do well the first  thing what I want to do is I want to determine we   need to run an advanced filter right for example  right if I want to know all the materials sold   I need to know all the materials sold within a  given date so we need to run an advanced filter   and we're going to do it inside here so we have  some drill down criteria here we already have our   transaction date here same thing greater than  equal to from date transaction date less than   or equal to Due Date all I need to do is put in  the account here so a u and three a u3 is simply   going to be equal to whatever's to the right which  is indeed materials sold if I click here I want   material stall to be put directly in au3 so let's  take care of that right now so we're going to do   transaction database dot range okay au3 dot value  is equal to what is going to be range simply D and   the selected row and the selected row dot value  set account criteria so once we know that said   the count criteria I also want to set that plus  and turn it into a minus so the target dot value   is equal to minus right let's change that to the  minus symbol so it's going to be our expand and   we'll just call it shrink call the shrink icon  shrink icon so once we set I also want to know   the last data Row the last data we're going to  be running an advanced filter so I need a last   data row it's going to be equal to the transaction  database right based on the last row so we'll set   that equal to transaction database it's the last  transaction or last transaction row if let's do   last result row last result row is less than three  then exit Sub in fact okay I like to put this just   in case there's no data I'm going to put this  right here that's good I only want to add that   shrink icon if there's actual data okay obviously  there should be data if we've got a value okay so   now that we know that we have data right we can  we've added the shrink icon we can continue on so   what I want to do is I want to run that advanced  filter so we're going to focus on this and I want   to add in that transaction database that's what  we're focused on transaction database range it's   going to be A2 all the way through e 2 through  e and where's that criteria coming from we've   already added in the dates we've already got  it dated and we've already added that account   criteria located in au3 so let's take a look at  that and set that criteria it's going to come   directly into this that criteria here is located  right here drill down criteria a S2 through a   u3 that is what we're going to as23 here so as2  through a u3 and where do we want those results to   come I want those results of that filter to come  directly in the drill down results here I want   to know the transaction date I want a description  I want to skip a column and I want to go through   a z now when we skip a common results we need to  make sure that our original data includes a blank   column so we should come all the way to F when  we do that all the way to F include that blank   because our results include a blank too so right  we want those results to come directly inside here   right so bringing it up here here is going to  be our results located in columns aw2 through a   z that's what we want the results to come so aw  let's go ahead and update that aw through a z w   through a z so now what I want to do is I want  to determine the last results row and if that   last results row is less than 3 of course we have  no data we want to make sure that we do have data   we should of course so here we go the last results  row I'm going to make sure it's going to be based   on column what is it called um it's not call them  a it's gonna be called aw it's gonna be our last   result so I want to make sure that we have it if  it's less than three we're going to exit the sub   so last results row if last results less than  three then we want to make sure that exit the   sub we'll change that to three and last result  rows less than three exit that in case we don't   have any results if we do have results I want to  bring over those results so how do we can do that   we're going to set the results row I want to know  the number of results so the result rows equal to   the last result row minus two minus two why is  that well because if we have results located   right here right if they're coming to 7 I want to  subtract two so I know that we only have five rows   I want to know the number of results because it is  those number of results that we're going to need   to expand on that so continuing on so we know the  last results so we've got that into a variable the   number of results so we want to know the number  of those results once we have that we can then   continue on and bring those results over so how  we can do that range I'm going to bring it over   see we'll put that results and I want to know  also the selected row selected row plus one right   it's not going to be that selector of plus one  that's the row below all the way through I I'm   going to copy those I'm going to bring down  right I need to bring down whatever is it I   and the last p l row I want to copy everything and  the last p l row dot copy so what am I doing here   let me show you example if I need to know that  I'm going to insert three rows here I need to   take let's just remove this dot reference we don't  need that that's not going to be helpful here and   any other instances of that because we're making  sure the last results are we need to add then   transaction database here that's the database for  focusing transaction database so now that we have   the transaction database located here I want to  make sure that we have it here as well transaction   database continuing on continuing on here that  looks good and also the same thing here I need   to add in that database we're focused on that also  here and the next one we also want to make sure   that it's also set up here inside the results here  because that's the one we're doing okay good so   that's just Auto hotkey continuing on all right  and we want to make sure that we have the last   results row that last result so is actually going  to be the last data row last data data row I like   that that looks good and then result rows we have  that very people should be last result row let's   take a look at the number of results we're going  to set that to result rows and we'll just update   the variable right here to result rows okay  so make sure we have the number of result rows   I'll add variables update all right it's looking  good so what I want to do is basically I want to   take all of whatever's below it all the way here  and bring it down I'm going to copy it bring it   all and bring it down the number of rows of the  results so that's what we're doing we're simply   copying and bringing it down however many rows  so the first thing what we want to do is we want   to copy and then what I want to do is I want  to paste the values I really only want to post   paste the values because I don't want to upset our  conditional formatting so how are we going to do   that well what we're going to do is going to do  range C and the selected row plus one or we need   to add one row one row down right and then we want  to know the number result so adding in the number   of results so plus the result rows I'm going to  paste that dot paste it's paste special only the   values that we're going to be concerned only those  values so let's go down here pasting those values   in right not upsetting the conditional formatting  once we've pasted the values what we want to do is   simply clear the contents of the existing cells  so we can do that with the next line of code so   I'm going to focus on that now what we want to do  is range again only C and the selected row plus   one right the row below is what we're going to be  select plus one all the way through I and colon   I and the selected row of course and the selected  row again plus the number of result rows plus the   result rows clearing those result rows dot clear  contents okay we'll go ahead and update that now   let's go take a look at that see what we have  saving our work let's take a look and see how   we're going so far all right so continuing on so  what I want to do is I want to click material safe   and so what I want to do is I want to clear the  rows out it brought down everything else and how   many rows did it clear out it cleared out let's  take a look you see all the way here six rows   so six rows is the number of rows that we add  going into our transactions and we see here our   results we have six result rows of results so it  is these results that we're going to be bringing   in so we've got materials sold here's the criteria  all the materials sold is here we've counted the   number of row sticks we've cleared out the number  of rows preparing to put in that data right here   so let's go ahead and write the next line of code  that we're going to be adding in the data we can   just click refresh report and everything's back  to normal okay so let's continue on inside the   code so we've already cleared that clearing the  contents out we need to bring the data over so we   we can do that so range we're going to put it in  column d d and the selected row selected row plus   one right not the exact row plus one the row below  right and all the way through G and okay through G   and the selected row plus results row and selected  row plus the results result rows dot value is   going to be equal what equals the transaction  database dot range where is it coming from column   aw if you remember aw3 all the way through a z and  the last results row and the last result row dot   value that's nice all right let's take a look at  that saving our work again okay going back in here   now we see that clicking on here okay that looks  really nice okay let's take a look at materials   okay so materials sold that's exactly what I want  the data here now we haven't added the string so   that's not working yet we're going to add that  in right now now what if I want to shrink the   data basically I want to determine the number of  rows that we're going through I want to clear the   contents and I want to take everything else and  bring it up the number of rows so let's write in   the code to shrink it and then we're going to add  in the conditional formatting then we're going to   be done okay great so we've got expanding done  that's expand now out of its strength if they   click on the minus what do we want to do well  first we need to determine the number of rows to   shrink so the shrink rows shrink rows is equal to  range I want to determine the cells right I want   to count the number how many what are we going  to do we're going to count I'm looking for the   number of values in a particular column right  what column we're going to be looking at I want   to know the target row plus the target column how  many values right what do I mean by that I want   to count I want to know how many rows until we  reach about if I've clicked on this column and   I know that there's it's going to be let's say  seven rows of data before I you know that's okay   before I reach the next I want to count the number  of that until we reach a value which is right here   so how are we going to do that well inside VBA we  can do it this way okay so range again let's do   that I want to count the number of blanks so it's  simply going to be cells then we're going to do   the target what is the target dot row well that's  the row and what is the column it's going to be   the target actually that's going to be the rows  plus one right and then row below I want to count   that it's not the same existing row target.com we  want to look in that column perfect and what do I   want to do what is the next part of that range  it's going to be all the way to the last let's   just do a large number cells here and let's just  do a larger 999 and what it also the target column   so this is our range dot column and what I want to  do I want to find I want to find what am I looking   for I'm looking for the next row with the value  so we're going to look for anything right so we're   going to use the star for that the asterisk there  and that's going to be our wild card and I'm going   to look in Excel values and I'm going to look for  Excel hole Excel hole okay and what do I want to   return I want to return the row I want to know the  row but not the row I want to know how exactly how   many rows so how are we going to find out well  that's simply the row minus the target row minus   one so it's the row minus the target dot row minus  one that's going to basically get us the number of   rows until the next value so that's going to get  us this is going to call the nut determine number   of rows to shrink so once we have the shrink rows  what I want to do is I want to take the expand   clear any expanded data so how do we know that  so range I want to clear that out all the data   that we entered must be cleared that detailed  data should be cleared out D and the selected   row plus one all the way remember we we added  in this data right now we need to clear it out   right so the D in the select are a plus one all  the way through G colon G and the selected row   and the selected row plus the number of shrink  rows shrink rows okay we're going to clear the   contents clear contents clear expanded data so  it's going to clear expanded data okay let's see   how that's Works see if we have any issues with  that okay saving our work and then we're going   to go in and take a look okay so what I want to  do is I want to clear that expanded data that's   exactly what I want notice it cleared all the  data exactly only on so it counted the number   of rows right from the target row minus one and  it cleared all the data out now all I need to do   is take the rest of the data and bring it up and  then clear out the remaining date at the bottom   so that's what we're going to do in the remaining  cells Okay so we've cleared out the remaining data   okay so now what we want to do is we want to copy  the remaining values below so range okay starting   with column c c of course that's where our data  is and the selector and the selected row plus   the number of shrink rows plus one plus shrink  rows plus one okay so so that's what I want to   do and right all the way here and all the way  through column I we want to get all the data   and copy it and the last p l row and the last p  l row we know the last one and I'm going to copy   that so we're going to copy that and then what do  I want to do I want to Simply paste it in the row   below so range C and the selected row plus 1 dot  paste special and then again only those values   right only values right we don't want to upset  conditional formatting so we're pasting only those   values so pasting the values in now what I want  to do is I want to clear the remaining rows at   the bottom right because there's duplicate rows at  the bottom that I need to clear out so to do that   what we're going to do is going to do range here's  C and the last p l row minus the number of shrink   rows right simply that's how we minus the shrink  row so we know how many we got duplicated plus one   and okay so we're going to continue on and then  I all the way through that's the column I control   let's see colon I and the last b l and the last  p l row dot clear con so we're just clearing out   those last duplicated items okay so clear out last  duplicated lines very good I like that let's take   a look at that all right continuing on now because  I know I just want to do one more application dot   Cut Copy mode equals false okay that's going to  remove those dancing ants okay not that one false   equals false very good okay saving our work as  we always do always a good idea let's refresh the   report and start over we're going to expand the  materials so let's say shrink rows let's fix that   variable continuing on and another one misspelling  these variables very easy to do for me at least   shrink rows that's the number of rows continuing  on okay it looks good all right so taking a look   inside our data here we've shrunken rows and  then we're going to continue all right I like   that and now let's do one more thing and let's  select E3 because I don't want those highlight   cells so just right here range E3 dot select  dot select okay so that's just gonna basically   so the range is not selected all right let's  take a look at that so we're going to refresh   the report here expand that I like that and  then reduce that that looks good expand that   and expand that that looks very good and then  shrinking that and then shrinking this okay it   looks like we need to add in we need to add the  plus symbol that's the only thing that we need   to do so let's add that in right continuing on  right shrink rows first thing we want to do is   change that so Target dot value equals the plus  that's the only thing we forgot there no worries   easy enough to add that in okay refresh the  report here shrink it out and then shrink it up   okay that looks really nice okay let's go ahead  and add some conditional formatting what I want to   do is I want a conditional format those cells that  I want to put alternating rows on that okay so   we're going to focus just on columns D through G  so I'm going to highlight those and let me go into   home and I'm going to do conditional formatting  and I'm going to do a new rule it's only going   to be when values are contained in column G  that's fine we can use that formula so we're   going to do equals right and then really focusing  on column G of course any row within that column   so it's going to be G stick starting at G6 any  row does not equal empty I want to color it but   I want to give it actually two conditions I want  it alternating so how do we do that we're going to   do and right that's going to be one condition the  other condition is going to be if it's a e odd or   even row so we can use mod for that so mod of  Row 2 equals zero well that's going to be for   even rows so what I'm going to do is I'm going to  end the parenthesis so it's going to be those two   conditions I'm going to copy that and I'm going  to get a give a format and then I'm going to give   it a fill fill effects I'm going to give it that  light brown color that we've been using and then I   also want to give it a little bit of a border on  that so we're going to click OK and then I want   to do maybe just the dark brown border on that  like let's say this border here just something   very light all the way around clicking ok ok so  that's great for even rows I'm going to click   OK and what about odd row so I'm going to click  on home conditional formatting and then new rule   that's fine and then I've copied that so I'm just  going to paste it in here I'm going to change this   to one so we're going to do a very similar step  this time I'm going to change this to White so   I want that fill white color I want those borders  to still to be that dark brown so we're going to   clear that and then use the dotted line and then  all the way around clicking OK and clicking OK   that's the way I like there we go so that looks  really nice so now when we see we've expanded it   we can get that nice conditional formatting here  that's going to show those alternating rows for   expanding it and that looks really good all right  I liked it it's created a great p l from scratch   as you've seen here in our drill down profit  and loss availability refresh report print the   report and we're going to be able to automatically  change that report based on those particular date   changes just as we've done thank you so much for  joining me if you do like to support this channel   I would appreciate it we have an incredible 250  template workbook that you're just going to love   at the low price go ahead and pick that up also  of course if you like we have a patreon channel   I'll be adding even more to this particular  training what would you like to see I'll be   adding a new training and an updated workbook  on our patreon account each and every training   thank you so much for your continuing support and  we'll see you next time thanks so much foreign

Channel: Excel For Freelancers

Views: 27,191

Keywords: Excel VBA, VBA In Excel, Excel Application, Excel Application Development, Excel Software, VBA in Excel, Free VBA Training, Free Excel Training, Free Excel Course, Free Excel Training Course, Excel Profit & loss, Profit & Loss Excel, Profit and Loss Statement, Profit & Loss Statement, Profit & Loss Report, Profit & Loss Drill Down, Drill Down Profit & Loss, Excel Profit & Loss Statement, Excel Profit & Loss Report, Profit & Loss Report In Excel, Drill Down Profit Loss Excel

Id: 3XDy2GhGLPQ

Length: 78min 2sec (4682 seconds)

Published: Tue Sep 20 2022