You could solve yesterday's podcast using the often-cursed-at, but seldom-understood GETPIVOTDATA function. In Episode 870, how to use GETPIVOTDATA to return the grand total and also using =IF(ISBLANK to clean up stray results below the pivot table.
Transcript of the video:
Hey, welcome back to the MrExcel netcast, I'm Bill Jelen.
Well, yesterday we had a look at Richard's Pivot Table, and there's another solution to this: It's called GETPIVOTDATA.
Now, most of us hate GETPIVOTDATA because Microsoft forced it upon us, and no one understands exactly how it works; but it does solve this particular problem.
So, here I'm going to enter this formula, =G5-- and I typed G5, I didn't click on it-- and then divided by-- and I'm going to click here in Cell G9 and it generates a formula for us called GETPIVOTDATDA("Reason",$A$3).
Well, the $A$3, that's just pointing to a cell within the Pivot Table, and Reason is saying that that is adding up all of the Reason counts.
Now, let's just click somewhere else here, and we'll see how this is working.
If I would click on Branch 31 Grand Total, then it says, "Hey, we're going to take the Reason measure from the Pivot Table and A3 only, where Branch = 31.
And if I would click up here, Branch 31 and Reason C, you'll see that the GETPIVOTDATA says we want the Reason measure from the Pivot Table in A3, Branch 31, Reason C. So, you can imagine that it adds additional pairs of criteria.
But if, in fact, if we just really want this Grand Total, we could simply use GETPIVOTDATA("Reason"A3), Enter that, copy that down, and you'll see that as we change to a different quarter-- which may have more or less reasons-- it continues to always return 35 as the denominator.
The GETPIVOTDATA-- very flexible function, although most of us never take the time to learn what it's doing because Excel just starts turning it on by default.
It also solves Richard's problem in this case.
Now, what about getting rid of the extra zeros at the end?
I'm going to edit this formula and say =IF(ISBLANK(G5), then I want "" otherwise I want my formula, and put a closing parenthesis at the end to close the IF function.
Copy that down, and what we should see is, now, all of the cells where there is no data, will appear blank.
The formula is still there-- you can see in the formula bar as I go down there-- but as we update each item in the Pivot Table, it's only going to show the items where the corresponding value in Column G is not blank.
So there, we have it-- a better way, perhaps, to solve yesterday's problem, and then also modification of using =IF(ISBLANK) to make sure that the items with no data appear not to have a formula.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
Well, yesterday we had a look at Richard's Pivot Table, and there's another solution to this: It's called GETPIVOTDATA.
Now, most of us hate GETPIVOTDATA because Microsoft forced it upon us, and no one understands exactly how it works; but it does solve this particular problem.
So, here I'm going to enter this formula, =G5-- and I typed G5, I didn't click on it-- and then divided by-- and I'm going to click here in Cell G9 and it generates a formula for us called GETPIVOTDATDA("Reason",$A$3).
Well, the $A$3, that's just pointing to a cell within the Pivot Table, and Reason is saying that that is adding up all of the Reason counts.
Now, let's just click somewhere else here, and we'll see how this is working.
If I would click on Branch 31 Grand Total, then it says, "Hey, we're going to take the Reason measure from the Pivot Table and A3 only, where Branch = 31.
And if I would click up here, Branch 31 and Reason C, you'll see that the GETPIVOTDATA says we want the Reason measure from the Pivot Table in A3, Branch 31, Reason C. So, you can imagine that it adds additional pairs of criteria.
But if, in fact, if we just really want this Grand Total, we could simply use GETPIVOTDATA("Reason"A3), Enter that, copy that down, and you'll see that as we change to a different quarter-- which may have more or less reasons-- it continues to always return 35 as the denominator.
The GETPIVOTDATA-- very flexible function, although most of us never take the time to learn what it's doing because Excel just starts turning it on by default.
It also solves Richard's problem in this case.
Now, what about getting rid of the extra zeros at the end?
I'm going to edit this formula and say =IF(ISBLANK(G5), then I want "" otherwise I want my formula, and put a closing parenthesis at the end to close the IF function.
Copy that down, and what we should see is, now, all of the cells where there is no data, will appear blank.
The formula is still there-- you can see in the formula bar as I go down there-- but as we update each item in the Pivot Table, it's only going to show the items where the corresponding value in Column G is not blank.
So there, we have it-- a better way, perhaps, to solve yesterday's problem, and then also modification of using =IF(ISBLANK) to make sure that the items with no data appear not to have a formula.
Thanks for stopping by, we'll see you next time for another netcast from MrExcel.
[ music ]
