**** sends in a cool question today. First, he figured an amazing way to grab the first value from a subtotaled group. But, Episode 712 shows three lines of VBA code to finish the process.
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:
Hey, welcome back to the MrExcel netcast.
I'm Bill Jelen Now, today we have a question sent in by ****.
**** is trying to add subtotals.
So, he said Subtotals, At each change in, account of course he picked all the numeric fields, but he also chose the customer field which initially isn't going to make any sense because it's a text field.
Now, in past podcasts I've talked about leaving that blank and using a cool trick to fill in that cell with the most recent value, but **** needs to fill it in with the first value.
So, let's take a look at these formulas here comes up as subtotal comma 9 and **** had a cool trick he said hey we're going to use Edit, Replace and change every occurrence of subtotal 9 comma 2 choose 1 comma.
Let's do replace all and initially we have a problem that none of the formulas work the CHOOSE function says hey go choose the first value from this list and normally, you'd have to type E2, E3, E4, E5, but **** put in a range and he discovered that if he would edit the formula, and use control shift enter to change it to an array formula.
It works perfectly.
But he didn't want have to go through and use ctrl shift and or dozens and dozens and dozens of times and that's when he wrote to me.
When I said hey, this is simple. Let's grab the book VBA and Macros for Microsoft Excel and we could write a short little Macro, in this case a three line Macro that will solve this problem.
So, here's the Macro it says, for each cell in selection, but not just the selection we're going to use the special cells property and say we just want this visible cells.
The formula where a property is going to be equal to the formula.
So, we have a whole bunch of formulas there they just need to be made in the array formulas and we can do that with this Macro.
Let's run the Macro and you can actually see back here the results.
Sure enough, it went through and changed all of those formulas.
Now, this is a great trick for basically returning the first value from the subtotal group great idea.
I wanna thank **** for sending it in and with a few lines of Macro code we were able to make it work very easily.
So, thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
I'm Bill Jelen Now, today we have a question sent in by ****.
**** is trying to add subtotals.
So, he said Subtotals, At each change in, account of course he picked all the numeric fields, but he also chose the customer field which initially isn't going to make any sense because it's a text field.
Now, in past podcasts I've talked about leaving that blank and using a cool trick to fill in that cell with the most recent value, but **** needs to fill it in with the first value.
So, let's take a look at these formulas here comes up as subtotal comma 9 and **** had a cool trick he said hey we're going to use Edit, Replace and change every occurrence of subtotal 9 comma 2 choose 1 comma.
Let's do replace all and initially we have a problem that none of the formulas work the CHOOSE function says hey go choose the first value from this list and normally, you'd have to type E2, E3, E4, E5, but **** put in a range and he discovered that if he would edit the formula, and use control shift enter to change it to an array formula.
It works perfectly.
But he didn't want have to go through and use ctrl shift and or dozens and dozens and dozens of times and that's when he wrote to me.
When I said hey, this is simple. Let's grab the book VBA and Macros for Microsoft Excel and we could write a short little Macro, in this case a three line Macro that will solve this problem.
So, here's the Macro it says, for each cell in selection, but not just the selection we're going to use the special cells property and say we just want this visible cells.
The formula where a property is going to be equal to the formula.
So, we have a whole bunch of formulas there they just need to be made in the array formulas and we can do that with this Macro.
Let's run the Macro and you can actually see back here the results.
Sure enough, it went through and changed all of those formulas.
Now, this is a great trick for basically returning the first value from the subtotal group great idea.
I wanna thank **** for sending it in and with a few lines of Macro code we were able to make it work very easily.
So, thanks to you for stopping by.
We'll see you next time for another netcast from MrExcel.
