antman2988
Board Regular
- Joined
- Jun 28, 2018
- Messages
- 78
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 link and navigate to attached file.
https://techcommunity.microsoft.com...rence-and-Cascading-Slicers/m-p/224200#M16707
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 link and navigate to attached file.
https://techcommunity.microsoft.com...rence-and-Cascading-Slicers/m-p/224200#M16707
