Description

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

The Mr. Excel podcast is brought to you by Easy-XL. Hey, welcome back to the Mr. Excel netcast I’m Bill Jelen. Alright finally, Sean’s question. Sean has a drop down here, use 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 big. They’re 27 rows by I think 34 columns all the way over. So, I’m going to do a little of set up work here before hand. 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 width. Alright and the reason I’m doing this, it makes all the column with the right width and the paste gets the date format sent for me. That’s why—so that’s a little bit of set up in advance. I know Sean, that’s not what you are 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 its going to tell me where June starts, what row it starts in comma zero. Now, I actually need that row number to be one less so I’m going to put minus one. And we’re just do a little test here, so June it says is a hundred forty two rows below cell A1. Let’s go check. June should appear in row 143. Beautiful! Okay. All of that, so far so good. Next stop, I’m now going to choose the entire range where I want the report to appear so those 27 rows by 34 columns, I’m going to enter one formula for that whole range. Isn’t this cool? Equal offset. Offset very powerful function. We’re going to start from year exclamation point A1. How many rows down do we want to go, okay, well that’s the value that’s stored up here in cell B1, how many columns over as a starting point, zero 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 want 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 date’s changed and everything comes across. Now, a couple of little artifacts here, any blank cells they will show up as zero so I might want to come back to the 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, Sean did not say what he wants to do with this. I assume he just wants to print it. You can’t edit anything here Sean, you can’t go to one cell and change things. Also, but we’re going to have kind of a hassle if you add new employees, if you have more employees, if you decided you need another position here and you add some extra rows, that’s going to be a problem. So, there’s some downsides of this but maybe you make it a little bit wider to begin with or taller to begin with. It should be a very cool solution for self. Sean, thanks for sending that question in. Thanks to you for stopping by. We’ll see you next time for another netcast from Mr. Excel.