SOLVED
Home

GETPIVOTDATA Relative Reference and Cascading (Linking) Slicers to Filter Students and Test Scores

%3CLINGO-SUB%20id%3D%22lingo-sub-224200%22%20slang%3D%22en-US%22%3EGETPIVOTDATA%20Relative%20Reference%20and%20Cascading%20(Linking)%20Slicers%20to%20Filter%20Students%20and%20Test%20Scores%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224200%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20All%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20could%20really%20use%20some%20help%20before%20I%20go%20jump%20off%20a%20bridge.%20Jk.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20issue%20I%20am%20currently%20having%20it%20related%20to%20using%20relative%20references%20in%20the%20GETPIVOTDATA%20function%20and%20cascading%20slicers.%20I%20have%20a%20PivotTable%20set%20up%20that%20has%20a%20list%20of%20students%20and%20their%20test%20scores.%20What%20I%20would%20like%20to%20happen%20is%20for%20the%20user%20to%20select%20one%20student%20from%20the%20cascading%20slicer%2C%20whose%20parent%20is%20a%20slicer%20with%20which%20class%20they're%20in%2C%20and%20the%20student's%20name%20changes%20in%20one%20cell%20while%20the%20student's%20test%20score%20appears%20in%20another.%20I%20do%20not%20know%20if%20this%20is%20possible%20with%20slicers%2C%20but%20I%20was%20able%20to%20get%20it%20to%20work%20with%20data%20validation%20lists.%20Unfortunately%2C%20the%20data%20validation%20is%20not%20dependent%20on%20a%20parent.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHow%20it%20works%20with%20the%20data%20validation%20is%20I%20connect%20it%20to%20the%20list%20of%20students.%20After%20that%2C%20I%20get%20the%20scores%20from%20the%20PivotTable%20and%20change%20the%20absolute%20reference%20to%20relative%20by%20changing%20the%20name%20of%20the%20student%20to%20the%20cell%20in%20which%20I%20have%20the%20students%20listed.%20The%20function%20works%20well%20for%20what%20it's%20supposed%20to%20do.%20The%20issue%20is%20that%20it%20is%20not%20dependent%20on%20the%20parent%20slicer%20and%20it%20takes%20away%20from%20the%20interactivity%20of%20the%20dashboard%20I%20am%20working%20on.%20In%20addition%2C%20I%20am%20unable%20to%20search%20the%20list%20of%20students%20to%20make%20it%20more%20efficient%20for%20the%20user.%20Is%20there%20a%20way%20to%20search%20slicers%3F%20Can%20I%20connect%20another%20slicer%20that%20allow%20me%20to%20search%20by%20the%20first%20two%20letters%20of%20the%20student's%20name%3F%20How%20would%20I%20do%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20other%20way%20I%20have%20done%20it%20is%20to%20use%20the%20GETPIVOTDATA%20function%20to%20obtain%20all%20the%20names%20in%20the%20PivotTable%20relative%20to%20which%20class%20they're%20in.%20After%20that%2C%20I%20set%20up%20the%20GETPIVOTDATA%20function%20to%20retrieve%20the%20test%20scores%20for%20each%20student.%20The%20absolute%20reference%20was%20changed%20to%20relative%20by%20connecting%20the%20formula%20to%20the%20cells%20with%20the%20names%20I%20had%20already%20retrieved.%20This%20works%20really%20well%20for%20it%20does.%20The%20student's%20name%20appears%20at%20the%20top%20along%20with%20the%20respective%20test%20score.%20The%20issue%20with%20this%20is%20that%20all%20of%20the%20other%20cells%20either%20turn%20into%20a%200%20or%20a%20reference%20error%20since%20that%20data%20has%20been%20filter.%20Is%20there%20a%20workaround%20for%20this%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20see%20the%20attached%20file.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-224200%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EGet%20%26amp%3B%20Transform%20Data%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EPivotTable%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ESlicers%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224772%22%20slang%3D%22en-US%22%3ERe%3A%20GETPIVOTDATA%20Relative%20Reference%20and%20Cascading%20(Linking)%20Slicers%20to%20Filter%20Students%20and%20Test%20Scor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224772%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20you%20so%20much!%20I%20am%20not%20entirely%20familiar%20with%20the%20OR%20function.%20I%20will%20have%20to%20look%20into%20it%20a%20bit%20more.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPart%20of%20the%20reason%20I%20wanted%20to%20connect%20a%20shape%20or%20combo%20box%20is%20because%20I%20want%20the%20data%20to%20appear%20on%20a%20nicer%20graphic.%20I%20will%20probably%20just%20write%20the%20formula%20in%20a%20text%20box%20and%20format%20the%20text%20box%20so%20the%20information%20looks%20cleaner%20and%20easier%20to%20read%20on%20my%20dashboard.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-224488%22%20slang%3D%22en-US%22%3ERe%3A%20GETPIVOTDATA%20Relative%20Reference%20and%20Cascading%20(Linking)%20Slicers%20to%20Filter%20Students%20and%20Test%20Scor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-224488%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20not%20really%20sure%20what%20you%20are%20trying%20to%20do.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20your%20Example%202%2C%20you%20can%20use%20an%20IF%20to%20get%20rid%20of%20the%200s%20or%20%23Refs%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3DIF(OR(A5%3D%22Grand%20Total%22%2CA5%3D%22%22)%2C%22%22%2CA5)%3C%2FP%3E%3CP%3E%3DIF(OR(A5%3D%22Grand%20Total%22%2CA5%3D%22%22)%2C%22%22%2CGETPIVOTDATA(%22Field1%22%2C%24A%244%2C%22Name%22%2CI4))%3C%2FP%3E%3CP%3EThese%20will%20tidy%20up%20your%20results%2C%20it%20just%20gives%20you%20an%20empty%20cell%20if%20its%20not%20returning%20a%20score.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20example%201..%20You%20can%20add%20another%20slicer%2C%20just%20add%20it%20and%20select%20class%20and%20you%20will%20have%202%20slicers%20that%20work%20together%20to%20let%20you%20select%20a%20class%20and%20then%20a%20student.%20On%20the%20Name%20slicer%20right%20click%20and%20select%20Slicer%20Settings%20then%20tick%20the%20Hide%20items%20with%20no%20data%20to%20hide%20students%20not%20in%20whatever%20class%20wasn't%20selected.%20You%20could%20then%20use%20something%20like%20the%20above%20IFs%20to%20check%20if%20only%201%20child%20has%20been%20selected%2C%20by%20seeing%20if%20A6%20%3D%20Grand%20Total..%20then%20show%20the%20name%20and%20score.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'm%20not%20100%25%20sure%20that%20I%20understood%20what%20you%20were%20trying%20to%20do%2C%20but%20hopefully%20that%20helps%20a%20bit%2C%20I've%20attached%20a%20workbook%20with%20the%20above%20examples%20working.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-846573%22%20slang%3D%22en-US%22%3ERe%3A%20GETPIVOTDATA%20Relative%20Reference%20and%20Cascading%20(Linking)%20Slicers%20to%20Filter%20Students%20and%20Test%20Scor%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-846573%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F170772%22%20target%3D%22_blank%22%3E%40Philip%20West%3C%2FA%3E%26nbsp%3BI%20have%20a%20similar%20request%20yet%20I%20wasn't%20quite%20able%20to%20figure%20out%20how.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20%22ID%22%20has%202%20mark%20%22X%22%20at%20different%20identifier%20A-N.%20As%20the%20list%20(data%20tab)%20gets%20updated%20over%20time%2C%20I'd%20like%20to%20plot%20a%20matrix%20(matrix%20tab)%20where%20the%20filtered%20total%20count%20got%20updated%20as%20well.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20total%20identifier%20count%20is%20depending%20on%20three%20filters%3A%20Owned%2C%20Rarity%20and%20the%20specific%20identifier%20(only%20one%20identifier%20at%20a%20time%2C%20either%20A%20or%20B%20or%20C...).%20Are%20you%20able%20to%20advise%20what%20would%20be%20the%20tweaks%20I'm%20missing%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Anthony Smith
Contributor

Hi All,

 

I could really use some help before I go jump off a bridge. Jk.

 

The issue I am currently having it related to using relative references in the GETPIVOTDATA function and cascading slicers. I have a PivotTable set up that has a list of students and their test scores. What I would like to happen is for the user to select one student from the cascading slicer, whose parent is a slicer with which class they're in, and the student's name changes in one cell while the student's test score appears in another. I do not know if this is possible with slicers, but I was able to get it to work with data validation lists. Unfortunately, the data validation is not dependent on a parent. 

 

How it works with the data validation is I connect it to the list of students. After that, I get the scores from the PivotTable and change the absolute reference to relative by changing the name of the student to the cell in which I have the students listed. The function works well for what it's supposed to do. The issue is that it is not dependent on the parent slicer and it takes away from the interactivity of the dashboard I am working on. In addition, I am unable to search the list of students to make it more efficient for the user. Is there a way to search slicers? Can I connect another slicer that allow me to search by the first two letters of the student's name? How would I do this?

 

The other way I have done it is to use the GETPIVOTDATA function to obtain all the names in the PivotTable relative to which class they're in. After that, I set up the GETPIVOTDATA function to retrieve the test scores for each student. The absolute reference was changed to relative by connecting the formula to the cells with the names I had already retrieved. This works really well for it does. The student's name appears at the top along with the respective test score. The issue with this is that all of the other cells either turn into a 0 or a reference error since that data has been filter. Is there a workaround for this?

 

Please see the attached file. 

3 Replies
Solution

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.

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. 

@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?

Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
16 Replies
flashing a white screen while open new tab
cntvertex in Discussions on
11 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
28 Replies
Early preview of Microsoft Edge group policies
Sean Lyndersay in Discussions on
65 Replies