Forum Discussion
GETPIVOTDATA Relative Reference and Cascading (Linking) Slicers to Filter Students and Test Scores
I'm not really sure what you are trying to do.
In your Example 2, you can use an IF to get rid of the 0s or #Refs
=IF(OR(A5="Grand Total",A5=""),"",A5)
=IF(OR(A5="Grand Total",A5=""),"",GETPIVOTDATA("Field1",$A$4,"Name",I4))
These will tidy up your results, it just gives you an empty cell if its not returning a score.
In example 1.. You can add another slicer, just add it and select class and you will have 2 slicers that work together to let you select a class and then a student. On the Name slicer right click and select Slicer Settings then tick the Hide items with no data to hide students not in whatever class wasn't selected. You could then use something like the above IFs to check if only 1 child has been selected, by seeing if A6 = Grand Total.. then show the name and score.
I'm not 100% sure that I understood what you were trying to do, but hopefully that helps a bit, I've attached a workbook with the above examples working.
I'm not really sure what you are trying to do.
In your Example 2, you can use an IF to get rid of the 0s or #Refs
=IF(OR(A5="Grand Total",A5=""),"",A5)
=IF(OR(A5="Grand Total",A5=""),"",GETPIVOTDATA("Field1",$A$4,"Name",I4))
These will tidy up your results, it just gives you an empty cell if its not returning a score.
In example 1.. You can add another slicer, just add it and select class and you will have 2 slicers that work together to let you select a class and then a student. On the Name slicer right click and select Slicer Settings then tick the Hide items with no data to hide students not in whatever class wasn't selected. You could then use something like the above IFs to check if only 1 child has been selected, by seeing if A6 = Grand Total.. then show the name and score.
I'm not 100% sure that I understood what you were trying to do, but hopefully that helps a bit, I've attached a workbook with the above examples working.
Philip West I have a similar request yet I wasn't quite able to figure out how.
Each "ID" has 2 mark "X" at different identifier A-N. As the list (data tab) gets updated over time, I'd like to plot a matrix (matrix tab) where the filtered total count got updated as well.
The total identifier count is depending on three filters: Owned, Rarity and the specific identifier (only one identifier at a time, either A or B or C...). Are you able to advise what would be the tweaks I'm missing?
Thank you so much! I am not entirely familiar with the OR function. I will have to look into it a bit more.
Part of the reason I wanted to connect a shape or combo box is because I want the data to appear on a nicer graphic. I will probably just write the formula in a text box and format the text box so the information looks cleaner and easier to read on my dashboard.
