Manfred from Germany has an interesting problem. Each record takes up six rows in his spreadsheet. Formulas need to point to the first row of the company. Those formulas need to be copyable. But those formulas are in conditonal formatting rules. Episode 1075 tries to solve this problem.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Bill: Hey, I'm Bill Jelen from MrExcel.com, and I've got a cool Excel tip for you today!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey, it’s another Friday, that means it’s another Dueling Excel podcast! I’m Bill Jelen from MrExcel, joining with me Mike Girvin from ExcelIsFun at YouTube. Question sent in by Manfred in Germany, Manfred sent in a great question, and I actually have no idea where this podcast is about to go. I’m going to paint a picture here, Manfred has data where each company takes up several rows.
OK, and normally, if we have to build a formula that relied on this cell within the company, we would, you know, build the formula using $! So I want to point to that cell, press the F4 key, times this value over here in column A and press the F4 key 3 times to lock the column, and then divide it by this number up here, press F4 twice to lock the row. And we get a formula that’s going to point to, you know, the first column, the first row, and cell B2 of that company.
But now the problem is, if we had to copy that formula down here, because those $ are hardcoded into pointing to, for example, B2, it’s going to be the wrong formula. Alright, so I have a solution for this, I can build those formulas as normal, and then use Edit, Replace, and say that I want to replace every $ with nothing, make sure I go to Options that I look in Formulas, and Replace All.
And now I essentially have relative references, but that always point to B2, for example, and I can now copy those formulas down into the other companies, and things are going to work great! Alright, but that’s not Manfred’s problem! I’m going to go on the second sheet here.
The problem is that Manfred is using Conditional Formatting. He has conditional formatting that says “Hey, if that cell contains a 5, it’s yellow, if it doesn’t contain a 5, it’s not!” Alright and, so we go and look at that conditional formatting here, come down to Manage Rules, and edit that rule, you see that we’re hardcoding $ in the conditional formatting formula. Ahhh… now I’m lost… I don’t know how I can globally replace all of those $ in the conditional formatting formulas. Right now if we would copy formats, Ctrl+C, Edit Paste Special Formats, ah, it’s not working. If this is a 5 or this is a 3, it’s not looking at it, it’s looking at this one back up here, it’s changing all of the companies when that first one is a 5. That’s not what Manfred wants to do!
Alright, so I’m going to throw this one over the wall to Mike, and I’m going to sit here and try and puzzle that out myself, let’s see what Mike comes up with. Usually, when I throw it over to Mike, I know where he’s going, I will just be shocked and amazed with you when we come back, and hopefully Mike will have a solution!
Mike: Thanks MrExcel! Shocked and amazed?
You can’t be shocked and amazed if I’m going to some formula here, I learned most of the tricks from the books YOU wrote, and your message board. If you want to be shocked and amazed, then go to the message board and see some of the responses that Aladdin or Houdini or Don Quixote and ALL the other Excellers do over there. Alright! So there it is, we’re going to do some Conditional Formatting, and whatever we put in whatever one of these boxes, is to trigger the conditional formatting.
Now, as I think of this, if I’m going to start, for instance here, and tell all of these cells to look right there, well right here, if I’m looking up there at that cell, I could say “If I look at that cell, I’m 0 away.” When I go down 1, if I was looking at that cell and it moves down as a relative cell reference, how many do I need to move to get back up there? -1! Then -2, because if I was looking there, that one just moved down to there and to there. So, that’s the trick we’re going to use inside of OFFSET!
And when I was thinking about this, I was thinking “Well, this whole formula has to move down here.” So I came over here and I said “Well, I need a 0 here, and then a -1 here.” And I just highlight those and copy that down until I got to here, and I said “Oh, and here I need a 0, right?” So these are the numbers that I need inside OFFSET for how many ROWS do we need to go up! So if I can think of a formula to get that sequence of numbers, I can just plop it inside of OFFSET!
So, how about ROWS, we’ll do ROWS! Actually not ROWS, =MOD, because ROWS would be how you get 1-2-3-4-5-6-7-8-9-10-11-12, but MOD would give us a remainder! And so we can say inside of the MOD(ROWS, and I’m sitting in J3, so I’m going to say J$3,J3) and then comma the divider, well I have 7, so I’m going to put 7) Ctrl+Enter! Now let’s copy this down and just take a look, OK, that’s not quite what I want, I really want a 0 here so I’ll hit F2, and from, if I scoot over here, from the ROWS, I can just -1! Right, now I have my 0-1-2-3-4-5-6-0, and then F2 again to put it in Edit mode and I’ll just put a negative, a negation, a unitary operator, poof, just like that. So that little formula there I’ll use inside of OFFSET! That is the piece of the OFFSET that will say how many rows to go up. And the reason why is because if we get down here, you need to go 0 up and then -1, so that ability to increment a number cycle through is very useful here.
Let’s just see if we can get this to work right here with numbers, =OFFSET, the reference is going to be here, and I need to lock it, when I go down I need it to move, but when I move it this way I need it locked! So I’m going to hit the F4 key and it’ll lick it in front of that column reference, comma, OFFSET says that’s where I want to start always, and that’s going to be partially relative and partially locked. But now rows, how many from this starting point, how many rows do I want to go up or down? Up is negative, if I’m in row this one right here, if I want to go up, 3-1 would be 2, so this is where that incrementer goes, Ctrl+V to paste that. And then comma columns, we’re going to take this column, we don’t need that argument so I close parenthesis. Ctrl+Enter, I’m going to drag it down and then over, and just to test I’m going to copy it, paste it down here, oh it worked! Now if I change this to a 9, sure enough it worked.
Now we need one other thing before we can do conditional formatting, because it needs TRUE or FALSE. So I highlight that range, hit F2, and right at the end I’ll say =, and whatever your criteria is, you put it here, I put a 5 here. Generally I like to put it as a cell reference, so I’m going to click right there, F4 to lock it in all directions, and then I’m going to immediately come up here and type a 5, and then put Criteria.
Right, now let’s see if it works, Ctrl+C, Ctrl+V, Ctrl+V, I change this to a 5, and sure enough that works. Now I’m going to Ctrl+Z, Z, Z, Z, I’ll just delete that.
I’m going to come up and get this formula right here, copy, Esc, I don’t need all those right there. Now I’m going to highlight this whole range, and I’m going to use this keyboard shortcut Alt O D, New Rule, click there, that, and whatever format you want.
I’m going to do Fill whatever it is, yellow, click OK. We have our rule there, click OK, click OK! Now let’s go ahead and try it, with this whole highlighted I’m going to use my paintbrush, that’s also right there, double-click it, and then I’m going to click there and there and however many other ones there are. See when I double-click it, you can see the little paintbrush, Esc to turn that off.
And now let’s try it, the test is, boom, does it work, boom, sure looks like it worked.
I’m going to click right here and hot Alt O D, and just look at that formula, sure enough, everything moved beautifully just like we want it. Now, I always like to put criteria up here like this, if I change this to 9, then boom, those ones don’t change and that one does. Alright, thanks to MrExcel and the MrExcel message board for helping me to learn how to do Excel. We’ll throw it back to MrExcel, I’m sure he has something even more amazing!
Bill: Hey thanks Mike, that’s cool! I love these Dueling podcasts, I knew that Mike was going to use OFFSET somehow, but used it in a way that I didn’t ever anticipate, very cool. I think even Mike was happy with that one, right, when he put that 9 in there, you could see that sparkle in his eye. Now while Mike was working of his, I knocked out some VBA here, just the good old standby. I ran VBA and said “Hey, we can do this brute-force method, let’s go through each individual cell, A2:G6, FormatConditions.Add with our formula.” Here’s the thing that just threw me though, it didn’t work the first time, when this formula is an relative formula, no $ in the formula, it is based on, or considers, the active cell! So, if I don’t do cell.Select before setting that formula condition, it’s not going to be correct. So, normally in a macro we never have to select a cell before we can do something to it, but when setting up conditional formatting without a $, sure enough, had to do cell.Select in order to get it to work.
We’ll run this real quick, and what’s that going to do for me is set up conditional formatting in all of these cells that point to B2, but it’s a relative reference to B2 instead of absolute reference. Now we can use Mike’s trick, double-click the Format Painter, and paint, paint, paint, press Esc.
And now the big test, we put in 5, that’ll turn yellow, 6 they don’t, 5 yellow, 6 they don’t, 5 yellow, 6 they don’t, so. Not as elegant as Mike’s, just doing some brute-force VBA to set those up, and then create something that can be copied. Mike, definitely excellent trick, using OFFSET there, very, very cool.
Hey, I want to thank you for stopping by, we’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
Mike: Hey this is Mike Gel Girvin, I’m ExcelIsFun in YouTube, and I have a different way to do that!
Bill: Hey, it’s another Friday, that means it’s another Dueling Excel podcast! I’m Bill Jelen from MrExcel, joining with me Mike Girvin from ExcelIsFun at YouTube. Question sent in by Manfred in Germany, Manfred sent in a great question, and I actually have no idea where this podcast is about to go. I’m going to paint a picture here, Manfred has data where each company takes up several rows.
OK, and normally, if we have to build a formula that relied on this cell within the company, we would, you know, build the formula using $! So I want to point to that cell, press the F4 key, times this value over here in column A and press the F4 key 3 times to lock the column, and then divide it by this number up here, press F4 twice to lock the row. And we get a formula that’s going to point to, you know, the first column, the first row, and cell B2 of that company.
But now the problem is, if we had to copy that formula down here, because those $ are hardcoded into pointing to, for example, B2, it’s going to be the wrong formula. Alright, so I have a solution for this, I can build those formulas as normal, and then use Edit, Replace, and say that I want to replace every $ with nothing, make sure I go to Options that I look in Formulas, and Replace All.
And now I essentially have relative references, but that always point to B2, for example, and I can now copy those formulas down into the other companies, and things are going to work great! Alright, but that’s not Manfred’s problem! I’m going to go on the second sheet here.
The problem is that Manfred is using Conditional Formatting. He has conditional formatting that says “Hey, if that cell contains a 5, it’s yellow, if it doesn’t contain a 5, it’s not!” Alright and, so we go and look at that conditional formatting here, come down to Manage Rules, and edit that rule, you see that we’re hardcoding $ in the conditional formatting formula. Ahhh… now I’m lost… I don’t know how I can globally replace all of those $ in the conditional formatting formulas. Right now if we would copy formats, Ctrl+C, Edit Paste Special Formats, ah, it’s not working. If this is a 5 or this is a 3, it’s not looking at it, it’s looking at this one back up here, it’s changing all of the companies when that first one is a 5. That’s not what Manfred wants to do!
Alright, so I’m going to throw this one over the wall to Mike, and I’m going to sit here and try and puzzle that out myself, let’s see what Mike comes up with. Usually, when I throw it over to Mike, I know where he’s going, I will just be shocked and amazed with you when we come back, and hopefully Mike will have a solution!
Mike: Thanks MrExcel! Shocked and amazed?
You can’t be shocked and amazed if I’m going to some formula here, I learned most of the tricks from the books YOU wrote, and your message board. If you want to be shocked and amazed, then go to the message board and see some of the responses that Aladdin or Houdini or Don Quixote and ALL the other Excellers do over there. Alright! So there it is, we’re going to do some Conditional Formatting, and whatever we put in whatever one of these boxes, is to trigger the conditional formatting.
Now, as I think of this, if I’m going to start, for instance here, and tell all of these cells to look right there, well right here, if I’m looking up there at that cell, I could say “If I look at that cell, I’m 0 away.” When I go down 1, if I was looking at that cell and it moves down as a relative cell reference, how many do I need to move to get back up there? -1! Then -2, because if I was looking there, that one just moved down to there and to there. So, that’s the trick we’re going to use inside of OFFSET!
And when I was thinking about this, I was thinking “Well, this whole formula has to move down here.” So I came over here and I said “Well, I need a 0 here, and then a -1 here.” And I just highlight those and copy that down until I got to here, and I said “Oh, and here I need a 0, right?” So these are the numbers that I need inside OFFSET for how many ROWS do we need to go up! So if I can think of a formula to get that sequence of numbers, I can just plop it inside of OFFSET!
So, how about ROWS, we’ll do ROWS! Actually not ROWS, =MOD, because ROWS would be how you get 1-2-3-4-5-6-7-8-9-10-11-12, but MOD would give us a remainder! And so we can say inside of the MOD(ROWS, and I’m sitting in J3, so I’m going to say J$3,J3) and then comma the divider, well I have 7, so I’m going to put 7) Ctrl+Enter! Now let’s copy this down and just take a look, OK, that’s not quite what I want, I really want a 0 here so I’ll hit F2, and from, if I scoot over here, from the ROWS, I can just -1! Right, now I have my 0-1-2-3-4-5-6-0, and then F2 again to put it in Edit mode and I’ll just put a negative, a negation, a unitary operator, poof, just like that. So that little formula there I’ll use inside of OFFSET! That is the piece of the OFFSET that will say how many rows to go up. And the reason why is because if we get down here, you need to go 0 up and then -1, so that ability to increment a number cycle through is very useful here.
Let’s just see if we can get this to work right here with numbers, =OFFSET, the reference is going to be here, and I need to lock it, when I go down I need it to move, but when I move it this way I need it locked! So I’m going to hit the F4 key and it’ll lick it in front of that column reference, comma, OFFSET says that’s where I want to start always, and that’s going to be partially relative and partially locked. But now rows, how many from this starting point, how many rows do I want to go up or down? Up is negative, if I’m in row this one right here, if I want to go up, 3-1 would be 2, so this is where that incrementer goes, Ctrl+V to paste that. And then comma columns, we’re going to take this column, we don’t need that argument so I close parenthesis. Ctrl+Enter, I’m going to drag it down and then over, and just to test I’m going to copy it, paste it down here, oh it worked! Now if I change this to a 9, sure enough it worked.
Now we need one other thing before we can do conditional formatting, because it needs TRUE or FALSE. So I highlight that range, hit F2, and right at the end I’ll say =, and whatever your criteria is, you put it here, I put a 5 here. Generally I like to put it as a cell reference, so I’m going to click right there, F4 to lock it in all directions, and then I’m going to immediately come up here and type a 5, and then put Criteria.
Right, now let’s see if it works, Ctrl+C, Ctrl+V, Ctrl+V, I change this to a 5, and sure enough that works. Now I’m going to Ctrl+Z, Z, Z, Z, I’ll just delete that.
I’m going to come up and get this formula right here, copy, Esc, I don’t need all those right there. Now I’m going to highlight this whole range, and I’m going to use this keyboard shortcut Alt O D, New Rule, click there, that, and whatever format you want.
I’m going to do Fill whatever it is, yellow, click OK. We have our rule there, click OK, click OK! Now let’s go ahead and try it, with this whole highlighted I’m going to use my paintbrush, that’s also right there, double-click it, and then I’m going to click there and there and however many other ones there are. See when I double-click it, you can see the little paintbrush, Esc to turn that off.
And now let’s try it, the test is, boom, does it work, boom, sure looks like it worked.
I’m going to click right here and hot Alt O D, and just look at that formula, sure enough, everything moved beautifully just like we want it. Now, I always like to put criteria up here like this, if I change this to 9, then boom, those ones don’t change and that one does. Alright, thanks to MrExcel and the MrExcel message board for helping me to learn how to do Excel. We’ll throw it back to MrExcel, I’m sure he has something even more amazing!
Bill: Hey thanks Mike, that’s cool! I love these Dueling podcasts, I knew that Mike was going to use OFFSET somehow, but used it in a way that I didn’t ever anticipate, very cool. I think even Mike was happy with that one, right, when he put that 9 in there, you could see that sparkle in his eye. Now while Mike was working of his, I knocked out some VBA here, just the good old standby. I ran VBA and said “Hey, we can do this brute-force method, let’s go through each individual cell, A2:G6, FormatConditions.Add with our formula.” Here’s the thing that just threw me though, it didn’t work the first time, when this formula is an relative formula, no $ in the formula, it is based on, or considers, the active cell! So, if I don’t do cell.Select before setting that formula condition, it’s not going to be correct. So, normally in a macro we never have to select a cell before we can do something to it, but when setting up conditional formatting without a $, sure enough, had to do cell.Select in order to get it to work.
We’ll run this real quick, and what’s that going to do for me is set up conditional formatting in all of these cells that point to B2, but it’s a relative reference to B2 instead of absolute reference. Now we can use Mike’s trick, double-click the Format Painter, and paint, paint, paint, press Esc.
And now the big test, we put in 5, that’ll turn yellow, 6 they don’t, 5 yellow, 6 they don’t, 5 yellow, 6 they don’t, so. Not as elegant as Mike’s, just doing some brute-force VBA to set those up, and then create something that can be copied. Mike, definitely excellent trick, using OFFSET there, very, very cool.
Hey, I want to thank you for stopping by, we’ll see you next week for another Dueling Excel podcast from MrExcel and ExcelIsFun!
