Microsoft Excel Puzzle and Solutions.
Bob Umlas sent in the puzzle. How to refer to every 3rd column on Sheet2?
Table of Contents
(0:00) Bob Umlas Book
(0:22) Problem: Refer to every 3rd cell
(0:44) CHOOSECOLS with SEQUENCE
(1:16) Horizontal XLOOKUP
(1:46) Fill Handle from 3 Cells
(2:32) Delete Empty Cells
(2:54) R1C1 Method 2
(3:43) Replace = with x=
(4:12) Change R1C1 to A1
(4:21) Insert colums/rows to test
Bob Umlas sent in the puzzle. How to refer to every 3rd column on Sheet2?
Table of Contents
(0:00) Bob Umlas Book
(0:22) Problem: Refer to every 3rd cell
(0:44) CHOOSECOLS with SEQUENCE
(1:16) Horizontal XLOOKUP
(1:46) Fill Handle from 3 Cells
(2:32) Delete Empty Cells
(2:54) R1C1 Method 2
(3:43) Replace = with x=
(4:12) Change R1C1 to A1
(4:21) Insert colums/rows to test
Transcript of the video:
Today we have a puzzle and a solution from Bob Umlas, the formula for every third column.
Bob is the king of weird Excel techniques in...
Bob, of course, is the author of many books, including this one and the king of unusual Excel techniques.
So Bob has data on sheet two and he needs to grab every third column.
So there's a month and then other stuff, a month and then other stuff, month, other stuff.
How to get every third column.
And he says in the future someone will be inserting columns on sheet 2 so no offset.
No offset.
Okay.
All right.
So hey, the absolute easiest way to do this is with the new CHOOSECOLS.
We take all of the data on sheet 2, and then which columns do we want.
We want every third column starting at one.
So this is going to give me one row, 12 columns, starting at one, jumping by three, which will successfully give me 1, 4, 7, 10, 13, 16, and so on.
One formula and we're done.
Bob didn't think of CHOOSECOLS, so then he changes the rules.
No offset, no CHOOSECOLS, because again, someone will be inserting a column on sheet 2.
All right, so then I thought, okay, what if we use XLOOKUP, like a horizontal XLOOKUP?
So I'm looking up January in all the data on sheet 2, and then what to return.
Well, I want to return the entire column starting in row four to 15.
That gets us all the January numbers and then just copy across and we're good to go.
Okay, so then Bob changes the rules again.
No, they might be inserting rows in the future so no XLOOKUP.
Okay, Bob, let's see your method.
So we start off with a simple little formula here that points to the very first cell that we want to grab, the January cell and then select three cells like that, and then drag to the right out to column AH.
And what that does is that successfully gives us just the month names going across.
We end up with A3 and then here D3 and then here, G3 and so on.
All right, so that is pretty cool there Bob.
You started with formula, blank, blank and when we copy that to the right, we get formula, blank, blank, formula, blank, blank.
And now that we have those empty sales, we can take advantage of that.
And then from the home tab, find and select.
Go to special, select the blanks, okay, and then home, delete, delete sales, shift sales left.
And we now have just the 12 months and drag down and we're good to go.
All right, nice trick Bob.
Okay, then Bob has a method two here that relies on the R1C1 referencing style.
So file, options, formula, turn on the old R1C1 referencing style.
This formula here is going to be equal sheet 2, exclamation point R, and because I'm currently in row four and want to row three, I need to go up one row and then column one, copy that over to the right and manually edit from column one to column four.
All right, just so you can see what happened there, if we go into show formulas mode, you see that we now have this C1 at the end and C4 at the end.
And that one and four will create a pattern as we drag that across.
But first, we have to make these not be formulas.
Control H for replace, change equals to X equals, replace all.
Now with those two cells selected, drag out to column 12 and then change back from X equals to equals, replace all, click okay, click close and we should be able to drag down.
And then file, options, formulas, go back to A1 style, click okay, and the big test here is someone should be able to come here, insert new columns, and then insert new rows and still have all of the references back to the original cells.
It's a weird situation with a lot of constraints, but a couple of cool tricks there from Bob Umlas.
Thanks to Bob for sending that in.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
Bob is the king of weird Excel techniques in...
Bob, of course, is the author of many books, including this one and the king of unusual Excel techniques.
So Bob has data on sheet two and he needs to grab every third column.
So there's a month and then other stuff, a month and then other stuff, month, other stuff.
How to get every third column.
And he says in the future someone will be inserting columns on sheet 2 so no offset.
No offset.
Okay.
All right.
So hey, the absolute easiest way to do this is with the new CHOOSECOLS.
We take all of the data on sheet 2, and then which columns do we want.
We want every third column starting at one.
So this is going to give me one row, 12 columns, starting at one, jumping by three, which will successfully give me 1, 4, 7, 10, 13, 16, and so on.
One formula and we're done.
Bob didn't think of CHOOSECOLS, so then he changes the rules.
No offset, no CHOOSECOLS, because again, someone will be inserting a column on sheet 2.
All right, so then I thought, okay, what if we use XLOOKUP, like a horizontal XLOOKUP?
So I'm looking up January in all the data on sheet 2, and then what to return.
Well, I want to return the entire column starting in row four to 15.
That gets us all the January numbers and then just copy across and we're good to go.
Okay, so then Bob changes the rules again.
No, they might be inserting rows in the future so no XLOOKUP.
Okay, Bob, let's see your method.
So we start off with a simple little formula here that points to the very first cell that we want to grab, the January cell and then select three cells like that, and then drag to the right out to column AH.
And what that does is that successfully gives us just the month names going across.
We end up with A3 and then here D3 and then here, G3 and so on.
All right, so that is pretty cool there Bob.
You started with formula, blank, blank and when we copy that to the right, we get formula, blank, blank, formula, blank, blank.
And now that we have those empty sales, we can take advantage of that.
And then from the home tab, find and select.
Go to special, select the blanks, okay, and then home, delete, delete sales, shift sales left.
And we now have just the 12 months and drag down and we're good to go.
All right, nice trick Bob.
Okay, then Bob has a method two here that relies on the R1C1 referencing style.
So file, options, formula, turn on the old R1C1 referencing style.
This formula here is going to be equal sheet 2, exclamation point R, and because I'm currently in row four and want to row three, I need to go up one row and then column one, copy that over to the right and manually edit from column one to column four.
All right, just so you can see what happened there, if we go into show formulas mode, you see that we now have this C1 at the end and C4 at the end.
And that one and four will create a pattern as we drag that across.
But first, we have to make these not be formulas.
Control H for replace, change equals to X equals, replace all.
Now with those two cells selected, drag out to column 12 and then change back from X equals to equals, replace all, click okay, click close and we should be able to drag down.
And then file, options, formulas, go back to A1 style, click okay, and the big test here is someone should be able to come here, insert new columns, and then insert new rows and still have all of the references back to the original cells.
It's a weird situation with a lot of constraints, but a couple of cool tricks there from Bob Umlas.
Thanks to Bob for sending that in.
If you like these videos, please down below, like, subscribe and ring the bell.
Feel free to post any questions or comments down in the comments below.
