Excel Magic Trick 823: Conditional Formatting Row, Column, Intersecting and Specific Table 2 Methods

welcome to Excel magic trick number 823 if you want to download this workbook excel magic trick 823 to 825 and follow along click on the link below the video in this video this is what we want to be able to do we want to be able to type any number right here and have a gross amount and allowances and look it up but we want to do conditional formatting to highlight the row the column and the intersecting value now I have done other videos on this and there's some down here 296 is for an exact match either highlighting the role of the column and 787 shows approximate match we're gonna do both of those but here's what's different about this video if I this is the single weekly table if I change this to married I want the conditional formatting to jump over here right if I change this to single monthly I want the conditional formatting to jump here and finally if I change if I change married monthly I want the conditional formatting to jump over here now we're gonna go do this on the sheet 823 but before we do all of our conditional formatting here and then do it to multiple tables I want to go to the sheet TF formulas for true/false funds because here's the deal how does conditional formatting know how to work every single cell here has a formula that either comes out true and false all of these yellow cells got true this is the right column all of the orange cells got true in it from a formula that said this is the right row and then that value said not only is this the right row but it's the right column the intersecting value so I'm gonna look at on the sheet TF form is I want to look at the true/false formulas or some of them the important parts of it we're going to use now here's our gross pay and I have just a little table now again what is if we say gross pay of 211 that's going to determine the row so we're gonna say we're gonna have to look this value up look through the first column here and get our matching value now if you look this up the way lookup works when you're doing a proc that match these values are sorted from smallest to biggest it takes the lookup or vlookup or match or any of those function take this into the formulas brain it comes racing down here when it bumps into the first record that's bigger of it bigger than that it jumps back and that's what's gonna determine the row this one we're just gonna say hey is anything in this range equal to this it'll say yes and so then we need before matting applied here but here's how it works each cell in a range that gets conditional formatting it's gonna have the formula stored in memory and it's getting in it true and false so if this column is to this row is to be formatted we need four truths and the rest have to be false all right so how are we gonna do that we are gonna start our formula here and build the formula from the point of view of this cell and copy it over and down here we're doing it in the cells just to see how the process works but later when I go over to the souther sheet we have to do it in the conditional formatting dialog box well this cell right here we're gonna have to ask the true/false question is this row header equal to well it can't be that actually what we're gonna do is we're going to take this value and look it up in this column the lookup will return the row header and our formula will say is this row header equal to the result of the lookup function now let's go ahead and try this equals and remember this is as if I was putting it here or as if ultimately it's in the conditional formatting dialog box I'm gonna say this cell right here now remember we're gonna have to copy this over all of the cells here or here are gonna have to look at this row header so I need to cut the column reference locked but not the row reference when the true/false formula copies down that dancing ants needs to drop down to 210 so I'm gonna hit the f4 key three times and lock the D but not the four and then I have to say hey is that equal to this is where the lookup comes in and it's an approximate lookup I need to say hey look up look up this it'll race down here it'll find the first bigger one which in this case will be the 220 it'll jump back here and it would actually return this value to the formula you could see for this gross pay 210 is the indicator that this is a row so for this example and this row of course it would return false now I have the choice of either using vlookup or lookup function now what's great about the lookup function is that it only does approximate match and it will require few or arguments so I'm going to use the lookup function that cell reference needs to be locked in all directions so I'm going to hit the f4 key comma and then the lookup vector that's this right here f4 now the lookup vector just means it's going to look that up but since there's only one column it will return the item from that so in essence it's looking up to 11 it's going to return to 10 again the algorithm the way it calculates is different than the way we think of it metaphorically lookup that value bump into the first one bigger jump back a row let's prove to ourselves that this lookup is returning that 210 I'm going to hit f4 sorry f9 the evaluate key and what would that return is 200 equal to 210 false ctrl Z ctrl enter I don't think I have the columns whine enough false but now I'm going to copy it over and down alright so there we get our truths and you can see sure enough that d5 which is the row header for this row here is we're asking a question is 210 equal to the lookup value from 211 and it is true so that'll be the the true/false formula for highlighting the row now let's do the column this one's easy I'm going to say is the column header and I need to lock it f4 twice lock it so it's locked going down but not to the side is that equal to that f4 that's it control-enter copy it over and down now this is a an exact right so we don't have to do any lookup we just use the comparative operator equal and say are they exactly equal but here we had to get a little tricky right now if let's just go ahead and see if this works here I'm going to copy this in edit mode the uppermost left one ctrl C escape I'm gonna highlight here conditional formatting in two thousand seven and ten is home styles conditional something like new rules I'm gonna use alt OD that works in all versions click new rule click formula click here control V and then format it I'm gonna say orange for the row look okay all right now I'm going to copy the uppermost one here control C escape Aalto D new rule click here control V yellow all right so that's working now what about the intersecting value by the way why is the yellow on top of the orange alt OD alt OD because the yellow ones on top if you wanted the orange one on top you'd move it but we really want one further condition and I'm going to go ahead and copy just this part of it Oh what what do we have to do to get this intersecting value both of these have to be true so I'm going to highlight and use the and function fault OD the new rule formula that's going to be in the dialog boxes will be practiced for our next sheet and function because both things have to be true control V that's one logical test comma and then the other comp logical test will be that one f4 to lock the row reference but not the column equals and watch this when I click on that too by default it comes locked close parentheses those two logical tests when they're both true only at the intersecting point then we'll apply our format alright so there we go if we change this to two twenty nine to twenty nine so it moves if I change this to three now let's go over to this sheet because that's the basics of how to do this now we're gonna have to do it take a ticket a bunch of steps further I'm gonna build the first three formulas alright and we're gonna have and we'll just see how that works and then we have to deal with the fact that I want the different tables to also get formatting depending on what I did here now the trick here is going to be this I have single here and actually did data validation list based on well based on these here and this one here that asurs that weekly monthly single and married are all spelled correctly then I put a word at the top single weekly with no space so that's gonna be the trigger that says this is the right table when I select from here but if I select Mary I want the conditional formatting so married weekly I want the conditional formatting to jump to here so the key is I have the these words here without any spaces down here it's married monthly and single monthly alright alright I'm gonna highlight this I'm going to build all these formulas in the conditional formatting dialog box now when you're doing this that active cell on the upper corner is the one where you you in the dialog box you're going to build a formula from that point of view it's as if we're start we're in memory we're going to put a true/false form in here and and the dialog box will copy it over and down alt OD new rule all right I'm gonna start with the lookup so equals this is the active cell so I'm clicking there and I lock it f4 column reference but not row is that equal to look up look up of what this notice by default it's locked in all direction which is we want we want comma and then this column by default it's locked now that will highlight the whole row but what do we need we need one other condition we need this formula to be looking there and there so I'm going to do an and AMD so I have one that has to be true and it has to be the right table so I'm just gonna say that ampersand that that joins them together now this part of the dialog box of the formula will have married weekly is equal to this and those are all absolute in all directions close parenthesis so that's one I'm gonna click format and I think I'll do orange I think that's what I put for the row orange and yellow make red that's why I use orange one way yellow the other way and then red as the intersecting let's just see if this works it's working perfectly but when I change this to single there we go all right now alt o D actually alt o D let's build our next role let me just build it right off the bat equals and open parenthesis and the first condition is is that ampersand that shipped seven this is that equal to this cell up here all absolute cell references come because every single cell needs to say is am I in the right table then I go the allowances locked an old direction is that equal to that first cell for allowances but this one needs to be locked row reference but not the column close parenthesis and I'm going to format it yellow look ok click ok so now we have those two right there and so if we change this to 2 21 and 4 it looks like it works just fine let's do this over here again one last condition Oh the intersecting value and guess what there's gonna be three things in our and it has to be the right call on the right row and it has to be the right table also D double click this orange one and steal the lookup out of here that way copy actually I can steal I'm going to steal this whole thing the reason why is because we need both of these conditions and we only have that I have to add one more control C okay new rule use formula control V and the only other condition I'm going to come to the end and type comma this allowance Lock by default is equal to that cell right there and I'm going to hit the f4 key and lock it going down but not to the side and this will be format however you want like ok click ok click ok so now if I uh equals and I have the max and min down here so now if I start hitting the f9 key I have two randomizing formulas here f9 oh look at that that is just amazing but now we have to go do this to the other tables now you're gonna have to just brute force this and redo it for each table but I do want to show you a second way of doing this so I'm gonna come over maybe hide these columns because here we did when if I highlight this in all tody one two three each one of these though needed the question am I in the right table well we can do these without that and add a fourth one now the fourth one I spend so long since I used 2003 and earlier I think there's only three conditions allowed in 2008 so this would be the way in earlier versions but I'm gonna show you if we were allowed a fourth condition I'm going to right-click hide let's see what happens here alt code E and I'm going to do my three rules in super fast speed mode allowances equal to the column header up there and lock the row reference only and you'll add some yellow for that and then the new rule will do the lookup part of it equals Oh active cell there so I'm clicking there I'm going to lock the column but not the row is that equal to look up look up of what this gamma in this column here and we'll color that orange finally we need the intersecting value I'm going to say a new rule equals a end and this one does need the to the intersection doesn't need the two so I have a little lookup comma and then we have our allowance is that equal to this column header and I'm gonna lock just the row part of it and then finally we're going to do one other now the order of this does matter here we want the red one applied on top on top of the red and yellow so the next one the final one I'm going to say new rule use formula and get this I'm going to go down to format box and because those other three conditions do not have any trigger that says am I on the right table they'll be applied but I'm gonna add a one on top that uh applies them it doesn't really uh apply them I'm just gonna apply no Phil and normal font color so I'm going to say new rule and the trick is I want to say is this cell right here absolute in all direction is it not equal to that one ampersand this one and if that's true then format I'm gonna say for font automatic and fulfill no color click OK click OK click OK now I'm going to unhide these right-click unhide and let's see if it works okay I'm going to change this to married weekly oh look at that then I'm gonna hit the f9 key oh that's so amazing I'm gonna go back over here to single I'm hitting the f9 key so it's looking like this work now I already applied these to man and you know the same thing as I did up here let's see if I go down to married monthly so it looks like it's working there and finally about single all right so the idea was we had to create some an intersecting conditional formatting for the red we had to do the row we had to do the column for the row we did a proximate match for the column we did an exact match did intersecting but for multiple tables we had to think about how to tell a conditional formatting to say I'm in the right table in the way we did it is we had these names here and a little drop down now so that's conditional formatting for multiple tables in our next video we're gonna take this to its logical conclusion right each time we have a value we actually want to return this to a cell so if I hit f9 you see it's returning the right amount and when we change our drop-down right we want the formula to correctly choose the right table so it'll be a two-way lookup from multiple tables and in the next video that'll be 824 we'll see how to do it multiple ways including if the tables are on different sheets or the same sheets alright see you next video

Channel: ExcelIsFun

Views: 16,251

Rating: 5 out of 5

Keywords: Excel, 2007, 2010, excelisfun, Mike, Gel, Girvin, Highline, Community, College, Slaying, Dragons, Conditional, Formatting, Row, Column, Intersecting, Value, Specific, Table, Approximate, Match, and, the, LOOKUP, function, Exact, equal, sign, comparative, operator, format, correct, table, based, on, selection, from, drop-down, in, cell, Payroll, Wage, Bracket, Method, for, Federal, Withholding, Tax, conditions, into, dialog, box, AND

Id: FuPdQvOFZkw

Length: 18min 57sec (1137 seconds)

Published: Sun Nov 13 2011