Shaun has a large worksheet with 12 monthly reports on it. On a summary worksheet, he would like to show one particular month based on a dropdown. Episode 1163 discusses Paste Special Column Widths, Array Formulas, Match, and OFFSET.
Transcript of the video:
The MrExcel podcast is brought to you by “Easy-XL”!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Alright, finally, Shaun's question.
Shaun has a drop down here, he used data validation to create this drop-down, and when he chooses a month he wants a report from the appropriate section of the Year worksheet to come across.
Now these reports are, they're big, they're 27 rows by, I think, 34 columns all the way over.
So I'm going to do a little bit of setup work here beforehand, I'm going to copy the January report, Ctrl+C, and then come back here to the Sheet tab, figure out where I want it to be, I'm going to Ctrl+V to paste.
And then I'm also going to Paste Special and choose Column widths, alright.
And the reason I'm doing this, it makes all the column widths the right width, and the paste gets the date formats in for me, that's my whole goal.
OK, so that's a little bit of setup in advance, I know, Shaun, that's not what you're trying to do.
Next, we use a really complicated formula here, =MATCH, go find June within column a of the Year worksheet.
And what this is going to do for me is, it's going to tell me where June starts, what row it starts in, ,0 . Now I actually need that row number to be 1 less, so I'm going to put -1, and we'll just do a little test here.
So June, it says, is 142 rows below cell A1, let's go check, June should appear in row 143, beautiful, OK.
All of that, so far, so good.
Ready?
Next up, I'm now going to choose the entire range where I want the report to appear, so, those 27 rose by 34 columns, I'm going to enter ONE formula for that whole range, isn't this cool?
=OFFSET, very powerful function!
We're going to start from Year!A1, how many rows down do we want to go?
OK Well, that's the value that's stored up here in cell B1.
How many columns over as a starting point, 0 columns over, how many rows tall, 27 rows tall, how many columns wide, 34 columns wide.
And now the trick, because we want all of these answers to appear in all of these cells, I’m going to press Ctrl+Shift+Enter, it's going to create one single array formula that has all of the data.
Now, let's try it, if we switch to another month, let's go back to January, you see that the dates change, and everything comes across, Now, a couple of little artifacts here, any blank cells, those show up as zero.
So I might want to come back to your original spreadsheet, here and actually put in spaces in those blank cells to make sure I'm not getting any zeros showing up, but in general, a good way to go.
Now, Shaun did not say what he wants to do with this, I'm assuming he just wants to print it, you can't edit anything here, you can't go to one cell and change things.
Also, we're going to have kind of a hassle, if you add new employees, if you have more employees, if you deciding, you know, the position here, and you add some extra rows, that's going to be a problem.
So, there's some downsides to this, but you know, maybe you make it a little bit wider to begin with, or taller to begin with, it should be a fairly cool solution for you.
So, Shaun, thanks for sending that question in, thanks to you for stopping by, we’ll see you next time for another netcast from MrExcel!
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Alright, finally, Shaun's question.
Shaun has a drop down here, he used data validation to create this drop-down, and when he chooses a month he wants a report from the appropriate section of the Year worksheet to come across.
Now these reports are, they're big, they're 27 rows by, I think, 34 columns all the way over.
So I'm going to do a little bit of setup work here beforehand, I'm going to copy the January report, Ctrl+C, and then come back here to the Sheet tab, figure out where I want it to be, I'm going to Ctrl+V to paste.
And then I'm also going to Paste Special and choose Column widths, alright.
And the reason I'm doing this, it makes all the column widths the right width, and the paste gets the date formats in for me, that's my whole goal.
OK, so that's a little bit of setup in advance, I know, Shaun, that's not what you're trying to do.
Next, we use a really complicated formula here, =MATCH, go find June within column a of the Year worksheet.
And what this is going to do for me is, it's going to tell me where June starts, what row it starts in, ,0 . Now I actually need that row number to be 1 less, so I'm going to put -1, and we'll just do a little test here.
So June, it says, is 142 rows below cell A1, let's go check, June should appear in row 143, beautiful, OK.
All of that, so far, so good.
Ready?
Next up, I'm now going to choose the entire range where I want the report to appear, so, those 27 rose by 34 columns, I'm going to enter ONE formula for that whole range, isn't this cool?
=OFFSET, very powerful function!
We're going to start from Year!A1, how many rows down do we want to go?
OK Well, that's the value that's stored up here in cell B1.
How many columns over as a starting point, 0 columns over, how many rows tall, 27 rows tall, how many columns wide, 34 columns wide.
And now the trick, because we want all of these answers to appear in all of these cells, I’m going to press Ctrl+Shift+Enter, it's going to create one single array formula that has all of the data.
Now, let's try it, if we switch to another month, let's go back to January, you see that the dates change, and everything comes across, Now, a couple of little artifacts here, any blank cells, those show up as zero.
So I might want to come back to your original spreadsheet, here and actually put in spaces in those blank cells to make sure I'm not getting any zeros showing up, but in general, a good way to go.
Now, Shaun did not say what he wants to do with this, I'm assuming he just wants to print it, you can't edit anything here, you can't go to one cell and change things.
Also, we're going to have kind of a hassle, if you add new employees, if you have more employees, if you deciding, you know, the position here, and you add some extra rows, that's going to be a problem.
So, there's some downsides to this, but you know, maybe you make it a little bit wider to begin with, or taller to begin with, it should be a fairly cool solution for you.
So, Shaun, thanks for sending that question in, thanks to you for stopping by, we’ll see you next time for another netcast from MrExcel!
