Bob writes with a question about using AutoFilters when the headings are not in row 1. With Episode #1310 Bill looks at a solution, as well as a method for totaling the visible cells...
"Learn Excel 97-2007 from MrExcel" - by Bill Jelen
"Learn Excel 97-2007 from MrExcel" - by Bill Jelen
Transcript of the video:
MrExcel podcast is sponsored by Easy-XL.
Learn Excel form MrExcel podcast, episode 1310.
Filter Dropdowns.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent by Bob.
Bob send me this spreadsheet and it actually looked like this.
Okay so, there were these filter drop downs and bob was going to a specific product out here choosing that product and he wanted to see the totals in row 2.
So, let's go to turn this off and take a look at what's going on here, the real headings for this data set are in row three product and the months going across here they have a subtotal function, that's sub totaling everything beneath it, the advantage of subtotal is that it shows only the visible cells.
So, if you were able to filter just product A that would give us the totals, and then it has some sort of a percentage up here.
Now, when they turn on the AutoFilter here and you choose a single cell and it looks for the entire contiguous ranger that's Control+Asterisk, shows you that continued contiguous range and then it uses the top row for the dropdowns.
Well, that's not really what we want to have happen.
I said Bob, that the solution here is really, really simple.
I want you to go between row 2 and row 3 and insert a row.
All right and if you want you can even make that real incredibly small like a row high of 1 or something like that, so nobody even realizes it's there, then go into your data set and turn on the filter drop down and now, you see the filters up here in well row 4, were the real headings are and now you can go through and show us just one product A and get the totals for that product line which I think is what he was trying to do.
Now, hey, by the way if you have to set this up, Bob didn't tell me how he did this.
One great way of doing it is to filter the data set first and then come down below and hit the AutoSum button and usually AutoSum gives us some functions, but this one because we've already filter the data set gives us the SUBTOTAL function and then you can't Control+X, to copy that and Control+V to paste, creating a ad hoc subtotal at the top.
Now, when I pasted that the formulas that we're referring there of course became bad, but great little trick for summing that up beforehand.
So, the answer in this case is just incredibly simple we'll just create a tiny little row blank row to keep the real headings away from the calculations above the table and then the AutoFilters allowed to go to row 4, instead of row 1, listen it will still calling an AutoFilter or what they call it just the Filter now for four years going on, but I still call it the AutoFilter because that's what it was called...
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
Learn Excel form MrExcel podcast, episode 1310.
Filter Dropdowns.
Oh! Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen.
Today's question sent by Bob.
Bob send me this spreadsheet and it actually looked like this.
Okay so, there were these filter drop downs and bob was going to a specific product out here choosing that product and he wanted to see the totals in row 2.
So, let's go to turn this off and take a look at what's going on here, the real headings for this data set are in row three product and the months going across here they have a subtotal function, that's sub totaling everything beneath it, the advantage of subtotal is that it shows only the visible cells.
So, if you were able to filter just product A that would give us the totals, and then it has some sort of a percentage up here.
Now, when they turn on the AutoFilter here and you choose a single cell and it looks for the entire contiguous ranger that's Control+Asterisk, shows you that continued contiguous range and then it uses the top row for the dropdowns.
Well, that's not really what we want to have happen.
I said Bob, that the solution here is really, really simple.
I want you to go between row 2 and row 3 and insert a row.
All right and if you want you can even make that real incredibly small like a row high of 1 or something like that, so nobody even realizes it's there, then go into your data set and turn on the filter drop down and now, you see the filters up here in well row 4, were the real headings are and now you can go through and show us just one product A and get the totals for that product line which I think is what he was trying to do.
Now, hey, by the way if you have to set this up, Bob didn't tell me how he did this.
One great way of doing it is to filter the data set first and then come down below and hit the AutoSum button and usually AutoSum gives us some functions, but this one because we've already filter the data set gives us the SUBTOTAL function and then you can't Control+X, to copy that and Control+V to paste, creating a ad hoc subtotal at the top.
Now, when I pasted that the formulas that we're referring there of course became bad, but great little trick for summing that up beforehand.
So, the answer in this case is just incredibly simple we'll just create a tiny little row blank row to keep the real headings away from the calculations above the table and then the AutoFilters allowed to go to row 4, instead of row 1, listen it will still calling an AutoFilter or what they call it just the Filter now for four years going on, but I still call it the AutoFilter because that's what it was called...
I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
