Users to be able to get the information they want extremely quickly all right so that's what we're doing and there's a great way that we've done it too so that it's extremely fast no matter how much data now let's go over how this is done okay well once again like I said we have three sets of data here we have a customer table we have a set of invoices and we have a set of payments okay and the important thing each of these tables has a unique identifier unique qualify.
Or number as you can see each customer has their own unique number okay so excuse me when we have customer numbers here each customer has their own unique number okay so that's very important no two customers can have the same number so you'll if you're deciding your table you want to make sure that the user doesn't have ability to edit or change this or to duplicate additional customer numbers in invoice numbers as you just saw each invoice number is also unique as well and that's.
What identifies each specific invoice you do not want to have more than one invoice number and on payments all we do is we simply track those customer numbers and then we track the invoices so we know which one to apply those invoices to so here's basically a list of payments okay and then each one of those payments is tied to a specific invoice number so that's gonna help us track and that's how we formed our data okay and now what we've done is the idea is when we expand when we click on the.
Plus here it shows all the invoice numbers the invoice dates the amounts and the total payments keep in mind that these values are not accurate it's just test data so they don't add up okay don't worry about that I was just playing with it but I'm sure in your tables they were add up they will add up properly all right and so the idea is when we click on when we expand an invoice I want to know the payments that make up that invoice okay so generally you will have your payment amounts all match the total payments but we're just demonstrating the ability to.
Expand ok so how do we do that okay well let's start out with some conditional formatting all right I've got multiple forms of conditional formatting here and that really helps us quickly color things we don't want to use Excel VBA to color if we can help it because that's a little bit slower conditional formatting is extremely fast and I've been able to do that with one hidden row and let's go ahead and unhide column Oh okaycome ho so will unhide that and as you see here I've got little notations little letters.
Here that tell me what type of data is in this row C is for customer okay IH is for invoice header I is for invoice P H is payment header P is for payment and so on so because we're able to uniquely identify each row we can then create conditional formatting based on those values so let's go ahead and look at it I'll just click on one cell here go to home conditional formatting let's go ahead and I take a.
Look at the rules and we've got a set of rules we've got about six rules or so for each one we'll start at the beginning and this one this particular rule is for our main table okay it's for all these blue and whites down to for this to be accurate we want to have two things actually we want to be able to let's see this should be equal to C let me fix that a little quick all right because I want you to be able to see what that is okay so what this is is the.
Ability says if it's a customer see and if it's an odd row okay this is odd rows what this mod row beginning and end parentheses to tells you every other row every other row color it light blue okay so that's what this is alright it worked all also on m5 but but we want to make sure and this m5 remember it's very important that there's no dollar sign before the five that means that it's going to be able to use for every row okay if we put a dollar sign here it's not gonna work okay and then also the.
Important thing is remember that five okay our starting row also must be five so when we have five and are starting row that means it'll work for every single row okay and so that's what we get that's what we get our main coloring for the main table okay and next up let's go with invoice okay we have an invoice header here okay and this is a very simple rule and all we're saying is that let's go ahead and move it over here dead if it's ih invoice header I'll want.
To apply the following colors now let's go look at that conditional formatting see what type of we have apply well one I've changed the border color okay - I've changed the font color and I've changed the fill color so we're gonna apply those to three different settings so they have a different color okay and let's go ahead and take a look at the invoice okay those are just four invoice items and again if M five remember no.
RELATED TOPICS:
Dollar sign off before the five that's critical that that's why we can use one rule that applies to the entire table if it's
Invoice I want to color the cells what's that like a light blue or turquoise right I'm gonna put a border around it which is the same color we're using and there's no change to the font okay so that helps us colors and when we look at the when we look at the rule it applies to we're not applying this to the whole table we're applying it to columns G.Right we're starting at G okay so you see G here from to the end till L okay that's gonna cover us for the whole table okay for the for the whole area that we want the invoice just for the invoice section not the whole table whereas in this particular rule we did cover the whole tail because that's for all the invoices and basically the same idea for payments we've gone ahead and you know any the header row that has pH we've gone ahead and changed that color and also for the payment in the.
Individual payments we've gone ahead and given that a light and we've covered only I write high through K high through K just these three just these three columns here four payments so that's how we're able to get the colors all right and now let's go ahead and vbn get under the hood and see how this thing really works all right to get into the VBA editor if you have a developer's tab open you can go here to the visual basic alt F 11 is this sort is your shortcut if you do not have the developers tab into file and options also under the.
Customize ribbon you can see the developers here so we'll want to check that okay so we'll go ahead and alt f11 that'll bring us into the developers tab and first up we have our customer Sheik here okay in this customer sheet we have what we want to do is you want to create macros based on the selection change in this customer sheet this is the only sheet that we have the macros running and the important thing here is selection change okay because what happens is when we select something.
Right it's not when we change something it's when we select something we want some action okay so that's selection change okay and what is the range well the range is this G column here okay G and I've and I've put so if not intersect target range g5 through G 100 now this 100 you can you can make it 1000 you can make it 10,000 so you can change this depending upon your data or you can use the entire column g2 if you want I just kept it at.
A hundred for now so we're saying is that if we make any kind of a change in this then I want something to run okay if we make any kind of selection now the reason we have if target you're gonna see this a lot in when I do programming because when if without this watch let me show you what happens without it without this okay I'm gonna comment that out now when we select let me pull this up when we select a large range we're gonna get a bug okay it's just and that's what happens when we do a target when we do a.
Selection changes and we highlight a lot of rows we're gonna get a bug okay so and it's gonna say hey something so what we're saying here is if the count if the count is more than one if you selected more than one cell then just exit this up so that's why we have that there so now with that in right and you select more than one row and then you don't get a bug so that's the reason we have that there so it's very helpful you'll want to use that also in most of your code when you do selection change or value.
Change you'll want to have target count but keep in mind that if you're selecting a merged cell it might cause problems because merge cells often contain you know more than one cell so so if you're if you're using merge cells you may not want to use this okay so that's why I have that rule and basically the next area is what I'm saying is if the target value equals this plus sign and M and the target rope equals C then show the invoices so what that means is if it's a plus right if.
We're at a plus here and this hidden column is a see that means we know that means in that case it's a customer oh it's not an invoice room okay so what do we want when that happens well when that happens I want to run a macro at a macro called show invoices and we'll go over that in a minute okay if it's the minus sign right if it's the minus sign and it's also customer then I want to hide the invoices okay if it's a minus sign like this I want to hide them okay or like this item okay so if it's a minus sign and a see I want to hide okay.
If it's and then the next two parts of the code are basically saying if it's a
If it's a minus sign and an in and an invoice I want to hide the payments okay so if you look on the code here the next the next section is if it's a plus and an I show the invoices if it's a minus this should be hide so either you update that that we're gonna be hiding invoice let me update these are comments so you'll help show you what we're doing so.I want to make sure we accurately so we're hiding the invoices and that these are payments not invoices so let me update that code for you as well so that when you get the workbook you know everything that's going on here okay so we're hiding I've also made the macro names very clear to show payments hide payments okay so you've got lots of commenting so you know what's going on so that's all that they're all that's all the code that runs on this page okay let's go.
Ahead and look at the invoice macros okay and we'll go over this line by line so you know what's going on but basically here on the invoices here's the idea what I want to do is I want to want an advanced filter and I didn't know what advanced filters were the first few years and cough thank God I learned that it's one of the if you don't know what an advanced filter is it's one of the best things that you can learn and I'm going to teach that to you here today and it's fast it works with tons of data it's very easy to use it's very easy to manipulate and it's really.
It's really great thing moving forward you're using all your applications and basically the idea is here is I want to I want to search all of the info and I want to search for let's just say we're working on customer for okay so what we're gonna do is we're gonna take that customer number and I want to know all the invoices for this customer okay so in the advanced filter what I've done is saying I want to filter all these but I don't want to filter it in the place I want to filter it here an advanced filter the ability is here under data.
Advanced okay and we're going to use VBA but if you were doing it manually you would use it here what we're gonna do is we're gonna copy it to another location okay and so first of all what is the data okay here's the data the original data III through K okay the only difference is in ours we don't want 48 because we don't know what the last row is in VBA so we're gonna actually use a variable for that okay and the criteria means it means is here a1 through AG okay so the criteria means.
What should we filter this by right and then copy to range bool I want to copy the data where do I want to copy it I wanna I don't want to copy it in place okay I want to copy two different locations but we can work with it once it's in a different location and you can see here we filtered this customer number four and two listed only you know you cancel that up and we listed only the customers number four and so that filtered our data so advanced works really good we're gonna do all this through VBA but you can see what's going.
On here and we did the exact same thing for payments if we scroll over except we use invoice number so I want to know all of the payments for this specific invoice number so we ran an advanced filter now when running advanced filters there's one very important rule okay and that's these headers name names must match these headers if there's a slight difference it's gonna throw an error okay so what I'll do is always you know copy this and then I'll paste here and I'll hold it ctrl and then I'll paste the values okay I want to make 100% sure.
And that these values are the same as the headers those headers values must be the same that's the really important rule so you'll want to take care of that okay so that's what's going on so basically the idea is when I wanna I want to know all the invoices for this customer I want to know how many and I want the data so I want also when we filter it we use number four here now we know we've got all those and so through VBA we'll do that all right so let's go ahead back into the VBA and I'll show you what's going on here under the invoices we've dimensioned a few variables.
Customer number and invoice number is string okay and the invoice quantity that's gonna be the number of invoices cuz we need to count we need to know how many rows to expand under that plus one for the header so that's very important to know how many ok we need to know the last invoice row when we run our advanced filter we need to know the last row of our invoices okay so that's very important also we need to know the active row and the active row is whatever row we have selected okay we need to know what that row is in this.
Case is 13 but we need to we need to put that into a variable so that we can use that through our code so we need to know what that active row is okay so in our code here the first thing I've done is I've gone with sheet one most of our work is going to be here in sheet 1 which is customers so we've we've used width and width so that we can no longer need to defer to that sheet or refer to that sheet here so we've done which you want and the active row basically is saying where it's the active cell row.
You could theoretically use active cell row throughout the code as well but we've shortened it up just as a variable under active row okay first of all we want to know what the customer number is okay so the customer number is e and the active row that'll get us a customer remember our customer know it's e right so if we've selected if I've selected G right if I selected here I know the customer number is 1 e 1 right because that's the selected row okay so II 5 is customer number 1 so we know so we have.
To get that customer number which is here all right now the last invoice row we need to know what the last impress or if we're gonna run a filter right and we're gonna go back into the invoices if we're gonna run a filter on all of these invoices we need to know what what's the last row in our case it's 48 okay but we need to know that through VBA so this line of code will tell us the last row of invoices sheet to 999 you can change this number to I think the maximum is.
265 that'll I don't know I don't know what the maximum is but this covers us okay so this is going to tell us the last row of the invoice this is important because we don't want to run an advanced filter on all of you know the possible rows we just want to run it only on the data we have and that helps speed things up okay next is what I want to do is I want to clear out any possible previous filters okay so what I've done here is we're just clearing some contents here a two and a fourth a.
A four through a G 99 all of clear data and I'll show you what that is basically what I'm gonna do is I'm gonna say you know what I want to delete this I want to clear I want to clear this any any possible customer number before and I want to clear all this out I don't want to get any confused with any possible previous filter so make sure that before we run a new filter we've clear cleared all the data out from a previous filter that's really important okay so that's what this line of does clears any.
Previous data next up for our advanced filter we want to put that customer number in a a to write we want to put that customer number right here okay because that's going to tell us what to filter the data bottom alright so in our next line of code is the advanced filter okay an advanced filter is just one line of code alright and it's the same thing remember it's this it's doing the same thing as this data advanced filter it's doing all.
Of this work okay it's doing that exact same thing except in VBA so under advanced filter alright first of all we need to know what the data is we're filtering okay and remember this is all of the invoices all of the invoices okay so III to K and the last invoice row all of our all of our all of our invoices here III include the header make sure you include the header III to K and then whatever the last row is okay so that's the filter that we're going to be.
Working through okay and the criteria means well you want to filter what is the filtering okay so the criteria is all this right and if you wanted a specific customer number and a specific invoice number you could add theoretically to different criteria shares we're only using one but our criteria is here a 1 a 1 through a g-tube that sets our criteria so that means it knows it's going to filter anything we've already put the customer number in there so we're gonna know what to filter it by all right so the.
Criteria a 1 through AG 2 and now it says we'll wait once this is filtered where do you wanna where do you want to copy this range to okay we want to copy it to a a a a 3 to a g3 and all saying is we want to copy it right here including the headers always including the headers a a 3 through a G so that's all we're gonna do it we're copying it there okay and the last one is is unique are these records do you want just the.
Unique ones and that is correct true so that's what that does so what that does is it's gonna put all that data here so for example if we click on customer right if we click on customer number two and now we see three invoices let's go back okay now you see the code ran customer to customer to right and it puts all informations it's just amazing once you learn how to do this it opened up so many possibilities for me so.
Advanced filter is critical so that's all we did now what we need to do is okay we have all of our data but now what I want to what I want to do is I want to basically say I want to get this data here I want the invoice number I want the invoice date the amounts I want to put that all I want to put it right here okay okay I want to put that right there so I want to put these three information and I want to stick it right here right here okay that's what I want to do so what we have to do is we need to know first of all we need to know how many rows of data how many how many.
Filters so we need the last filter row okay what we need to know is what's the last row in our case this last row is 6 right so we need to find out because we need to know how much data to transfer over we know the starting rows 4 right but we don't know the end grow or VBA doesn't know so the last row is simply sheet to a a we're gonna use this column a a a ok just make sure you use columns that always have data in there a a and then.
It'll give us the last row so this tells us the last filter row and in if there's a case where the last filter row is less than 4 that means we have no invoices ok so what's gonna do is we said okay if there's no invoices we're gonna skip all the way down here and we're gonna put a little message box saying there's no invoices for this customer ok so what we do is normal normally we're gonna exit sub but this tells us to message just give the user message by saying there's.
No invoices we don't want to run through all this if there's no invoices so what we this tells us to skip all the other stuff ok so we know the last filter row in this case is 6 okay so the invoice quantity the number of invoices equals the last filter row minus 3 okay so if this is 6 minus 3 that means we have 3 invoices 3 invoice so we know we have the invoice quantities 3 okay now the next line the active cell we need to.
Change it to negative that means back in the customer sheet we need to change this value here if I click on this going to go back to plus just to the right we need to change it from a plus to a minus because it's now expanded ok so that's all that line of code does it just changes it to a minus okay now what we need to do is we need to insert the number of rows basically what we want to do is we want to enter the number of invoices plus one extra one for the header ok we want to enter the header first so we know what it is so all saying is the invoice quantity.
Plus 1 insert down that's what this line of cuz inserts the number of rows and I'll just put inserts number of rows plus one for a header okay and so that's what this does now the next is we need to get the header in here alright but but we don't have the header right where's the header what I've done is I've just put in some simple labels on the right if you'll see.
Here's our invoice header all I've done is taken this information just put it here and you'll want to hide this right in your own thing in your own workbook you'll hide it okay put it somewhere where you know anywhere the old my suggestion is don't put it low if you put it down here and you're inserting the rows if you put it down here and you start inserting rows it's not going to be row you know 11 or 12 because as you insert rows so make sure you put it above Row 5 or put it on another sheet is even better okay you can put it on another sheet that's okay just make sure.
It's on row 5 or above because as we expand these rows that road numbers change and we need to know what row these headers are on okay all right so make sure you didn't do that I did it yep that's why I learned so you'll want to go to where were we oh so we insert the header so all we're doing is saying they H and the active row plus 1 right active row plus 1 that's the row we want into the header and where's the header.
It's u3 through z3 okay our headers on u3 through z3 okay so all we're doing is copying those values remember we don't want to copy the format so the formatting is already taken care of through our conditional formatting so all we're doing is pasting the values as soon as the values get placed and as soon as we place that I for invoice right and as soon as we we place that it's gonna take care of itself so we.
Don't have to worry about that and as you can see I've included the header item IH right that's that's gonna help us determine I've included those headers here so when we paste it in automatically it's pasted here and then our conditional formatting is going to be automatic all right and next up on the line of code we have the ability we wanted to so we're gonna add in the header and once we add in the header it's necessarily gonna be justified right it's gonna be based on.